Importing text that is spread over three lines (for each customer)

[Gary W.]

RE: Access 2002

Hi,

I have .txt files that I need to import into Access. The
data I need for each customer is spread out over three
lines of text (in the .txt documents). I want those
three lines of text to equate to one row in Access.

For example, the .txt document shows:

On Line 1: Customer Name (Joe Schmoe)
On Line 2: Customer Address (123 St)
On Line 3: Customer Purchases (ABC $125)

Since the information on all three lines is really for
just one customer, I want to import this data into one row
in Access (not three rows).

What I want:
(Joe Schmoe, 123 St., ABC $125)

I just need Line 2 and 3 data to be 'moved up' next to the
corresponding data on line 1.

Any ideas you can provide would be greatly appreciated.

 

[Ken Snell [MVP]]

Two approaches come to mind.

(1) Run a VBA procedure that modifies the text files by combining the data
from every three-line section and writing it into new text file. Then import
the new text file.

(2) Import the data using VBA code by opening the text file, reading the
three-line segment, and writing it directly into a recordset that is bound
to the data table.

Which would you prefer?

 

[Gary W.]

Thanks for getting back with me. Option # 1 sounds the
best. Unfortunately, I might be beyond my skill set at
this time, but I'm willing to give it a try.

Thanks again.

 

[Nikos Yannacopoulos]

Gary,

Here is some sample code to implement Option 1:

Sub Manipulate_Text()
Dim SourceFile As Text
Dim DestFile As Text
Dim readline As Text
Dim newline As Text

SourceFile = "C:\SomeFolder\SomeFile.txt"
DestFile = "C:\SomeFolder\NewFile.txt"

Open SourceFile For Input As #1
Open DestFile For Output As #2

Do Until EOF(1)
For i = 1 To 3
Line Input #1, readline
If newline <> "" Then newline = newline & ","
newline = newline & readline
Next
Write #2, newline
newline = ""
Loop

Close #2
Close #1

End Sub

A different approach would be to manipulate the file in Excel: import the
text file (in one column), move the imported column to B. use column A to
mark every third line, then copy column B to columns C and D, delete /shift
up C1 and D12, so every third column has a full record. Sort on column A
to get all the full records together, and delete the rest.

HTH,
Nikos

 

[Ken Snell [MVP]]

Here's some code to read one text file and write out every three-line block
as one new record in a new text file:

Dim strFileIn As String, strFileOut As String, strLine As String
Dim strNewLine As String
Dim intCount As Integer

' Path and file of text file being read/converted
strFileIn = "C:\MyFolder\TextFileName.txt"
' Path and file of text file being created
strFileOut = "C:\MyFolder\NewTextFileName.txt"

Open strFileIn For Input As #1
Open strFileOut For Output As #2

intCount = 0
strNewLine = ""

Do While EOF(1) = False
Line Input #1, strLine
intCount = intCount + 1
' Combine the line with the other lines of a group,
' using pipe character "|" as the delimiter
strNewLine = strNewLine & strLine & "|"
' If this is the third line of a group, write it out
' to the new file and reset the counter
If intCount = 3 Then
Print #2, strNewLine
strNewLine = ""
End If
Loop

Close #1
Close #2