Import From Excel Stripping Specific Data in a Price Field, But Not All

[sn0wghost]

I looked through the threads in this topic, but didn't see anything like this.

I have an Access file in which I store a price list and other associated pricing data. I update this file, as needed, buy importing the data (in entirety) to the target table.

My Excel file has pricing to four decimals ($0.0000) accuracy (table spec: Currency field, 4 decimals). When I load my pricing data, all the fields in the table hold their integrity, except certain values for price; specifically: $0.0001, $0.0008, $0.0016, $0.0032, $0.0064, and $0.0128. These all convert to "$-00" when imported. Definite pattern here. All other values above $0.0128 appear to load properly.

I also have another Access file that uses this Excel data, but that file uses "links," rather than the native table, and it acts the same way; so, it doesn't appear to be my specific Access application file.

First, I tried "painting format" in my Excel file from pricing that did properly load to those that did not. When I uploaded, no change. I tried applying the format of the cells that didn't load to ones that did to see if it would prevent those cells from loading; it corrupted those records. This made me suspect a format problem in Excel.

Just to be sure, the next thing I tried was saving my Excel file to .CSV to strip out any Range Names, formatting, or other background attributes. Opened and saved the .CSV file back to Excel Workbook and used this new file for the import to the native table version and linked to the Access file using file links. Same result--zeros for the specific price values.



Out of about 5,000 records, I have only 131 prices that fall into this "bucket" of specific prices that won't load. Once the file was upload to my Access file with native tables, I manually updated the specific records to the correct pricing, and they hold their values just fine.

Is there something in the code of Access itself for imports that interferes with these values specifically? If not, could there be something in the formatting or background (such as name range) in the Excel source file that is causing this? I don't see how such info could make it through the Excel--->CSV--->Excel conversion.

This behavior is driving me crazy. Any insight is appreciated.

JIMK

[ItsMe]

I would not concern yourself too much with formatting.

Have you tried importing this range of cells into a field that is of data type double?

Another option may be to import everything as text into a temp table and then employ an append query to bring the data over to a permanent table with the appropriate data types.

[sn0wghost]

I would not concern yourself too much with formatting.

Have you tried importing this range of cells into a field that is of data type double?

Another option may be to import everything as text into a temp table and then employ an append query to bring the data over to a permanent table with the appropriate data types.

SOLVED: Thanks ItsMe for the reply. You're advice about formatting made a lot of sense to me, especially type conversions. I tried your suggestion (importing as double), but got the same results. It got me thinking, though. If type conversion could be a potential problem, then maybe my source formatting's ability to be type converted was a problem. I had been formatting the input data a "currency, 4 decimals." If I changed it to "general" and tried importing, would Access make the correct type conversion?

I copied out my data to a new workbook and tab, then "pasted special" values to get everything converted to "general" format. I then pasted the results back to my original workbook (straight copy, no special pasting), and then saved it.

When I imported this data (all "general" format), Access properly type converted everything, including my problematic pricing column to "currency, 4 decimals." Then to confirm this was happening, I changed by source worksheet pricing column to "currency, 4 decimals" and imported again. The problem reappeared! Somehow, my Access table with the field formatted for "currency, 4 decimals" was not able to import an Excel column formatted in exactly the same way and maintain format integrity, but was able to take "general" text and convert it to the table specification.

I have no idea what is going on here, but this method solved my problem. Thanks for the insight.

JIMK

[ItsMe]

Glad you got it sorted. The currency field having four decimals was a curiosity for me. Was not sure how practical it was to change the Excel properties.