CSV upload: date format, semicolons are locale-dependent in Windows

[rolfkleef]

Currently, an upload of a CSV file where the separator is a semicolon (;) rather than a comma (,) generates an error that the header row is not the same as the template.

Related: dates are required to be in Y-m-d format.

The problem is that when a user edits the template file in Excel under Windows, he/she has no apparent control over these two things: when you enter a date, Excel will recognise it as a date and determine its own format. When you export to CSV, you have no options for the separator.

The only way to change these seems to be to adapt the overall internationalisation settings of Windows: that's nearly impossible to discover or do for an average user.

[SJ-bond]

This is what I've found when working with the CSV upload:

1. When you download data from AidStream as a CSV it combines multiple instances that occur in the same activity file ie two sector codes or two countries, into a single cell separated by a semi-colon (;). Users then assume that you can upload new activities in the same way, using a semi-colon (;) to separate multiple instances but we've found we get the same error as Rolf (above).
2. And if you use it to separate country percentages, you get an additional error message saying that percentages need to be a number ie. it doesn't recognise the semi-colon as a separator.

Dates: I looked on Stack Overflow for some ideas on resolving this, and the most effective way to make sure dates stay in the YYYY-MM-DD format when converted from XLS to CSV seems to be to change the date settings on your computer/laptop, as Rolf said.

Would AidStream be able to use something similar to the fix you can apply on CSV Convertor, where it converts the dates to the right format on upload? I don't think we can expect users to mess around trying to change the dates in the CSV file, they are going to get frustrated.

[rolfkleef]

Maybe it makes sense to experiment with csvkit?

Basically: in2csv data.xlsx > data.csv generates a CSV file from an Excel XLSX file, so Excel can take care of locale for the user, dates will always come out as 2016-11-22 00:00:00 in in2csv. And you get rid of separators, encoding, etc (or better: csvkit can deal with that). And users simply can work in "normal Excel", only need to make sure they save in the modern .xlsx format.

(Note: you can also convert the older format .XLS files, but then dates will be exported as numbers ("days since 1900-1-1") (see also this bug at cvskit)

[SJ-bond]

You can also use separate rows for additional information for each project (see AidStream guidance page at https://github.com/younginnovations/aidstream/wiki/How-to-use-the-mass-import-option-for-uploading-activities so maybe this should be made clearer?