Checking your data file prior to importing it
The information provided in this article is valid for all types of imported data. For the purposes of this article, the examples are based on importing contact data into your site's database.
First, save your exported data as either a CSV or TAB delimited file. CSV files are somewhat easier to check for errors but you can use whichever format you prefer.
CSV format has an extension .csv and the TAB delimited has .txt. If your exported data file uses a different file extension, return to that system and re-save the file.
Original file: This is an example of how the original file will look in Excel
CSV File Output: This is how the CSV file data actually looks
TAB Delimited File Output: This is how the Tab Delimited file looks
The best way to check the file for errors is to open it in the text editor such as Notepad (we recommend you to download a free program called Notepad++). Examine the file to ensure nothing out of the ordinary stands out. You should see the file as shown in the image examples above.
Check the CSV file for commas
It is common for Excel to save the empty cells on the end of the Excel fiel as a line of commas, which will cause the import to fail, so you need to check for this. Here is what the file will actually look like:
CSV File Output - The comams at the end of the file will make the import fail
Check for line breaks
Your columns must not include any line breaks otherwise your import will fail. When you create your CSV or TAB delimited file each customer record will appear on one line (as shown above). If for example you were to use the Detailed import and the address column included a line break the output would appear as:
CSV File Output - first line has a line break (the import will fail in this case when it gets to line 3)
The Line Break directly after 233 has pushed the remainder of the columns to a new line. The import feature will treat the new line as a new customer which is incorrect. Its likely that your import will not fail but the data imported will be incorrect.
Making sure the content is not in one cell
It sometimes happens that the Excel file seems in order but when you click onto a first top left cell you will notice that the whole content of the file is actually residing in that one cell.
You can tell when you have this problem by looking at the formula bar. You will notice that the whole file is in that bar.
Selecting the correct options on the import screen
Here is what the typical import screen looks like. These options willl be present on all types of imports. Please see the explanations of each field below the image.
Advanced contact import screen
- This is where you select the file to be imported. Please rember that you can not import .xls file. You need to same it into either .csv format or tab delimited format.
- Choose the correct file type. If you happen to select the wrong file type the import will fail.
- You will normally have the names of the columns in the first line, such as Email,Title,First Name,Last Name,Address,City, but you can also import a file without these. If your columns have names you must select this option.
- This is the description of the columns and their order. If your file's data is not arranged in this order, your import will fail.