Importing Tab Delimitted Text

[MatMac]

Hi

I need to import a tab delimitted text file with approx 400 fields. However, it appears that only the first 255 fields of this file are available to import.


Is this a limitation of Access? I can't find this limit specified anywhere.

Previously I have imported fixed width text of a similar number of fields successfully, and there seems no such restriction.

Can anyone advise? Will I have to used fixed width again?

Thanks - Mat Mac.

 

[norman]

Hi

I need to import a tab delimitted text file with approx 400 fields. However, it appears that only the first 255 fields of this file are available to import.


Is this a limitation of Access? I can't find this limit specified anywhere.

Previously I have imported fixed width text of a similar number of fields successfully, and there seems no such restriction.

Can anyone advise? Will I have to used fixed width again?

Thanks - Mat Mac.

I found this on Microsoft Knowledeg Base Article 154070

The Microsoft Jet database engine has an internal limit of 255 fields per query. As the Microsoft Jet database engine iterates through the records in an update query, it creates a field for the original value and a field for the updated value. When more than 127 fields are selected, it reaches the 255 field limit of a query.

Norman

 

[MatMac]

Norman - Thanks for that. I was aware of the 255 field limit on queries on tables, and am importing these data into a number of tables to overcome the 255 field limit on tables. What I couldn't understand was why I could import a fixed with file of over 400 fields but not a delimitted one.

Mat Mac

 

[MatMac]

I found this on Microsoft Knowledeg Base Article 154070

The Microsoft Jet database engine has an internal limit of 255 fields per query. As the Microsoft Jet database engine iterates through the records in an update query, it creates a field for the original value and a field for the updated value. When more than 127 fields are selected, it reaches the 255 field limit of a query.

Norman

Norman - Thanks for that. I was aware of the 255 field limit on queries on tables, and am importing these data into a number of tables to overcome the 255 field limit on tables. What I couldn't understand was why I could import a fixed with file of over 400 fields but not a delimitted one.

 

[norman]

Mat

I have tried creating a table with greater than 255 fields and it wouldn't let me. (I created a table with one field and used the Alter table method to add fields)

I have also tried importing data using the import method and where the number of fields was greater than 255 it put all the data after the 255th field in the 255th field! This was using fixed lenght fields so I would be interested to know how you achieved it!

Norman

 

[MatMac]

Mat

I have tried creating a table with greater than 255 fields and it wouldn't let me. (I created a table with one field and used the Alter table method to add fields)

I have also tried importing data using the import method and where the number of fields was greater than 255 it put all the data after the 255th field in the 255th field! This was using fixed lenght fields so I would be interested to know how you achieved it!

Norman

I haven't been able to. My problem ocurrs when I use the GET EXTERNAL DATA command. The dialogue box shows my TAB delimitted file, but only the first 255 fields and none of the others. They are simply absent!

Mat.

 

[norman]

Previously I have imported fixed width text of a similar number of fields successfully, and there seems no such restriction.

Mat.

Mat

Sorry to confuse, I haven't got an answer to your problem, I was interseted to know how you had managed it for Fixed width fields as it wouldn't let me do that either.

Norman

 

[MatMac]

Mat

Sorry to confuse, I haven't got an answer to your problem, I was interseted to know how you had managed it for Fixed width fields as it wouldn't let me do that either.

Norman

I didn't do anything special. I had to import in stages to a number of tables though. Mat.

 

[MatMac]

Hello Again

Does anyone know about Access's limitations on text file size? Previously I have successfully imported (using GET EXTERNAL DATA) FIXED WIDTH data comprising well over 500 fields to a series of tables. Now I need to import TAB Delimitted text in the same manner. However, when the TAB file is viewed in the GET EXTERNAL DATA dialogue, only the firts 255 fields are visible (the file has over 400 fields). Is this an absolute limit or is there a way round it. (I don't really want to split the TAB file and import in stages, as this will ultimately be used regularly for new data and I want to keep the process as simple as possible.

Many thanks. MatMac.

 

[jeremie_ingram]

Have you attempted creating a linked table to the file? Then run a query to extract the data you need into a tbl within the mbd if you desire. If this is something you will be regularly updateing via delimited files, then this could be problematic, but if its a one time shot you may want to try it.

 

[WayneRyan]

MatMac,

You could use VBA as a last resort. That's a lot of fields per record, how
did you direct each "slice" of the records to the appropriate table before?
That's more fields than Access allows for one table.

Barring any miracles from our other members, post a sample of your table's
structure and an excerpt of the file and I'll give it a go.

Wayne

 

[Pat Hartman]

If you can have your import changed to fixed width you'll be able to automate the import by using several Import Spec's and the TransferText Method. This will allow you to import sections of the file at a time. Delimited files don't give you this option because Access uses the delimiters to determine the field breakdown. If you must use delimited files, you're going to have to brace yourself for a lengthy coding session to parse the input record via VBA.

 

[MatMac]

If you can have your import changed to fixed width you'll be able to automate the import by using several Import Spec's and the TransferText Method. This will allow you to import sections of the file at a time. Delimited files don't give you this option because Access uses the delimiters to determine the field breakdown. If you must use delimited files, you're going to have to brace yourself for a lengthy coding session to parse the input record via VBA.

Pat - thanks very much indeed for answering my question. This is indeed the way I have been previously importing data, as fixed width using multiple TransferText methods. I was hoping to use delimitted as setting up the spec is easier. I can however used fixed width again, so now I know I have little choice I shall go ahead and do so. Thanks again. Mat. (Bridge players really do seem to know all the tricks! Must have a rubber or two myself sometime - might improve my Access! Kind regards.)

 

[MatMac]

MatMac,

You could use VBA as a last resort. That's a lot of fields per record, how
did you direct each "slice" of the records to the appropriate table before?
That's more fields than Access allows for one table.

Barring any miracles from our other members, post a sample of your table's
structure and an excerpt of the file and I'll give it a go.

Wayne

Jerelie / Wayne

Many thanks for your replies. Jeremie - no I need to set up something automated for continuous use.

Wayne - sadly my VBA is not up to that, besides, my rational for using demitted was to make things easier not more difficult. I can used fixed width and now know I will have to. Pat Hartman in a separate thread advised me similarly, and I quote his reply below...

"If you can have your import changed to fixed width you'll be able to automate the import by using several Import Spec's and the TransferText Method. This will allow you to import sections of the file at a time. Delimited files don't give you this option because Access uses the delimiters to determine the field breakdown. If you must use delimited files, you're going to have to brace yourself for a lengthy coding session to parse the input record via VBA." - Pat.

Wayne - I appreciate your kind offer to have a go, but I shall go ahead and use fixed width as I have done in the past.

Thanks once again to everyone who has helped me with this. Kind regards

Mat.