CSV Issues

[selinabissessur72]

Hi
I was wondering if someone could help with a very annoying CSV file issue.


I am a remote worker based in South Africa working in a finance team for a UK based company.
The company uses Xero as their accounting software, and to import data into xero I need to create CSV columns with data in multiple defined columns (which vary depending on what type of data I am importing)


However, for some reason whenever I download the CSV template direct from xero, populate the columns with the data and save it, when it comes to importing into Xero, I get an error message saying Xero can’t read any of the data.


I have sent this CSV file to my manager, and when he opens it, the column headers in row 1 are all in the right columns, however the data that I have populated in rows 2 and below are all bunched together in column A, with lots of commas separating the different data points.


Likewise, if my manager creates the CSV himself, and then sends me that file, when I open it the same thing happens again (Column headers in row 1 stay in their columns but all data from row 2 and below gets bunched up together in column A)


I am pretty sure it is a delimiter issue, but I have tried changing my regional settings to no avail, as well as using the text to columns button and using comma as a delimiter then resaving, but that still doesn’t work. I have attached an example of the file my manager receives for reference.
Any advice would be greatly appreciated

 

[vletm]

Test this sample file...
Save it
Open it
Press [ Do It ]
... select Your csv-file

 

[Marc L]

Hi !​

I have attached an example of the file my manager receives for reference.

You should better attach a CSV template exported from Xero​

in order to see what are the data, decimal and end of line separators like the date format as well, or clearly elaborate them at least !​

So once the file format Xero expects is known, if a VBA procedure is needed to directly export data from Excel to a CSV file​

it should be not difficult to help if only you attach a sample workbook, as guessing can't be coding …​

​

When Excel is not set as US aka data separator different than comma and decimal separator different than point​

and the text file to create must be an US format then with a clever worksheet​

the VBA procedure just needs a single codeline to save the worksheet to a text file​

just using Worksheet.SaveAs statement with its Local parameter set to False as clearly explained in VBA help, a must read !​

​

Other ways exist for not such clever worksheet, the reason why a workbook attachment is expected as explained in this forum rules …​

​