Too Many Columns to Import

[EZEason]

I have a tab delimited text file that I need to import into Access. The problem is that the file has about 350 columns. I only need about 60 of them. I have tried to set up a "Specification" for the import and skip the columns I do not need, but I still get a "Record is too large." error when I try to import it. Is there a way to import this data?

What doesn't kill you makes you stronger.

 

[bboffin]

You could use a text editor that has a keystroke macro capability (for example EditPlus or ConText) to remove all (or most) of the unwanted columns and then import the resulting file.

Alternatively the awk utility could handle this task with ease provided you have some knowledge of regular expressions.

If this is a recurring task and needs to be automated the awk solution would be best.


Bob Boffin

 

[BoxHead]

I'm not familiar with 'awk' but if you wanted to keep it all within Access, you could import the file as fixed length with a specification and use a function like the following to parse out the data you need. If you tell the function to find the 17th column of data, it will pull everything between the 16th and 17th tabs (Chr(9)).

Public Function findTabBreak(FieldNum As Integer, FldName As String) As String
[COLOR=green]''''' call as findTabBreak(6,[fieldName]) to grab the sixth field from the table[/color]
Dim preFld As Integer, postFld As Integer, myCount As Integer
myStr = FldName
myCount = 1
For x = 1 To Len(myStr)
If myCount = FieldNum - 1 Then preFld = x + 1
If myCount = FieldNum Then postFld = x - IIf(preFld = 0, 1, preFld)
If Mid(myStr, x, 1) = Chr(9) Then myCount = myCount + 1
Next x

[COLOR=green]'check to see if it is the first field[/color]
If preFld = 0 Then preFld = 1
[COLOR=green]'check to see if it is the last field[/color]
If preFld + postFld = Len(myStr) Then postFld = postFld + 1

findTabBreak = Mid(myStr, preFld, postFld)

End Function

Copy, paste and save the above into a new module.

Call the function in a query as:
SELECT findTabBreak(1,[field1]) AS FirstField, findTabBreak(4,[field1]) AS FourthField, findTabBreak(5,[field1]) AS FifthField, findTabBreak(11,[field1]) AS EleventhField, findTabBreak(31,[field1]) AS ThirtyFirstField, findTabBreak(356,[field1]) AS 356thField, findTabBreak(378,[field1]) AS 378thField, findTabBreak(400,[field1]) AS 400thField
FROM TabDelimTestStrings;

I tried this with a tab delimited text file with 400 columns. They are imported as one column named 'Field1'. My data only had a maximum of 4200 characters, but I set the specification to a 10,000-character fixed width.

I'd recommend that if you're going to use something like this, you would automate a make-table query and use CInt, CStr and CDate to properly format the data.




HTH


John







When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.