problems importing text file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone, I have a text file with client and device information. I need
to import the information into Access but am having trouble. The consumer
information is located on four lines. Therefore, the device information is
located in another row instead of a column. How do I get the consumer
information all in one row so I ?

The text file format is:

Contact ID Salutation First Name Middle Name Last
Name Address1 Address2 City State Zip Phone
Birthday Type Create
Date Telemarket Mail Serial Number Side Model
Circuit Type Expiration
Date Registration Date Serial Number Side Model Circuit
Type Expiration
Date Registration Date
 
Because of the line wrap on your post, it's not easy to determine the format
of your file, but I understand it has 4 lines per record. It might be
easiest to process the file before importing, so that the 4 lines are
collapsed into one.

The example below is aircode only. What it aims to do is read your source
file, and generate another file that has only one line per record. You can
then import this new file easily using TransferText.

The code needs to be tested and debugged, and error handling added. It also
needs to handle the final lines of the source file if the number of lines is
not an exact multiple of 4. It assumes that the data is delimited, but that
you need to add the comma separator at the ends of the lines.

Hopefully this is enough to get you on the right track:

Function ProcessFile()
Dim strLineIn As String 'One line from the source file.
Dim strLineOut As String 'Line to write to the output file.
Dim lngLineCount As Long 'Number of lines processed.
Dim lngLen As Long 'Length of string.
Const strcSep = ", " 'Separator to tack onto the end of the
lines.

Open "C:\MySourceFile.txt" For Input As #1
Open "C:\MyOutputFile.txt" For Output As #2

'Loop through all the lines of the input file.
Do While Not EOF(1)
'When 4 lines have been read, write to output file.
If lngLineCount Mod 4 = 0 Then
'Chop off the trailing separator.
lngLen = Len(strLineOut) - Len(strcSep)
If lngLen > 0 Then
Print #2, Left$(strLineOut, lngLen)
strLineOut = vbNullString
End If
End If

'Read the next line, and concatenate to the output string.
Line Input #1, strLineIn
strLineOut = strLineOut & strLineIn & strcSep
lngLineCount = lngLineCount + 1
Loop

Close #2
Close #1
End Function
 
Thanks Allen, I really appreciate the advice. It's always helpful when
someone points you in the right direction. You're the best!
 
Back
Top