Tips on using CSV files
Using CSV files
The CSV ("Comma Separated Value") file format is often used to exchange data between applications. CSV files are plain text files which can contain numbers, letters, and symbols, and structure the data contained within them in a table form.
Here is a list of common issues that can happen during CSV file import:
Duplicated SKUs
Each SKU needs to be unique, and Order Dispatch will not allow using the same SKU numbers for item creation. Before importing, please make sure that there are no duplicates in the SKU column or the import will return an error.
They can easily be found using the conditional formatting in Excel, just highlight the column, click on 'Conditional Formatting' on the Home menu, then select 'Duplicate Values' and Excel will highlight all the duplicates for you (if any). Other spreadsheet programs have similar functions.
Unusual characters
Anything that is not an alphanumeric character in the SKU or another field may cause the CSV file to be rejected, or as a result, you can get this, the unusual characters have been replaced with question marks instead.
Empty column name / duplicate column name
The column name is a mandatory field. If you do not fill out this field, it will not be possible to perform mapping and import the data. Each column name must be unique, or importing it will not be possible.
Importing other file formats instead of a .csv file
When saving the file, ensure you save it as a .csv file type, UTF-8 format; otherwise, you will not be able to import it to Order Dispatch.
Commas and Quotes
Wherever possible, it is best to avoid double quotes and commas in CSV files. A CSV file is a 'comma separated values' text file, so programs that read CSV files will be looking for the commas in between each value to be able to separate them, commas in your values will cause problems unless correctly handled. Fields that contain commas should be enclosed in double quotes. The same goes for text with multiple lines, which may be part of your field data. Any fields containing a new line as part of its data must be enclosed in double quotes. If your fields contain double quotes as part of their data, the internal quotation marks need to be doubled (escape character) so they can be interpreted correctly.
Example:
Result:
If there is an additional coma in the value of the column, then this will not work:
Result:
To retain the commas in "Title" column, we can enclose those fields in quotation marks. For instance:
As a result:
If the field contains double quotes as part of the data, the internal quotation marks need to be doubled so they can be interpreted correctly. For instance, given the following data:
In this case, simply enclosing the text in quotation marks will not work:
We can represent it in a CSV file as follows:
External double-quotes (") to escape internal comas and an additional double-quote (") before the actual double-quote in the text to get this:
Excel truncating long numbers
Microsoft Excel (and other spreadsheet programs) can automatically truncate long numbers (phone numbers, tracking numbers etc). For example, it can turn 8 and 9-digit numbers into numeric references like 9.876+E. This will almost always be an issue with barcodes and will cause problems with your items if not done correctly. If this is happening with your file, then you can:
1. Try the workarounds explained here.
2. Use OpenOffice
Export - Leading zeros
When exporting a file with data, large numbers can sometimes lose leading zeros. For example, phone numbers, product codes, account numbers, barcodes, postal codes, etc. The easiest way to prevent this is to add an apostrophe to the start of the value, this will force Excel to treat the number as text, and will not affect further data handling. Eg, instead of entering 0505234252342 as a barcode in Excel and finding it is replaced with 5.05234E+11, you would enter '0505234252342 and this would remain unchanged.