Manipulating CSV Files in Microsoft Excel

Every time you download any information from your store (inventory, items from Catalog Manager, cross-sell information, etc.) you receive a comma-delimited text file (CSV). These files are readable by most programs that can manipulate data, and in particular, you can simply open a CSV file using Microsoft Excel, make changes to it and save it just like you would with a regular spreadsheet. A little known fact is that when Excel opens up a CSV file, it removes any trailing zeros from cells containing only numbers. This can be a problem in your Code field, for example, if some of your product codes are numeric and they contain leading zeros. To prevent excel from removing the zeros, follow these steps:

  1. Rename your csv file from filename.csv to filename.txt (where filename is the actual name of the file.)
  2. Open up Microsoft Excel.
  3. Select File > Open.
  4. Under "Files of Type", select "Text files".
  5. Browse to your file and open it. Excel will present you with a file conversion dialog box.
  6. On the first screen, select "Delimited" (the default) and click "Next"
  7. On the following screen, select "Comma" as the delimiter, and click "Next"
  8. On the following screen, highlight the column containing your product codes, and select "Text" as the Column Data Format. Click "Finish" when done.
  9. Now Excel opens the file and all leading zeros are preserved in the Code column.

You can now change your file, and when you are ready to save it, select "File > Save As". On the next screen, under "Save as type", select CSV, then proceed with saving your file.