255 column limit on an Access Table

[nikotromus]

Hey Guys,

I have a big problem that I have been wrestling with for quite some time. I am trying to import a text file into Access that has more than 255 fields. Do you have any suggestions on how to get around this? Any help will be greatly appreciated.

Ryan

 

[Newman]

Create two tables.
Bring half the datas in the first one, and the other half to the second one.
Add the primary key to both tables and link them together.

I suspect that there is something wrong with the tables, 'cause you should make sure your tables have the least number of columns possible. You may have to look into creating many more tables using normalization. Make a search with «Normalization» for more informations.

 

[nikotromus]

I will be happy to break up the text file once I read it into Access. The problem is, I have to read it into Access first before I can break it up. It wont let me read in more than 255 columns

 

[Newman]

You'd have to transfer the first 255 columns, then create a program that erase these columns from the text file to allow you to transfer another 255 columns.
For now, I don't have much time to look out for the code needed to erase the columns, but I'm sure someone else would be glad to help you out from here, if needed.
Good luck!

 

[nikotromus]

If I could transfer half of the columns in the text file to another text file, that would solve all of my problems. Then I could import both of the flat files seperately. I don't know of a tool that will allow me to do that.

 

[Pat Hartman]

When you use the import wizard it allows you to uncheck columns that you don't want to import. Try that.

If your import file is fixed width the task is easier. You can lump large numbers of columns together and ignore them.

 

[nikotromus]

When I am importing a file and I am on the screen where it give me the option to skip a field from importation, it will not allow me to select any field except the first field. The screen gives you field options at the top, and it lists the fields at the bottom. This would work if it would allow me to select different fields so that I could check the box that would skip it from importation.

 

[ecniv]

Hi,

You need to code in VBA and import the line of data, putting it into several tables as required.

Why you'd have more than 255 columns is beyond me, but I guess somewhere out there it has to happen, and you've been unluck enough to get it

Good luck solving it.

Vince

 

[nikotromus]

Again, this is data in a text file. It was produced by a reporting system. I don't work with tables that are 255 columns long. I just need a way to read in this flat file, and then I can break it up from there.

 

[bjackson]

Can you post a sample of the txt file ?
It would be easier to make a suggestion if you know what the txt file looks like.Is it fixed width,comma delimited etc
Regards
Bjackson

 

[nikotromus]

The file has some phone numbers of customers that I can't post. It is a fixed width, tab delimited file.

Thanks for the interest.

Ryan

 

[bjackson]

Well here is my suggestion for a tab delimted txt file

make 2 or 3 or 4 or however many copies of the txt file you want to import.Open access and create a linked table to the first txt file,Selecting only the first 40 columns and unchecking the rest.create a linked table to txt file no 2,again select columns 41 to 80 with all other columns unchecked, keep doing this until your have imported all of your columns.
now you have the txt file broken up into however many tables you may wish.You could even import in such a way that would group your data for you, eg Customers,phone numbers etc and name your linked tables to suit

As Pat Hartman said access gives you the option of which columns to import.

if its a fixed width file you have to create the column widths to suit your data before you get the option of which columns to import

Regards
Bjackson