parsing and importing a text file with records through a VBA macro

[dgr ion]

Hi all,

I'm really stuck in a problem in parsing and importing a text file
into an Excel sheet with a VBA macro.
I strongly need to do it by a VBA macro in order to postprocess the
resulting data through Excel.
I searched a lot into the network and I found something useful but I
can't achieve the final solution actually.

Can you help me please?
Your help is really appreciated.

My text file is containing several records formatted like this:

REC1_FIELD1 REC1_FIELD2 REC1_FIELD3
REC1_FIELD4 REC1_FIELD5 REC1_FIELD6

REC2_FIELD1 REC2_FIELD2 REC2_FIELD3
REC2_FIELD4 REC2_FIELD5 REC2_FIELD6

...

Keywords:
1) FIELDS are separated by space but they are also on different rows
(you can considered they are separated also by a "single"
linefeed);

example using C language syntax:
i.e. ...REC1_FIELD2" "REC1_FIELD3"\n"REC1_FIELD4" "REC...

2) RECORDS are separated by an empty row
(you can consider they are separated by a "double" linefeed).

i.e. ...REC1_FIELD6"\n\n"REC2_FIELD1...

I need to import each RECs on single row, separating fields in cells,
having an Excel table like this:
+-----+----------------------+----------------------
+----------------------+----------------------+----------------------
+------------------------
| |A | B |C |D |E |F
| 1 |REC1_FIELD1 |REC1_FIELD2 |REC1_FIELD3 |REC1_FIELD4 |REC1_FIELD5 |
REC1_FIELD6
| 2 |REC2_FIELD1 |REC2_FIELD2 |REC2_FIELD3 |REC2_FIELD4 |REC2_FIELD5 |
REC2_FIELD6
| 3 | | | | | |


FIRST STRATEGY:
The parsing strategy I was thinking is
a) replace the double linefeed ("\n\n") with an temporary char (i.e.
"@")
b) replace the single linefeed "\n" with tab char ("\t")
c) replace the "@" with the single linefeed "\n"

NOW THE FILE SHOULD BE IN THIS FORMAT:

REC1_FIELD1"\t"REC1_FIELD2"\t"REC1_FIELD3"\t"REC1_FIELD4"\t"REC1_FIELD5"\t"REC1_FIELD6"\n"

REC2_FIELD1"\t"REC2_FIELD2"\t"REC2_FIELD3"\t"REC2_FIELD4"\t"REC2_FIELD5"\t"REC2_FIELD6"\n"

d) import the text file reading line by line and filling by cells.

Is there a way to do all these replacement through a VBA macro?


SECOND STRATEGY:
a) begin to read text file into a string choosing the linefeed "\n" as
delimiter
b) Write the string into a cell and move on the cell on the right
c) until a second linefeed "\n" is not found:
do point (a) and (b)
else 'a second linefeed is found
move on the next row
do point (a) and (b)

Is there a way to do it through a VBA macro?

Any suggestions, examples, whatever is appreciated.
Thanks so much for your help and your time.

 

[Dave Peterson]

This seemed to work for me:

Option Explicit
Sub testme()

Dim FSO As Object
Dim RegEx As Object

Dim myFile As Object
Dim myContents As String
Dim myInFileName As String
Dim myOutFileName As String

myInFileName = "C:\test.txt"
myOutFileName = "C:\testout.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")

Set myFile = FSO.OpenTextFile(myInFileName, 1, False)
myContents = myFile.ReadAll
myFile.Close

Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Global = True
.IgnoreCase = False
.Pattern = vbCrLf
myContents = .Replace(myContents, "@")
.Pattern = "@@"
myContents = .Replace(myContents, vbCrLf)
.Pattern = "@"
myContents = .Replace(myContents, vbTab)
End With

Set myFile = FSO.CreateTextFile(myOutFileName)
myFile.Write myContents
myFile.Close

End Sub

Make sure that there are no @'s in your file...