Importing or Linking Excel worksheet to Access as a table coming back with errors

I have a comma delimited txt and csv versions of a data set I am trying to either import or link in a MS-Access 2010 database.  The data set has over 100,000 lines.  This database was created in a pre-2003 version of Access but I have saved it to a 2010 version.

When I try to import the external data to the database, the 3rd field, FundCenter, does not copy over and I get NULL's at row 8550.  I have tried both the txt and csv versions of the data set and get the same result.  I have gone into the csv version and set the column to text without any success.  

I am trying to set up a macro to import the data automatically but if I cannot get it to work manually the macro is a mute point.

I have attached the txt file.  Can someone take a look and see what I need to do?


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I created an import spec and didn't have any problems.  If you don't create an import spec, the numeric codes get interpreted as long integer and loose their leading zeros and the amount fields get imported as text.

I deleted all but the first 10,000 rows to make the database small enough to attach.

When you do the import, press the advanced button so you can use the import spec I created.

If you do the import with VBA using the transferText method, reference the import spec I created.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GPSPOWAuthor Commented:

I figured out my problem.  I had to change the imported integer field to a double format.  In addition, the P1 Amt through P12 Amt fields were coming over as text.  I changed them to double also.  After that, all the fields imported without NULL values.

Thank you for looking at the table.

I figured out your problem too.  And you do NOT want to import the integers as double.  You want to import them as text.  They may look like numbers but they are text strings and importing them as double drops the leading zeros.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

GPSPOWAuthor Commented:
Figured out the problem before I got a response.
I answered your question 14 minutes after you posted it :)  That wasn't the point though.  I just wanted to make sure that you specify that the long numbers are text because if you specify they are numbers, leading zeros (if any) will be dropped.  Numeric fields do not support leading zeros.  Only text fields support leading zeros.  In the US, we use 5 digit "numeric" zip codes but here in New England we understand that zip code can not be defined as numeric or our zip codes become 4 digits instead of 5.  If you don't use a field for arithmetic, don't give it a numeric data type even if it only contains numbers.
GPSPOWAuthor Commented:

Got it.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.