• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Decoding file to a text file

wooli

New Member
Hello All,

Newbie here who has a very "Basic" understanding of programming.

I would like your advice on the best programming procedure to do the following:

What I want to do is decode an existing "vertical" text file and convert it into a horizontal text file with headings etc.

No data will actually go into the spreadsheet, just create an adjusted text file using VBA.

I have a basic knowledge of how to open and read/write to files.( not sure of the formatting)

Here is a sample of the vertical input file
5=14
37=1091.142
38=1935.242
39=4.198
5=15
37=1000.000
38=2000.000
39=4.327
5=16
37=932.600
38=1945.071
39=3.767
2=12
37=1002.29
38=1874.70
39=4.242
62=13
37=1034.04
38=1940.45
39=4.399
21=64.1334
11=73.00
3=1.680
0=M
5=20
6=1.900
7=317.0145
8=89.5014
9=64.8956
37=1049.7748
38=1830.4695
39=4.2068

From the last 3 lines above, I want the output file to look like this:

1049.7748, 1830.4695, 4.2068 (there may be more on this line depending on the codes)

The numbers on the left of the equal sign are "codes" and the other side of the equal sign is the "data".

There are about 100 different "codes" and say 10 of which indicate a new "group". This would be either a new line or a different procedure.

Basic Preliminary Ideas
  • Open both files for input and output.
  • Read one line as a string and use a LOOP and LEFT, MID and/or RIGHT to extract the "code" and "value".
  • Use a LOOP and/or CASE on the "code" to decide what to do with the "data".
Questions
My programming dates back to Fortran & Basic (shows my age). I have no knowledge of object oriented programming.
  1. If I want to use a procedure called SPLIT to extract the code and data from each line, should this be a SUB or a FUNCTION or something else? I Googled it but it didn't help.
  2. Should I be using an array to store the data?
  3. I have no idea about pivot tables and VBlookup
  4. Would I be better off creating and referencing a third file with all the "codes"
Any advice you can give me will be greatly appreciated,

Wooli
 
Hello Deepak,

Additional information.


I'm a land surveyor and use 3rd party software for reductions.


I've never been happy with this software's options for data manipulation and reports and would like to develop my own workaround using excel/vba.


All I want is advice on the overall structure for a VBA/Excel program to extract and manipulate the data as a report and/or for further processing by other software.


After doing a field survey, the output is a vertical file in the following format:


50=190615
0=P20
0=P20
0=P20.1
23=1112
2=20
37=381.708
38=461.458
39=1.267
62=3
37=426.659
38=433.330


The number to the left of the '=' sign is a label and the number/text to the right is the data value.


When doing a survey, each station or reading may consist of 5 to 15 of the above codes for that particular group.


The start of each group is usually delineated by the labels 0, 5 or 61 (and sometimes others).


I've attached a file showing all the codes. (I only use about 30 of these codes)



So in the above extract I want to extract data and produce a file as follows:


20,461.458,381.708,1.267


From a program structure point of view, am I better off inputting all the codes to an Excel file and using vlookup (or similar) for program control, or should I just include all the codes in the program and use a CASE statement to control looping?

Thanks

Michael
 

Attachments

  • 190615.txt
    1.2 KB · Views: 0
  • labels.pdf
    75.6 KB · Views: 0
Sorry,

I wasn't aware of the cross posting issues.

I'll stay with VBA Express for the moment.

Thanks for you interest & responses.

Wooli
 
Back
Top