Importing Large Text file

[Jackie]

Is there a way to import a text file that exceeds the limits of Access?

We have tried to use an import specification to "skip" all but 4 fields (approximately 15 characters total, but get an error message.

The import specification includes approx 500 fields, while Access' limit is 255 fields per table.

Our goal is to avoid manually editing the text file using Word Pad.

 

[kathryn]

Are you saying that you are unable to import only four fields from the text file? What is the error message?

Kathryn

 

[PaulF]

if this file uses a fixed length format, you can write code to read the four fields into your table. Just change the code to show correct tablename, path and filename of the text file, Field Names and the starting point and length of each field you are importing

Dim db As DAO.Database, rst As DAO.Recordset
Dim strField As String
Set db = CurrentDb
Set rst = db.OpenRecordset("TableName&quot
Open "Path And FileName" For Input As #1

Do While Not EOF(1)
Line Input #1, strField
If strField <> &quot;&quot; And strField <> &quot; &quot; Then
With rst
.AddNew
!Field1 = IIf(IsNull(Mid(strField, 1, 2)), vbNullString, Mid(strField, 1, 2))
!Field2 = IIf(IsNull(Mid(strField, 10, 2)), vbNullString, Mid(strField, 10, 2))
!Field3 = IIf(IsNull(Mid(strField, 21, 2)), vbNullString, Mid(strField, 21, 2))
!Field4 = IIf(IsNull(Mid(strField, 31, 2)), vbNullString, Mid(strField, 31, 2))
.Update
End With
End If
Loop
Close #1


PaulF

 

[Jackie]

I would like to import only 4 fields of the 255 fields.

 

[kathryn]

Jackie,

If you look at Paul's code, you can adapt it to your needs, if your text file is fixed length.

You have to edit it to have the fields start at the position that your four fields begin in your text file.

Paul's code takes the fields, each 2 characters long, which begin at position 1, 10, 21,31.

Look at your text file and find out where your fields begin and edit Paul's code accordingly.



Kathryn

 

[PaulF]

another, much simplier approach would be to combine several fields together in the import specification to reduce the number to less than 255. Then just import the 4 you want, and skip the remaining fields.

PaulF