wooli
07-30-2015, 03:43 AM
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.
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.
Should I be using an array to store the data?
I have no idea about pivot tables and VBlookup
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
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.
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.
Should I be using an array to store the data?
I have no idea about pivot tables and VBlookup
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