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...

 

[DanDungan]

Hi,

I have a similar need to parse a file. Today, I googled regex, and I'm trying to understand the code Dave supplied. I don't yet understand how the code works.

I ran it on my file using excel 2000.

The records were moved to the same line.
The fields were not tab delimited. They were still space delimited and the first record of each page of the report were on the same line as the header.

I'd like to get each record on one line with the fields tab delimited.

This file is a report generated by our database. I can print the report to a file and ftp it to my computer as a text file.

I'm going to paste the First page and the first record of the second page below.

I'm looking forward to your suggestions.

Thanks,

Dan


SYMIX 4.1R2.00 2 ORDER STATUS REPORT 04/05/11 10:55
CO Type: RB
CO Status: POSCH
CO Line/Release Status: POFCH
Print Line/Release Text: No
Destination: F /usr2/dungan/input.txt

Starting Ending
Order: 80000
Customer:
Order Date:
Cust PO:

Item:
CI:
Due Date:
Last Ship Date: 03/01/11 03/31/11
Whse:
SYMIX 4.1R2.00 2 ELECTRO ADAPTER, INC dungan 04/05/11 12:41:57
CO01-R O R D E R S T A T U S R E P O R T Page: 1

Name Description Reserved Cust Item
Order Customer Ordered Line Rel Item Qty Ready Shipped Due Date Last Ship Reference
Stat Cust PO Stat Qty Ordered Invoiced U/M
------- -------- -------- ------- ------------------------------ ----------- ------------ --------- --------- -----------------
ACIFIC SCIENTIFIC Handle 4 Vane Rotary BATCH# 50360.1 0.000 Kanban Rel# 2157644
88132 1149 12/19/08 1 1111419-03 0.000 243.000 12/31/11 03/07/11 J 619090-000
O DZ3386BP O 250.000 243.000 EA

LABARGE INC. REV AJ REF 28540S08040451 0.000
88914 1109 02/19/09 3 80-04127-156 0.000 5.000 03/31/11 03/28/11 J 621638-000
O B207036 F 5.000 5.000 EA

OPTICS 1, Inc. GRIPPER RAIL ANODIZED BATCH# 55614.1 0.000 Change to Rev. A
90799 3297 02/22/10 6 13181-1013 0.000 1,000.000 11/01/10 03/15/11 J 647376-000
O 14133 F 1,000.000 1,000.000 EA

!AIR-IAI PO LINE 4 0.000
91064 3366 11/05/09 3 2654010000556 0.000 30.000 03/29/11 03/31/11 J 633151-000
O H000027096 F 30.000 30.000 EA

PO LINE 6 0.000
5 2654012000556 0.000 57.000 03/29/11 03/31/11 J 633153-000
F 57.000 57.000 EA

PO LINE 8 0.000
7 2654014000556 0.000 30.000 03/29/11 03/31/11 J 633155-000
F 30.000 30.000 EA

PO LINE 12 0.000
10 2654018000556 0.000 39.000 03/29/11 03/31/11 J 633158-000
F 39.000 39.000 EA

PO LINE 14 0.000
12 2654022000556 0.000 12.000 03/29/11 03/31/11 J 633160-000
F 12.000 12.000 EA

PO LINE 16 0.000
14 2654024000556 0.000 27.000 03/29/11 03/31/11 J 633162-000
F 27.000 27.000 EA

PO LINE 24 0.000
19 901-704120556 0.000 30.000 03/29/11 03/31/11 J 633036-000
F 30.000 30.000 EA

0.000
21 901-704140856 0.000 33.000 03/29/11 03/31/11 J 633038-000
F 33.000 33.000 EA

PO LINE 34 0.000
26 A265040456 0.000 33.000 03/29/11 03/31/11 J 633041-000
F 33.000 33.000 EA

PO LINE 38 0.000
29 A265060556 0.000 72.000 03/29/11 03/03/11 J 633044-000
F 72.000 72.000 EA

SYMIX 4.1R2.00 2 ELECTRO ADAPTER, INC dungan 04/05/11 12:46:41
CO01-R O R D E R S T A T U S R E P O R T Page: 2

Name Description Reserved Cust Item
Order Customer Ordered Line Rel Item Qty Ready Shipped Due Date Last Ship Reference
Stat Cust PO Stat Qty Ordered Invoiced U/M
------- -------- -------- ------- ------------------------------ ----------- ------------ --------- --------- -----------------
!AIR-IAI PO LINE 42 0.000
91064 3366 11/05/09 32 A265070756 0.000 54.000 03/29/11 03/31/11 J 633047-000
O H000027096 F 54.000 54.000 EA