Microsoft Excel is messing up my number fields.

Microsoft Excel is both your friend and enemy. With great power comes great responsibility! Excel is known to cause many issues with data feeds by reformatting numerical fields when you use it to open your feed file. Examples include:

1) Removing leading zeros from UPCs or SKUs
2) Reformatting SKUs or other numbers as dates (ie: 112009 becomes Nov-2009) or into scientific notation (3.24E+11)

These issues most frequently occur when opening .CSV files in Excel. For example, if you export your product catalog from your e-commerce platform in .CSV format and then need to edit it before importing it to SingleFeed, you may need to open the file with Excel. This is usually where the problem occurs. When opening a .CSV file, Excel tries to determine the format of the data being loaded (instead of treating it all like plain text). For number fields like UPC and SKU, Excel may remove leading zeros or change other important formatting. Excel makes these changes without notifying you and you may not even notice where the changes have been made. When you re-save the file the formatting errors are committed.

For the above reason it may be safer to stick with tab-delimited .TXT files if you are able to export your product catalog in that format.  However, dealing with .CSV files may sometimes be unavoidable (the singlefeed file you download from your account is in .CSV format) and so here is a workaround should you need to open those files in Excel:

1.) Never open a .CSV in Excel by double-click.
2.) Open Excel first and then goto Data > Import External Data > Import Data and select your file from the browse interface.
3.) An Import Wizard will start. Select "comma" as your delimiter and specify TEXT rather than General format and this will preserve your data.

More information is available here:
http://support.microsoft.com/kb/214233
http://excel.tips.net/Pages/T002588_Handling_Leading_Zeros_in_CSV_Files.html

Have more questions? Submit a request

0 Comments

Article is closed for comments.