import text w/ 255+ fields... changing field names?

[Guest]

I recently got help on importing a text file with over 255 fields. The advice worked and I can import what I want, but I'm looking for a little extra help. Right now, I'm importing into a predefined table. The code is below. But, the fields in the text file may change somewhat over time and I'd like (if at all possible) to not have to rely on predefined field names in a pre-existing table. Is there a way I can either

1. Change the code so that the predefined field names are changed to what appears in the first line of data? Say the first line of data in the text file contains the following field names: Student, Country, Level. Can I change the highly generic predefined field names (field1, field2, field3) to those as the data are read in? Or even after the data are read in

or..

2. Create the table as the data are being read in and use the first line of data as field names? Instead of importing the data into a prefined table with generic field names that would need to be changed, can I create the table on the fly and define the field names as the first row of data within each column (right now, the data are read in column by column)

Here is the code I'm currently using

Dim dbs As DAO.Databas
Dim rst As DAO.Recordse
Dim strLine As Strin
Dim varArray As Varian
Dim myTextFile As Strin
' Replace the character in the next step with the actual delimiter that is used in the text fil
Const strDelimiter As String = "~
Set dbs = CurrentDb(
Set rst = dbs.OpenRecordset("tblImportData", dbOpenDynaset, dbAppendOnly
Open "C:\Datafile.txt" For Input As #
Do While EOF(1) = Fals
' read one line (record) from text fil
Line Input #1, strLin
' split the line into an arra
varArray = Split(strLine, strDelimiter
rst.AddNe
' write the values into fields in the recordset using the desired array element
For f = 0 To 6
rst.Fields(f).Value = varArray(f
Next
rst.Updat
Loo
rst.Clos
Set rst = Nothin
dbs.Clos
Set dbs = Nothin
Close #1

Thank
rachael

 

[John Nurick]

Hi Rachel,

Your (2) - create the table as the data is being read is - is probably
the way to go. Here's a snippet of code I've used to create a table from
a collection of field names and append records to it:

'Now get the field names so we can create an Access table
'We need them in a CREATE TABLE statement *and* in a list
'for later INSERT INTO append statements.
strSQL = "CREATE TABLE [" & AccessTableName & "] ( "
strFList = "("
For i = 1 To dsW.DataFields.Count
strFName = dsW.DataFields(i).Name
strSQL = strSQL & "[" & strFName & "] VARCHAR (255), "
strFList = strFList & "[" & strFName & "], "
Next
'finish off the strings
strSQL = Left(strSQL, Len(strSQL) - 2) & ");"
strFList = Left(strFList, Len(strFList) - 2) & ")"
'Create the table
dbD.Execute strSQL, dbFailOnError
DoEvents



You'd need to :

1) open your textfile

2) read first line and split into your varArray

3) create the table using code like my snippet, replacing the references
to dsW.DataFields (dsW is a Word.MailmergeDataSource) with references to
varArray, e.g.:
For i = 0 to UBound(varArray)
strFName = varArray(i)
...
Next

4) loop through the remaining lines of the textfile as you;re already
doing.

The operations to build strFList - a comma-delimited list of field names
- are redundant for your purposes. My code goes on to add individual
records to the table by building and executing SQL INSERT INTO
statements (an alternative to the recordset operations you are using),
and the list is needed for those.

 

[Guest]

I was hoping there was a way to do the 2nd thing. I'll play around with this. Thanks!

 

[Douglas J. Steele]

Yes, you can dynamically create the table and assign the field names. The
problem is that you also need to know what the datatype of each field is:
just knowing the field names isn't adequate.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I was hoping there was a way to do the 2nd thing. I'll play around with

this. Thanks!