Exporting a CSV and Opening in Excel is Removing Trailing Zeros


Excel Formatting Error

Exporting a CSV from Order Time and Opening in Excel is Removing Trailing Zeros From Numeric Item names and numbers.

It seems that when exporting the item list to CSV, trailing zeros from numeric item names/numbers are being removed. This does not occur for alphanumeric item names.

Example: Item 20.040 and item 20.04 both arrive in the export file as 20.04 (but ABC.040 and ABC.04 would not be affected).


Solution

The fact is, when exporting from Order Time, the trailing zeroes are not being removed at all. When you open the CSV directly in Excel it is changing the formatting which is messing up the original CSV.

To fix this you need to do a data import into an excel file from the CSV instead of opening it right in excel. If you open it right in Excel it will cut off the 0's right away and then you'll be stuck with an incorrect document.


Below are the steps to do a data import:

  • Open Excel (the program only, not the CSV you exported) on a blank spreadsheet
  • From the DATA menu, depending on your version of Excel, it may be necessary to select Get External Data before seeing the option to select to import FROM TEXT, this will ask you to find the CSV file you saved previously and then it will open the Text Import Wizard screen.
  • mceclip0.png
  • Select DELIMITED as the Type and click NEXT:

 

Select COMMA as the only DELIMITER and make sure you select NONE as text qualifier, then click NEXT:

 

Highlight all the columns in the data preview section and then select TEXT as the data format, and then click FINISH:

 

You will then see a little pop up asking you which cell to put the data into, and on this one you just click OK

 

 

FINALLY, your data is ready, DO NOT modify anything and SAVE AS a CSV file (you can override the original one you had or you can give it a new name, but this is the one you will import into Order Time, just make sure that once it is saved and closed, it is never opened again before the import is done, because opening it as a normal CSV, you can break the format and the data does not import correctly.