Importing a text file that exceeds the column limit in Access

[hcisteve]

I have a text file that I want to import from a survey program that has 378 columns. The column limit in Access seems to be 256 columns. Is there a way to change the column limit in Access? The people I am working with want an easy method to import this that will not take up much time on their part.

Thanks,

Steve

 

[RoyVidar]

As far as I know, there's no way to alter the column number limit of an Access table. What you can do, is just create more tables...

378 columns doesn't seem very normalized - if I'm correct, then there's of course a question of at which time one wants the delays - when importing (once a day/week/month) or each time one is trying to get something useful out of the data...

Roy-Vidar

 

[bkclaw113]

You may need to look into using some other technology to import your file. If you have it available SAS works well with large text files. I have never run into a limit with column size and I have worked with data feeds containing up to 500 columns

 

[drctx]

you might try this link.

http://support.microsoft.com/default.aspx?scid=kb;en-us;872914

i had a problem importing a file, once it fixed the file i could import it and create the import spec. after i had the import spec i don't need to fix the file every time it's imported.

 

[MichaelRed]

Ms.A. (along with many relational dbs) has a limit on the number of "FIELDS" allowed in a recordset. This is often set at 255, but that doesn't mean that you should ever define a table with even close to this number of fields as Ms. A. (along with many ... ) counst each instance of a field (name) or table (name) or literal value (& who knows what else) as a field when constructing inidices and / or queries.

The reference to normalizing the data should be persued agressively. If, perchance, you are new to the world of relational databases, it will be imperative that you get and study a tome (or even a text) which defines, llustrates and explores this thoroughly before continuing with this exercise. Failure to do so WILL generate frustration.

The good news is that it is actually quite easy -in most instances- to get the information into Ms. A. once you haver passed the afore mentioned obsticals.



MichaelRed

 

[axism]

hmm did u try changing field data type maybe? like to varchar?

 

[earthandfire]

axism, I'm not aware that Access has a data type of varchar?

 

[hcisteve]

The text import just fails whenever the number of columns is greater than 256. As far as normalizing goes -- that would be great but from a practical standpoint I just want to load the data quickly without spending a lot of time on it. I am more interested in running reports and looking at the data on a form rather than doing a lot of queries. I did see one thing on the Excel forms that seems to help -- there is a utility that imports text files -- you have to create two profiles with the columns you want to import -- it works with both Excel and Access -- the URL is

http://www.beside.com/?ref=overture&g=import

The profiles are tedious to create but once they are created the import process goes quickly.

 

[MichaelRed]

something along the lines of " ... make haste slowly ... " will actually get you there a LOT faster. You CANNOT generate or load a table (or any recordset) in Ms. A. with this many fields. You have been advised of this several times in this thread. Continuing along the apparent path does no one any good, least of all you. I'm sorry you feel that you can ask the unwilling to do the impossible and get quick answers. At least in my experience, this has seldom been a positive experience.




MichaelRed