How to convert text file into excel using macro vba

[tyme]

Dear all, i have a text file containing the following sample data. I would like to convert some of the data into excel file. Basically i just want the field name and the value to be converted into the excel. Can someone help me on how to convert it into excel using excel macro?

Sample text file content:

START-OF-FILE
REFNUMBER=12345
DATEFORMAT=yyyymmdd<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

START-OF-FIELDS
FIELD1
<o>FIELD2
FIELD3
FIELD4
.
.
.

FIELD120
END-OF-FIELDS

START-OF-DATA<o></o>
<o>VALUE1</o>
<o>VALUE2</o>
<o>VALUE3</o>
<o>VALUE4</o>
<o>.</o>
<o>.</o>
<o>.</o>
<o>VALUE120</o>
<o>END-OF-DATA
END-OF-FILE

Preferred excel format:

FIELD1 FIELD2 FIELD3 FIELD4 . . . . FIELD120
<o>VALUE1 VALUE2 VALUE3 VALUE4 . . . . VALUE120
</o></o></o>

 

[diddi]

hi and welcome to the board. try this...

Sub Convert()
    Open "c:/myfile.txt" For Input As #1
    While Not EOF(1)
        Input #1, mydata
        If mydata = "START-OF-FIELDS" Then
            For col = 1 To 120
                Input #1, mydata
                Cells(1, col) = mydata
            Next col
        End If
        If mydata = "START-OF-DATA" Then
            For col = 1 To 120
                Input #1, mydata
                Cells(2, col) = mydata
            Next col
        End If
    Wend
    Close 1
End Sub

 

[tyme]

Thanks a lot Diddi for your reply...

i've tried to use your code but apparently the text file content has changed a little bit..where the content of START-OF-DATA is separated by pipe (|)...example is as per below:

START-OF-DATA<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o>VALUE1|</o><o>VALUE2|</o><o>VALUE3|</o><o>VALUE4|....|</o><o>VALUE120</o>
<o>END-OF-DATA

</o>so, how can i convert it into excel?
<o></o>

 

[diddi]

is it just the data or the fields as well, that have the "|" delimiter?

 

[tyme]

Hi Diddi,

Only the data have the "|" delimeter. The sample is as per below:

START-OF-DATA
VALUEA1| VALUEA2| VALUEA3| VALUEA4|. . . | VALUEA120
VALUEB1| VALUEB2| VALUEB3| VALUEB4|. . . | VALUEB120
.
.
.
VALUE(N)| VALUE(N)2| VALUE(N)3| VALUE(N)4|. . . | VALUE(N)120
END-OF-DATA

 

[diddi]

try this

Sub Convert()
    Dim SplitData
    Open "c:/myfile.txt" For Input As #1
    While Not EOF(1)
        Input #1, mydata
        If mydata = "START-OF-FIELDS" Then
            For col = 1 To 120
                Input #1, mydata
                Cells(1, col) = mydata
            Next col
        End If
        If mydata = "START-OF-DATA" Then
            line Input #1, mylongdata
            SplitData = Split(mylongdata,"|")
            For col = 1 To 120
                Cells(2, col) = splitdata(col)
            Next col
        End If
    Wend
    Close 1
End Sub

 

[tyme]

Hi Diddi..tq for your prompt respond. I've tried the new code but i have 2 questions here:
1) why the code didn't convert the VALUEA1 into excel?..it only convert starting from VALUEA2, VALUEA3, VALUEA4 to VALUEA120 into the excel column A2, B2, C2 and so on.

2) If the START-OF-DATA have many lines, how can i amend the code so that it will convert all the data into excel?..sample as below:

START-OF-DATA
VALUEA1| VALUEA2| VALUEA3| VALUEA4|. . . | VALUEA120
VALUEB1| VALUEB2| VALUEB3| VALUEB4|. . . | VALUEB120
VALUEC1| VALUEC2| VALUEC3| VALUEC4|. . . | VALUEC120

Preferred excel format:

FIELD1 FIELD2 FIELD3 FIELD4 . . . . FIELD120
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>VALUEA1 VALUEA2 VALUEA3 VALUEA4 . . . . VALUEA120</o>
<o>VALUEB1 VALUEB2 VALUEB3 VALUEB4 . . . . VALUEB120</o>
<o>VALUEC1 VALUEC2 VALUEC3 VALUEC4 . . . . VALUEC120



</o>

 

[diddi]

is there 1 start of fields and 1 start of data followed by many lines of data?


for 1) change as follows

        If mydata = "START-OF-DATA" Then
            line Input #1, mylongdata
            SplitData = Split(mylongdata,"|")
            For col = 0 To 119 '<----------------------
                Cells(2, col) = splitdata(col)
            Next col
        End If

 

[tyme]

Hi Diddi,

a) I've tried your code for no. 1...but i changed Cells(2, col+1) = splitdata(col) so that the value is inserted starting into cells (2,1) instead of cells (2,0)...thanks a lot for this...

b) For my question no. 2, only 1 start-of-data followed by many lines of data in it...e.g.

START-OF-DATA
VALUEA1| VALUEA2| VALUEA3| VALUEA4|. . . | VALUEA120
VALUEB1| VALUEB2| VALUEB3| VALUEB4|. . . | VALUEB120
VALUEC1| VALUEC2| VALUEC3| VALUEC4|. . . | VALUEC120
.
.
.
VALUEX1| VALUEX2| VALUEX3| VALUEX4|. . . | VALUEX120
END-OF-DATA

 

[diddi]

ok here we go...

Sub Convert()
    Dim SplitData
    Open "c:/myfile.txt" For Input As #1
    Input #1, mydata
    If mydata = "START-OF-FIELDS" Then
        For col = 1 To 120
            Input #1, mydata
            Cells(1, col) = mydata
        Next col
    End If
    line input #1,mylongdata   '  delete the header line
    MyRow=1
    While Not EOF(1)
            MyRow=MyRow+1
            line Input #1, mylongdata
            SplitData = Split(mylongdata,"|")
            For col = 0 To 119
                Cells(MyRow, col+1) = splitdata(col+1)   '<--- good pickup!
            Next col
    Wend
    Close 1
End Sub

only thing now is to workout how the end of file data reacts to this.