Related Links

Internet User Guide

 

Further Information

Why do CSV files look different when opened in Excel?

This is because CSV is a scienticfic format so when a file is re-opened in Excel some of the content changes.
 
This is easy to correct, see the example.
 
​​

Errors

You may prefer to correct errors on either the .xls file, the .csv file using Excel or to correct the .csv file in Microsoft Notepad.

Correcting errors on a CSV file in Excel is only slightly more complicated than on an ordinary Excel Spreadsheet. This is because a CSV file is saved in a Scientific format (this drops leading zeros and reduces the width in some columns when the file is re-opened in Excel.)

This example CSV file opened in Excel shows the changes in the VAT number, the period and a commodity code. There is also an error is 4th line, and the weight in the 5th column is missing.

 
T 1.23+08 100 TRADER X A 201009 909 CSV02
39029010 1250 EXW 10 122 DE
3055930 1400 EXW 10 456 IT
48053090 556 FOB 10 DE
 

 

Key
Fields changed opening a csv file in Excel
The error on this file is missing data
   

 Following these instructions will correct the file.

  • Widening the 2nd column will return your VAT number.

  • The 1st row 8th column and 3rd row 1st column the leading zero are missing. One way of returning them is by clicking on the cells, then right clicking on the mouse button to generate the Menu screen. Click on Format Cells on the Number tab, then click on Custom. On the right side under 'Type' enter the number of zeros required (e.g. 0000 for period and 00000000 for the commodity code). Click OK and the leading zeros will return.

  • Correct the error 4th row 5th column by entering the weight, resave the file and resubmit.

 

Or you may prefer to correct the errors in a text creator, for example Microsoft Notepad.

Example of the same error file when opened in Notepad

T,98754321,100,TRADER,X,A,201009,0909,CSV02
39029010,1250,EXW,10.122,,DE
03055930,1400,EXW,10,456,,IT
48053090,556,FOB,10,224,,DE 

 

As you can see the Vat number, period and commodity code are unchanged and only the weight need to be entered in the 4th row. Correct the error, then save and resubmit.

Please note: occasionally there are hidden errors which are not shown when a file is opened in Excel. If this happens, opening the file in Notepad will show the errors (e.g. extra lines with commas but no data).