Import txt file with multiple rows for each record

[VanessaNY]

I have a royalty report from SAP that I need to bring into Access. It
includes check number, payment date, amount, and check recipient.

The problem is the "check recipient" information comes in on either 1,2,3,
or 4 lines in the report. But the rest of the record information is all on
line 1. This is random and will not be the EXACT same structure each month.

Ex.
2539893 3/14/2002 55.09 Jane Doe
Anytown USA
2539894 3/14/2002 79.99 Bob Evans
2539895 3/14/2002 5.00 John Smith
111 Main Street
Anytown USA

I need the end result to look like
2539893 3/14/2002 55.09 Jane Doe Anytown USA
2539894 3/14/2002 79.99 Bob Evans
2539895 3/14/2002 5.00 John Smith 111 Main Street Anytown USA

I have very little VBA or Macro experience, but willing to learn.

 

[Klatuu]

What is the format of the data? text file, excel spreadsheet, etc?

 

[John Nurick]

Hi Vanessa,

This can be done with a bit of coding (or maybe a lot of coding) but we have
to get the parameters clear first.

Can you work out whether those are spaces or tabs between the various items
on the first line of each record, and before the address information on the
subsequent lines?

What is the structure of the table you want to import this data into? Is the
name "Jane Doe" one item or two? Do you store the whole address in one
field, or do you need to parse the different bits of the address into
separate fields for Street, City, State, Zip?

Can there ever be international addresses, or additional lines in the
address? If so, what should happen?


If "Jane" and "Doe" are separate fields, do you ever get different kinds of
name, e.g. "Jane A. Doe", "Acme Agency Inc." or "James E and Phyllis Jones"?

 

[VanessaNY]

Per my Subject title, "txt" = text. Thanks.

What is the format of the data? text file, excel spreadsheet, etc?

 

[VanessaNY]

As far as I can tell, its just spaces between fields, and spaces before the
other half of the addresses.

I don't need to capture the names/addresses in any concrete structure, just
so long as they are all on 1 line. It would be nice to have name in one
field, and any remaining address in another. This will not be used for mail
merge or labels.. the $ amounts are the most key.

The name and address is not really key, just as long as I can get 1 record
per row.

 

[John Nurick]

In that case something like this should do the job. This is a procedure
that converts the input file (assuming that we've got its structure
right) into an ordinary comma-separated file that Access will be able to
import in the usual way with DoCmd.TransferText.

The code uses the VBScript regular expression object, which is available
on most modern Windows computers. Using it saves writing a lot of
tedious procedural code to separate out the various fields by reading
the line character by character.

Sub Vanessa(InFileSpec As String, OutFileSpec As String)
Dim lngIN As Long
Dim lngOUT As Long
Dim strLine As String 'line read from file
Dim strBuf As String 'line to output to file
Dim oRE As Object 'VBScript_RegExp_55.RegExp

lngIN = FreeFile()
Open InFileSpec For Input As lngIN
lngOUT = FreeFile()
Open OutFileSpec For Output As lngOUT

Set oRE = CreateObject("VBscript.Regexp")
oRE.Global = False
oRE.IgnoreCase = True
oRE.Multiline = False
oRE.Pattern = "^(\S+)\s+(\S+)\s+(\S+)\s+(\S.+)$"
'Pattern: first (\S+) captures the check number
' second (\S+) captures the date
' next one captures the amount
' (\S.+)$ captures to the end of the line (i.e. the name)

Line Input #lngIN, strLine 'read first line
'replace spaces with tabs
strBuf = oRE.Replace(strLine, "$1,$2,$3,""$4"",""")

Do Until EOF(lngIN)
Line Input #lngIN, strLine
If Left(strLine, 10) = " " Then
'Line begins with blanks and is therefore an address
'to append to the buffer
strBuf = strBuf & Trim(strLine) & " "
Else 'Line starts a new record, so we need to
'write the existing buffer to disk
Print #lngOUT, Trim(strBuf) & """"
'... and start building the next one in strBuf
strBuf = oRE.Replace(strLine, "$1,$2,$3,""$4"",""")
End If
Loop

'Finally, print the last record, which is still in the buffer
Print #lngOUT, Trim(strBuf) & """"

Close #lngIN
Close #lngOUT

End Sub

 

[Michael J. Strickland]

I have a royalty report from SAP that I need to bring into Access. It
includes check number, payment date, amount, and check recipient.

The problem is the "check recipient" information comes in on either 1,2,3,
or 4 lines in the report. But the rest of the record information is all on
line 1. This is random and will not be the EXACT same structure each
month.

Ex.
2539893 3/14/2002 55.09 Jane Doe
Anytown USA
2539894 3/14/2002 79.99 Bob Evans
2539895 3/14/2002 5.00 John Smith
111 Main Street
Anytown USA

I need the end result to look like
2539893 3/14/2002 55.09 Jane Doe Anytown USA
2539894 3/14/2002 79.99 Bob Evans
2539895 3/14/2002 5.00 John Smith 111 Main Street Anytown USA

I have very little VBA or Macro experience, but willing to learn.

If you have MS Word, you could open the file in Word.
Then use the replace dialog (Ctl-H) dialog to:

1. Replace all carriage returns (^p) with nothing (leave replace box blank).
2. Replace all occurrences of any seven digits (^#^#^#^#^#^#^#) with a
carriage return and the seven digits (^p^&).


--