Decode vertical text file to horizontal text file

[wooli]

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

[jonh]

Sub test()
    strfileIN = "C:\IN.txt"
    strfileOUT = "C:\OUT.txt"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    f = Split(Replace(fso.OpenTextFile(strfileIN).ReadAll, vbNewLine, "="), "=")
    With fso.CreateTextFile(strfileOUT, True)
        .WriteLine Join(StepArr(f, 0, 2), ", ")
        .WriteLine Join(StepArr(f, 1, 2), ", ")
        .Close
    End With
End Sub

Function StepArr(a As Variant, i As Byte, j As Byte) As Variant
    Dim newa()
    For k = i To UBound(a) Step j
        If k = i Then
            ReDim newa(0)
        Else
            ReDim Preserve newa(UBound(newa) + 1)
        End If
        newa(UBound(newa)) = a(k)
    Next
    StepArr = newa
End Function

[wooli]

Sub test()
    strfileIN = "C:\IN.txt"
    strfileOUT = "C:\OUT.txt"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    f = Split(Replace(fso.OpenTextFile(strfileIN).ReadAll, vbNewLine, "="), "=")
    With fso.CreateTextFile(strfileOUT, True)
        .WriteLine Join(StepArr(f, 0, 2), ", ")
        .WriteLine Join(StepArr(f, 1, 2), ", ")
        .Close
    End With
End Sub

Function StepArr(a As Variant, i As Byte, j As Byte) As Variant
    Dim newa()
    For k = i To UBound(a) Step j
        If k = i Then
            ReDim newa(0)
        Else
            ReDim Preserve newa(UBound(newa) + 1)
        End If
        newa(UBound(newa)) = a(k)
    Next
    StepArr = newa
End Function

[wooli]

Hi Jonh,

Thanks for your reply,

Your suggested programming is not what I want.



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?

(For some reason, I couldn't attach a TXT or PDF file but it worked when I combined them in a ZIP file)

Thanks

Michael

[p45cal]

Cross posted at http://forum.chandoo.org/threads/dec...xt-file.24769/
wooli, please read http://www.excelguru.ca/content.php?184 to understand why this is important - ultimately it will be to your benefit. Take it seriously.

Just to confirm, you want eastings, northings and elevation from each group where it's available?
In your 20,461.458,381.708,1.267 where does the 20 come from? Is it the label 2 (Station Number)? If so, do we take the values for codes for 37 and 38 immediately after the code 2 (there are 2 sets of codes 37 and 38 in your sample file for station 20).
I don't think you need a list of translations code no. to text, just a short list of codes which are important:
0, 5 and 61 (and sometimes others), we will need to know which others.
37,38,39 for the info you want to extract, along with any others,
These, as you suggest, can be used in the code.

What to do if there are 2 sets of 37 & 38 in the same station?
What to do for those groups where there is no code 37 & 38?

[wooli]

Hello p45cal,

I appreciate your advice and will only post on VBA Express.

I'm in no hurry, so I'll post basic questions in the hope of updating my antiquated programming skills to "Object-Oriented".


Questions

Do you think it would be best to input the entire input file into a 2 x (file length) array using the SPLIT command?

Or would I be better off using DO, IF, CASE etc to interrogate each input line?

Thanks,

[p45cal]

That depends on the answers to my question which you haven't addresed

[wooli]

Okay,

A typical field day:

P20 - Station setup
If it is a known station, it will have a code of "0=P20.1"
For a resection station the code will be "0=P20.2"
Each of these will obviously have different codes and label output.

P23 - Set-out: Look for marks and/or set-out points - Once again, different code arrangement depending on which one you are doing.

P24 - Set-out or record a point along a line.

P2 - Record detail/level points.

P30 - Establish a new station


Each of the above programs record redundant information by recording the station and back-sight information again when activated.

I could separate each field file in accordance with each program, but I would rather not do this.


The way I see it is that there about say 10 or less pointer codes to determine the program looping.

Perhaps we could start with P2 (Detail survey) and move forward from there.

Kind Regards,

Wooli

[p45cal]

This does not help me much - lots of info but I don't know how to use it, nor really what it means.
Let's start again, piecemeal:

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

So in the above extract I want to extract data and produce a file as follows:
20,461.458,381.708,1.267

where does the 20 come from?