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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Helen Feddema
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.
ASKER
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.
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.
Would it be possible to get a small sample of your CVS file?
Flyster
Flyster
ASKER
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
Thanks
LOC0906.CSV
ASKER
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?
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?
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!
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!
Prior to importing, can you open your CSV file and format that column as a number?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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".
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
* 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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.