Importing Text File

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I am using Access 2002.

The following text is similar to what I am attempting to
import:

CUSTOMER: XXXXXXX ABN AMRO (F.K.A ATLANTIC MTG.)

-------------------------------------------------
AGENCY AGENCY NAME TAX TYPE
NUMBER
-------------------------------------------------
XXXXXXXXX BETHEL TOWN * 02 TOWN
XXXXXXXXX DANBURY CITY * 01 CITY
XXXXXXXXX FAIRFIELD TOWN * 02 TOWN
XXXXXXXXX RIDGEFIELD TOWN 02 TOWN
XXXXXXXXX TRUMBULL TOWN * 02 TOWN
XXXXXXXXX WESTPORT TOWN * 02 TOWN
XXXXXXXXX BRIDGEPORT CITY ELDERLY 01 CITY
XXXXXXXXX HARTFORD CITY * 01 CITY
XXXXXXXXX SALISBURY TOWN 02 TOWN
XXXXXXXXX SHARON TOWN 02 TOWN
XXXXXXXXX MERIDEN CITY * 01 CITY
XXXXXXXXX ANDOVER TOWN* 02 TOWN
XXXXXXXXX BROOKLYN TOWN 02 TOWN
XXXXXXXXX KILLINGLY TOWN * 02 TOWN

I am wondering if there is a way to have it automatically
place the CUSTOMER number in front of each record instead
of having to do it myself. Thanks for any help!!!

Michael
 
You cannot use a wizard to import a file of this structure.
The wizard assumes a table like structure for ASCII files.

If you are guaranteed to get the file in the same structure every time and
you need to do this more than once, then you should write code to import the
file.

Here is some sample code to get you started:

Public Sub ImportFile(strPath As String)

Dim db As Database, rs As Recordset
Dim sLine As String, sTrimmed As String
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1

'Read a single line from an open sequential file and assign it to a String
variable.
Line Input #1, sLine
'Trim the leading blanks
sTrimmed = LTrim(sLine)

Do While Not EOF(1)
'read the next line of the file
Line Input #1, sLine
sTrimmed = LTrim(sLine)

'manipulate the string if necessary, then add it to the rs table.
If rs.BOF = True Then
rs.AddNew
Else
rs.Edit
End If
rs.Update
Loop
End Sub
 
Not without coding.
You will have to use the word "CUSTOMER" as a delimiter.
You would read a line, see if it starts with the word, then collect the ID.
Read and skip lines until you get to the first "record"
Import it, parse it and add it and the ID to a new record.
Do this until you find the word Customer again and start over.

If this is a one time event I'd import the whole thing into a table using
fixed lengths. Base those lengths on the customer line and make ID and the
name fields.
(Field1 would be the word customer:, field 2 the ID and field three the
name.)
Then delete or don't import field 1.
Sort on field 2 and manually delete the odd ball records.
Then make the ID a key and get rid of dupes.

Not hard but a good bit of busy work.
 
Back
Top