reading from txt file into table

[Guest]

I'm trying to read data line by line from a delimited text file with over
1000 fields into a new table. I only want to bring in a handful of fields
from the beginning of the file (at this point). The actual number of fields i
bring in will vary from time to time, as will the total number of fields in
the file. But, i will always bring in all fields that don't start with "A".

I'm not going the normal "import text file with specifications" route
because the file changes over time, the names of the fields i want to import
change over time (except they'll never start with "A"), and since there are
over 1000 fields in the file I can't ever see all the fields in the
import/export wizard to determine which ones i want to import (not
necessarily a problem right now, since all the fields i'm currently focused
on are at the beginning of the file, but eventually i'll want to adapt this
to bring in other fields in the file).

I've gotten as far as reading in the first line from the file to create the
table where the data will be stored (because i won't know from the start how
many fields will be in the file, or how many i'll be importing, the table
isn't created beforehand). This is what i have so far:

strSourceFile = "C:\Data.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
iCnt = 1
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
strArray = Split(strInText, "~")
Select Case iCnt
Case 1
x = 0
For i = 0 To UBound(strArray)
If Not strArray(i) Like "A*" Then
fld = strArray(i)
With tdf
.Fields.Append .CreateField(fld, dbText)
End With
x = x + 1
End If
Next i
db.TableDefs.Append tdf
numFields = x
End Select
iCnt = iCnt + 1
Wend
Close lngInputFile

I'm new at this, so i'm stuck on how to actually import the data into the
table. Do i continue reading the data in line-by-line... and if so, what do i
need to do? I've looked up help on recordsets, but i'm still at a loss. Is
there another way i could/should go about this?

Thanks

 

[Allen Browne]

Hi Rachel

The approach you are using does work, but there may be a simpler way.

You may be able to attach the text file:
File | Get External | Link
Then you can create a query into the text file, and use an Append query
statement to actually append the fields you want to the target table. You
can create an Append query statement (Append on Query menu in query design),
then switch it to SQL View (View menu) to see exactly what you need to
achieve, and build your string in VBA to look like that.

Where attaching is not powerful enough, we sometimes import the data from
the text file into a temporary table, so we can add a primary key (keeps
them in order), fill in or remove the blanks, and manipulate the results.

The ImportSpec can be useful, though you say the fields in the text file are
not consistent. To create/modify an ImportSpec, use the Advance button once
you have the Import Text query running (through File | Get External).

 

[Guest]

Won't I still run into the problem of having well over 255 fields in the text
file when i try to link to it? When i link to the file and try to create a
query in design view, i just see the first 255 fields. Right now i'm just
trying to access the first 150 or so fields, but i do have a situation in
mind where I'll want to access fields past the 255 limit. I think i can tease
out those fields if i'm parsing the text file as i'm reading it in, but don't
think i can do it with a link. Unless i'm missing something (which is
definitely possible!)?

rachel

 

[Allen Browne]

Sorry: I obviously failed to read your question carefully.

Your question was about how to loop through the fields, matching the member
of the variant array. This is the kind of thing you're after, I think:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rs As DAO.Recordset
Dim strSourceFile As String
Dim lngInputFile As Long
Dim strInText As String
Dim varInputLine As Variant
Dim iFieldCount As Integer
Dim i As Integer

'Open the file, and get the first row as field names.
strSourceFile = "C:\Data.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile
Line Input #lngInputFile, strInText
varInputLine = Split(strInText, "~")

'Create table from first line.
Set db = CurrentDb()
Set tdf = db.CreateTableDef("MyTempTable")
With tdf
For i = 0 To UBound(varInputLine)
.Fields.Append .CreateField(varInputLine(i), dbText, 255)
iFieldCount = iFieldCount + 1
If iFieldCount >= 255 Then
Exit For
End If
Next
End With
db.TableDefs.Append tdf

'Append the following lines as records.
Set rs = db.OpenRecordset("MyTempTable", dbOpenDynaset, dbAppendOnly)
While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
varInputLine = Split(strInText, "~")
rs.AddNew
For i = 0 To rs.Fields.Count - 1
rs(i) = varInputLine(i)
Next
rs.Update
Wend

'Clean up
rs.Close
Close lngInputFile
Set rs = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

 

[Guest]

Thanks, Allen! That's exactly what i needed! It works like a charm.
rachel

 

[JBHansen]

I know this is an old thread, but it's exactly what I needed, with one
exception -

I'm using this code to read a file and insert its contents into a temp
table. However, there are smatterings of null values throughout my file, so I
want to see if it's possible to either 1) allow the table to accept null
values, or 2) change the code to insert some dummy character "x" whenever a
null value pops up.

I've tried using the Nz function but obviously can't figure out where to put
it because I keep getting the same error - "3315 - Field
'MyTempTable.somefieldname' cannot be a zero-length string" on the
'rs.Update' line.

Any ideas? Thanks!

jbhansen

 

[JBHansen]

Okay, I figured it out...

Changed this line:

.Fields.Append .CreateField(varInputLine(i), dbText, 255)

to:
Set fld = .CreateField(varInputLine(i), dbText, 255)
fld.AllowZeroLength = True
.Fields.Append fld

(Just had to know what to search for and it jumped right out at me!)

jbhansen