Import textfile - over 255 fields (1 Viewer)

[bobfin]

I'm trying to import data from a comma-delimited textfile. The first record has fieldnames, all fields are text, and the text qualifier is double quotes. Since there slightly over 255 fields, I've defined 2 import specs. One spec skips the last bunch of fields and the other spec skips the first bunch of fields. This system has worked on another vendor's files, resulting in the data dumped into 2 tables. But in this case, the data in each record is divided completely wrong. Only a single character is in the first field, the rest of the first field and the beginning of the second field are in the second field, etc. I'm running Access 2003 and the database is Access 2000.

I've checked the file in both WordPad and Word and the data looks fine. The first field has 5 digits inside double quotes, followed by a comma, then the second field with 7 digits in double quotes. I would have thought that the comma-delimited feature combined with the text qualifier would have made the import a breeze. Any ideas why the data is shifted?

 

[WayneRyan]

Bob,

You may have to parse it with VBA. Or maybe it's just the type of record
terminators used.

Can you post a small sample of it?

Wayne

 

[bobfin]

Bob,

You may have to parse it with VBA. Or maybe it's just the type of record
terminators used.

Can you post a small sample of it?

Wayne

Reply: The previous import spec that worked was for a fixed-width text file. I think it worked because I could specify more than 255 fields by entering the start position and width. But you can't fool Access into accepting extra fields in a comma-delimited text file. I did find, however, that I could import the file into Excel 2003. From there I can import the data into Access. Excel's Import Text Wizard lets you specify which fields to skip just like Access. Unfortunately, Excel doesn't let you save import specs, as far as I can tell. That means I have to manually click on all the skip fields everytime I do an import. Then import the spreadsheet into Access. Since the files will be produced daily, this is not a very good work-around. Any further suggestions would be appreciated.