Import CSV file to Access - Doesn't import Negative Numbers

I'm using this to import a CSV file into a table in Access 2003:
DoCmd.TransferText acImportDelim, "LOC2010", "T-LOC Current Import", strRPTLoc, True, ""

It works for everything except when there is a negative number in the Amount field.  Another table gets created with the import errors and it says Type Conversion on the Amount field.
The data type for the Amount field is Number and the Field Size is Double (see Table Structure snapshot).  Also attached is the Import Spec I'm using (LOC2010).

How can I fix this?

Thanks for any assistance!

Spec.jpg
Table-Structure.jpg
LVL 24
TracyVBA DeveloperAsked:
Who is Participating?
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.

LadyHagoodCommented:
0
Helen FeddemaCommented:
You might need to import the data into a temp table, and then use a query to do the necessary data type conversion, for example using Nz() to convert Nulls into zeroes.
0
TracyVBA DeveloperAuthor Commented:
LadyHagood: Thanks, but the negative sign is not trailing, it's -20,000 for example.  I might use the workaround suggested by GrayL.  A Double data type should work though:
Stores numbers from –1.79769313486231E308 to –4.94065645841247E–324 for negative values and from 1.79769313486231E308 to 4.94065645841247E–324 for positive values.

Helen: Thanks, but it's not an issue with Nulls, it's an issue with Negatives.  It's already being imported into a temp table.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

FlysterCommented:
Would it be possible to get a small sample of your CVS file?

Flyster
0
TracyVBA DeveloperAuthor Commented:
Sure, attached is the file, I put in "a" for all the other fields, since they're text anyway.  I left a sample of Amounts, from negatives, zeros and positives.

Thanks
LOC0906.CSV
0
Rey Obrero (Capricorn1)Commented:
try this

place the two files in the same folder
LOC0906.CSV
DB-Q-25645051.mdb
0
TracyVBA DeveloperAuthor Commented:
Cap - thanks, your test files work.  When I try it on my database and csv file though, it still doesn't work.  The only thing I see that you've changed (could be wrong), is that you changed two fields in the Spec to Indexed, duplicates ok.

I changed my database to add the two indexed fields, and it doesn't work with my CSV file.  What's weird is, if I sort my CSV ascending on the Amount column, before importing, then it works.

Any ideas?
0
Andrew_WebsterCommented:
My 5 cents: Cap - you may know.  Most big RDBMS systems allow a numeric type to be defined as signed (i.e. positive and negative) or unsigned (i.e. positive only).  AFAIK Access doesn't explicitly do this.

However, I do know that when you're importing something, Access seems to make decisions based on the first few rows that are being imported, not by doing a comprehensive scan of all the data.

So I'm wondering if behind the scenes, Access has had a look at broomee9's data, decided that it's unsigned, and so blithely ignores/rejects any negative numbers.

Does that make sense?  If so, it's a useful one for the tip collection!
0
FlysterCommented:
Prior to importing, can you open your CSV file and format that column as a number?
0
telyni19Commented:
I'm wondering if there's any interaction between the text format and the commas. The file is a comma-delimited file, with fields containing commas surrounded by quotes to maintain each number as a single field. Perhaps the import is having problems converting the negative numbers to numbers when they are in text form surrounded by quotes. Perhaps you could try removing all the commas from within the numbers as well as the quote marks? So instead of "-1,390.08" you would just have -1390.08. You don't need the commas for numeric importation anyway; once you get the numbers into the database, you can format them however you want.

The reason why sorting the CSV would make a difference would depend on what the first rows end up containing. If the first rows of that column contain a number that is explicitly numeric, like just 0, then the import procedure knows that column is numeric, and will interpret the rest of the column correctly. If the import procedure finds text-formatted numbers, then it will assume the column is text, and might then have trouble interpreting the numbers.
0

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
Andrew_WebsterCommented:
I've got a suggestion: enter some sample data by hand, export the table to CSV then open it in a text editor and take a look.  Compare it to the incoming file.  Does that give any clues?  I like tlyni19's line of thinking, and a test like this would reveal how Access/Jet "thinks".
0
Rey Obrero (Capricorn1)Commented:
this is what i'll do.
* import the csv file in a non existing table (tmpTable)
   all data will be imported as text
* create an append query ( do the type conversion here ) from the tmpTable to the destination table

* delete the tmpTable
0
Rey Obrero (Capricorn1)Commented:
the sampling or testing of records are always done when importing csv or .xls file

see this http://support.microsoft.com/kb/109376

0
TracyVBA DeveloperAuthor Commented:
Thank you to all for your help and suggestions.

I got it working by setting the Text Qualifier to None, instead of Double Quotes (telyni, your suggestion pointed me into this direction).

Also, Caps article explains it exactly:
"If the first row of the spreadsheet contains a numeric value in a column that allows alphabetical and numeric characters, Microsoft Access defines the field type in the table for that column as numeric. This is also true even if the field in the existing table is defined as text. It will not change the data type of the field, but it will cause errors during the import process."
Microsoft's suggestion is not helpful, they suggest to add a new row at the top with the proper data types of the data you want to have.  I'd rather just do the import into a temp table and convert after the fact like Cap suggests (LadyHagood's suggestion also led me to GrayL's suggestion, which is almost the same as this).

Thank you again, I tried to be fair splitting the points.
0
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.