300 Fields Import?

[Guest]

I have a .txt file that currently holds cirica 250 fields and have an import
spec into Access 2003 that works fine. This .txt file is about to have
another 50 or so fields added to it and I know I'm going to run into
problems. Thoughts appreciated:

1. I import the .txt file as it stands, with one added field (the 50 or so
fields all as one). I then write some queries to split this one field into
the 50 addtional fields once it's inside Access
2. We import the .txt file into a SQL warehouse and then set up connections
from Access to the SQL database. Will this method allow Access to hold 300
odd fields in a table?

Please help!!!

 

[Jeff Boyce]

Andy

If you are trying to replicate your 250+ field structure in Access, you will
not get the best use of the features and functions Access offers.

A "wide" table design is typically ... a spreadsheet! Access doesn't do its
best when you feed it 'sheet data.

Access is a relational database, and is designed to work with
well-normalized data. I'll recommend, before you do anything else, that you
step away from the computer. Use paper and pencil to map out the things
about which you have (want to store) data ("entities") and how they interact
("relationships"). The persons-places-things-categories about which you
want to store characteristics ("attributes") are your tables, and the
attributes are your fields.

Now go back to Access and create tables and fields and relationships.

Import your current text file data to a temporary table, and use queries to
"parse" the raw data into the tables/fields you created.

Good luck!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

An Access table can have only 255 field. Period.

Options:

1. Got to the source of the text data and have them clean it up or give you
two text files with something like a primary key field so that you can join
the data back together in two Access tables. I'd try this first.

2. Excel 2007 can handle over 300 columns. You could import it into Excel
2007. Next create Named Ranges of less than 255 columns. Import these Named
Ranges into Access as different tables.

3. Manually fiddle with the text files before importing. For example
splitting the text files into multiple files or combining some of the text
file fields into fewer fields, such as a memo field, if the data really
should be in one field. This would be similar to your first idea. However
remember that there is a 2,000 character limit for each record (excluding
Memo fields) and 255 characters per text field. The extra 50 fields' worth of
data might put you over the top. A memo field might work; however, it's
difficult to parse out data in a Memo field.

 

[John Nurick]

Or use txtnrm.pl at
http://www.j.nurick.dial.pipex.com/Code/Perl/index.htm. This is designed
to convert text files with hundreds or thousands of fields into
something Access can import.

 

[Aaron Kempf]

Jeff

you're full of friggin crap

I'm sorry that MDB only supports 255 columns.
this is one of the main reasons that I moved to ADP a decade ago

You should broaden your horizons, kid
Just because MS Access has a limit; that doesn't make it a 'good thing'

Databases DO NOT NEEED to be normalized

 

[Aaron Kempf]

#1 reason that I moved to ADP a decade ago-- because I needed more than 255
columns

 

[Aaron Kempf]

Access Data Projects, make a direct connection from MS Access to SQL Server

this MDB crap has been obsolete for a decade