?
Solved

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

Posted on 2010-04-01
14
Medium Priority
?
3,444 Views
Last Modified: 2013-11-29
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
0
Comment
Question by:Tracy
  • 4
  • 3
  • 2
  • +4
14 Comments
 

Assisted Solution

by:LadyHagood
LadyHagood earned 200 total points
ID: 29357171
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 29359443
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
 
LVL 24

Author Comment

by:Tracy
ID: 29360108
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 22

Expert Comment

by:Flyster
ID: 29361854
Would it be possible to get a small sample of your CVS file?

Flyster
0
 
LVL 24

Author Comment

by:Tracy
ID: 29364969
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 29367012
try this

place the two files in the same folder
LOC0906.CSV
DB-Q-25645051.mdb
0
 
LVL 24

Author Comment

by:Tracy
ID: 29368198
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
 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 29369340
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
 
LVL 22

Expert Comment

by:Flyster
ID: 29370202
Prior to importing, can you open your CSV file and format that column as a number?
0
 
LVL 12

Accepted Solution

by:
telyni19 earned 1000 total points
ID: 29370417
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
 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 29370701
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 29370885
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 800 total points
ID: 29370995
the sampling or testing of records are always done when importing csv or .xls file

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

0
 
LVL 24

Author Comment

by:Tracy
ID: 29374326
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

592 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question