importing a delimited file with more than 255 columns

[David]

one of my colleagues is working with access trying to get it to import a
large file with about 300 columns yet when the file is imported it is
truncated at 255. Is there a work around or am I stuck with this limitation?
The text file is pipe delimited if that matters.

We're stuck and thought access would be a solution to this excel limitation!
help!

thanks you.

 

[KARL DEWEY]

A 'normalized' relational database would not need that many field. You need
to figure out how you need to reorganize the data and import the fields
needed for each table.
Give a little more information on the data and how it will be used so
suggestion can be made on how to get there.

 

[Douglas J. Steele]

No, Access still has a 255 column, with no easy work-around, even in the new
Access 2007 version. Ironically, Excel's capabilities were increased
significantly with the Office 2007 release.

 

[5th Amendment]

you can ALWAYS use Access Data Projects to use fields with more columns than
that

and any real ETL tool-- like SQL Server 2000 DTS (Developers Edition only
costs $49 and it works against MSDE great) will also let you use that many
columns

It is recccomended to not use Access MDB for any reason

 

[David]

i was afraid of that.

thanks Douglas!

d

No, Access still has a 255 column, with no easy work-around, even in the new
Access 2007 version. Ironically, Excel's capabilities were increased
significantly with the Office 2007 release.

 

[David]

reorganizing the data is not possible in this situation, thank you.

 

[Tony Toews [MVP]]

reorganizing the data is not possible in this situation,

Why not?

On the other system can you somehow create two queries or equivalent with the primary
key on to both queries. Then import the results into two tables?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Susie DBA [MSFT]]

the workaround is called 'Access Data Projects'

FILE, NEW, PROJECT (EXISTING DATA)

it can hold more than 255 columns; i've used that feature in a half-
dozen applications

 

[Susie DBA [MSFT]]

'on the other system' lol

in ETL we have this concept called a STAGING table

import it into SQL Server in a STAGING table and then use INSERT,
UPDATE, DELETE statements to push it into the real table

I've done that 1,000 times

 

[Tony Toews [MVP]]

the workaround is called 'Access Data Projects'

FILE, NEW, PROJECT (EXISTING DATA)

it can hold more than 255 columns; i've used that feature in a half-
dozen applications

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/