Scientific Notation for Numerics

Submitted by gregor on Sun, 2013-02-24 16:53 in Price Tapestry

Hello David, I just noticed some problems with one feed that is saving some of the UPCs as scientific notation (8.23142E+11). When I look at the feed file in Excel, all of the UPCs display in scientific notation (which is normal), but when I import the feed most of them are converted to numeric values correctly. But for some reason there are 74 items that retain the scientific notation. Any idea what could be happening? I'm using csv|9|1|34 and I've tried several other options. I wonder if these particular codes have been converted to text somehow and will no longer convert back to numeric? Do you think any type of filter will work?

I run this feed through a pre-processor so I could add some code there if needed. The pre-processor does not affect this field.

Thanks,
Gregor

Hi Gregor, Excel will (I

Submitted by support on Mon, 2013-02-25 07:42

Hi Gregor,

Excel will (I think) display what it thinks are numbers that are too wide for the cell in scientific notation, however I wouldn't have thought this should extend to exporting from Excel (assuming that's what you are doing).

However, you can instruct spreadsheets that a field should not be interpreted as a numeric value by prefixing it with a single quote, e.g.

'012345678910112

This does mean that the ' would need to be present in your feed; but this can be easily stripped by using the Trim filter as described in this thread. After adding the filter to your includes/filter.php and applying to the UPC field for this feed, on the configuration page for the filter include the ' character in the Additional Chars box and that should do the trick...

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Thank you for the reply

Submitted by gregor on Wed, 2013-02-27 05:41

Thank you for the reply David. I am processing the feed directly and don't open it in Excel unless I want to take a look. Perhaps the merchant is using Excel and causing some of the UPCs to be corrupted. I tried a few things and finally got the numbers to convert, but found that they were not valid UPC codes anyway. So I added code to filter out the invalid values. All is well.

Thanks,
Gregor

If you open Excel, import

Submitted by henk on Fri, 2013-03-01 09:24

If you open Excel, import from text, open the file, in the third tab ( option ) you can give one column ( upc code ) the property text.

Sorry i have a dutch version.

Henk