Avatar of GPSPOW
GPSPOW
Flag for United States of America asked on

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?

Thanks

Glen
FI-Data-Extract-Actuals-2015-GlenPCopy.t
Microsoft AccessVBA

Avatar of undefined
Last Comment
GPSPOW

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
GPSPOW

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
PatHartman

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.
GPSPOW

ASKER
Figured out the problem before I got a response.
PatHartman

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
GPSPOW

ASKER
Ok.

Got it.

Thanks

Glen