Import Text File - can't insert any more field breaks?

[Guest]

hi

I am trying to import a fixed width text file using the wizard into Access
2000 - each line is 884 characters wide, 690 lines in the file. I have
defined 68 fields happily enough until I get to character 486 in the line,
when Access won't let me define any more fields. This is true whether I try
to use the field break feature of the Import Wizard, or manually create the
fields as part of an Import Specification.

I have placed a copy of the file at
http://homepage.ntlworld.com/elizabeth.walter/Access/x470tess03datadiff.txt

If someone could take a look and try to figure this out, I'd be very
grateful...

Thanks for taking the time to read this.

 

[John Nurick]

Hi Andy,

I've taken a look at the file and can't find anything in it that might
cause problems. And searching the newsgroups gives no hint there's
anything special about 68 fields (as far as I know the limit is 255).

The first thing to do is to make sure that you have installed the
current service packs for your version of Access.

If that doesn't fix it, there are other possibilities, including:

1) Try using a schema.ini file instead of an import specification. See
these links for more:
Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512
How to Use Schema.ini for Accessing Text Data
http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090
Schema.ini File (Text File Driver)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

This circumvents any possible problems with the import specification
system.

2) Set up an import spec with a smaller number of wider text fields and
use this to import the text file into a temporary table. Then create
another table with the actual fields you want. Finally, use an append
query to move the data, with calculated fields using Mid() and other
functions to parse the real data out of the fields in the temporary
table.

3) Use the txtnrm.pl utility at
http://www.j.nurick.dial.pipex.com/Code/Perl/index.htm
to convert the text file into a tall, narrow tab-delimited file that can
easily be imported into a normalised table structure in Access.