importing text files

[brianjay]

I'm stumped. I have a .txt file that has a list of info:

First Name
Last Name
Address 1
Address 2 (if needed)
City
State
Zip Code
Expiration Date
Type of License
License Number

Each record may be 8 or 9 fields wide depending on if a 2nd address is needed. I can import the data to 1 long field; but I can't figure out how to move the data the their proper fields after I create them. Am I missing something simple or is this a programmers nightmare. A portion of the list is shown below. The first record requires 8 lines and the 2nd record needs 9 lines.


MARK A Cornholder

5400 PARK HEIGHTS AVE

Ballyhigh

MD

21235

2006-07-26

MASTER BARBER

16537
KWABENA A Wiggles

2505 WEST DERE
APARTMENT 1

Ballyhigh

MD

21280

2006-08-24

BARBER

211360


Thank you,
Brianjay

 

[Ed2020]

That's going to be a bit of a pain. Isn't it possible to get the file as a comma-separated text file?

If not then the first thing that springs to mind is to import to one long text field, then loop through each record in your temp table and use the split function (on a vbCrLf character) to split the data into an array. You can then loop through the array elements to write the data to a properly formatted table.

Ed Metcalfe.

Please do not feed the trolls.....

 

[PHV]

What are the field and the record separators ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SeeThru]

I'd turn it into a simgle flat file by seaching and replacing all paragraphs with tabs, and then tring to seach for something unique about the licence number and getting it to split after that.

Howverey, you may have to import it all into a single field table. Then step thruogh the table, checking the value of each field. When it looks like you have located a name (or have just finished a licence no), create a new record and continue filling in.

 

[CautionMP]

brianjay,
Here is a routine that will open the file and convert it to a CSV (comma seperated, double quote qualified) file based on the format of the two records in your original post.

It could be a little prettier (the logic duplicates when it deals with address line 2), but if it ain't broke...

I choose to output the data as a CSV file for the sake of demonstration (you can then link/import the file in Access or view the data in Excel after it is converted), if this works you could eliminate the creation of the CSV file and just dump the data directly into an Access table.

Sub ConvertStumpingFile()
Dim intInputFile As Integer, intOutputFile As Integer
Dim intField As Integer
Dim strDataFields(8) As String
Dim strCurrentLine As String
'Open the input file
intInputFile = FreeFile
'*** Be sure to update the following to match your real file ***
Open "[i]C:\StumpingFile.txt[/i]" For Input As #intInputFile
'open the output file
intOutputFile = FreeFile
Open "C:\StumpingFile.csv" For Output As #intOutputFile

'Loop through the entire input file
Do
  Line Input #intInputFile, strCurrentLine
  'Test to make sure the current line has text
  If Trim(strCurrentLine) <> "" Then
    strDataFields(intField) = Trim(strCurrentLine)
    'Field one should have the first line of the address
    'check the next line to see if there is a line 2
    If intField = 1 Then
      Line Input #intInputFile, strCurrentLine
      If Trim(strCurrentLine) <> "" Then
        'There is so store the value
        intField = intField + 1
        strDataFields(intField) = Trim(strCurrentLine)
      Else
        'There isn't so clear the previous data
        intField = intField + 1
        strDataFields(intField) = ""
      End If
    End If
    'Increment the field counter
    intField = intField + 1
  End If
  If intField = 9 Then
    'Create a fully qualified CSV string and write to the output file
    Print #intOutputFile, Chr(34) & Join(strDataFields, Chr(34) & "," & Chr(34)) & Chr(34)
    'Reset the field counter
    intField = 0
  End If
Loop Until EOF(intInputFile)

Clean_Up:
'Close all open files
Reset
End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[PWise]

what i noticed that in record 2 where there is 2 address lines there is no empty line between address1 and address2

5400 PARK HEIGHTS AVE

Ballyhigh

2505 WEST DERE
APARTMENT 1

Ballyhigh

if this is true thru the whole file this is a record with 8 fields with 2 vbCrLf as a record delimiter

try

Open "C:\StumpingFile.txt" For Input As #intInputFile
invar=Input(lof(#intInputFile), #intInputFile)
invar=replace(invar,vbcrlf+vbcrlf,",")