Importing a .TXT document

  • Thread starter Thread starter glnbnz
  • Start date Start date
G

glnbnz

I have a .txt document I would like to import into an Access table. The
problem I am having is that 1 record is distributed over 3 or more lines like
the example below:

A120250000200 Bnz,Gln etal Smith
B0 38.63090 -88.67847330 N 462 E SE NE
C 1354BRLW ISGS 2523.000
N 1 0.000 0.000E 0.000N 0.00

Each line is represented by the first letter of the line A, B, C, & N.
Another problem I am having is with the C line. There is sometimes more than
one or one or none. Each record has at least an A, B, & N.
Thanks in advance.
 
You're going to have to write VBA code to read the file line by line (look
at the Line Input # statement), build up a SQL Insert Into statement and run
it.
 
I am sorry Doug, but I am not sure where I can do this. My experience goes
to using Access to import the table. Would I build the table first then
write the SQL Insert Into statement as a query? Where would I then write the
Line Input # statement?
 
Hi,

What Doug means is the old DOS version of file I/O which happens to work well in Access (doesn't require adding any extra references, etc.)

First of all, this appears to be 4 tables in an Access world. Not sure what, if any, delimiters are here? This could be a fixed length file based on appearance but without seeing at least a handful of lines, it's pretty hard to tell. You need to make the tables, figure out how long each field is (if fixed length rows) or what the delimiter is and detect that.

At the bottom of this message is some air code - once you know the exact format for each line and create a myParser function to parse out the appropriate chunk, this could get you rolling.

Note you will have to replace the Const values with your appropriate data -

The myParser function is passed the letter char for the type of line, the row of text, and the ordinal count of the field/column we want to retrieve. myParser should return a variant containing the appropriate data. Here is a declaration template:

Public Function myParser(strType as string, strRow as string, intOrdinal as integer) as Variant

Hope this helps - if you get stuck, email me a copy of the text file and I will try to build the myParser function for you. (gllincoln at live dot com)

Cordially,
Gordon

=================================================================================
Public Function ImportUglyFile() As Boolean

Const ImportFile As String = "C:\myfolder\myfilename.txt"

Const ATable As String = "ATable"
Const BTable As String = "BTable"
Const CTable As String = "CTable"
Const NTable As String = "NTable"

Const AFieldCount As Integer = 1 '???
Const BFieldCount As Integer = 1 '???
Const CFieldCount As Integer = 1 '???
Const NFieldCount As Integer = 1 '???

Dim fh As Integer 'file handle
Dim x As Integer 'loop counter
Dim s As String 'current row of text

Dim rsA As DAO.Recordset
Dim rsB As DAO.Recordset
Dim rsC As DAO.Recordset
Dim rsN As DAO.Recordset

Set rsA = CurrentDb.OpenRecordset(ATable)
Set rsB = CurrentDb.OpenRecordset(BTable)
Set rsC = CurrentDb.OpenRecordset(CTable)
Set rsN = CurrentDb.OpenRecordset(NTable)

fh = FreeFile()

Open ImportFile For Input As #fh

Do While Not EOF(fh)
Line Input #fh, s
s = Trim(s)
Select Case Left(s, 1)

Case "A"
rsA.AddNew
For x = 1 To AFieldCount
rsA.Fields(x - 1) = myParser("A", s, x)
Next x
rsA.Update

Case "B"
rsB.AddNew
For x = 1 To BFieldCount
rsB.Fields(x - 1) = myParser("B", s, x)
Next x
rsB.Update

Case "C"
rsC.AddNew
For x = 1 To CFieldCount
rsC.Fields(x - 1) = myParser("C", s, x)
Next x
rsC.Update

Case "N"
rsN.AddNew
For x = 1 To NFieldCount
rsN.Fields(x - 1) = myParser("N", s, x)
Next x
rsN.Update

End Select

Loop

Close #fh
Set rsA = Nothing
Set rsB = Nothing
Set rsC = Nothing
Set rsN = Nothing

ImportUglyFIle = True

End Function
 
Back
Top