Menu
I have a .xls I need to convert to .csv
The file contains some date columns. The format on the date is "*14/03/2001" which, according to Excel means the date responds to regional date and time settings specified for the OS.
Opening in Excel you see:
20/01/2013
01/05/2013
Save as... CSV
Open in notepad:
01/20/2013
05/01/2013
I have temporarily fixed by setting date formats to "14/03/2001" (no *) but even some other custom formats with no *, like "d/mm/yyyy h:mm" get mangled when saved to CSV.
900
Instead, this is due to the way Excel and other spreadsheet programs open a CSV file and display the data therein. Basically, spreadsheet programs are designed to be used for calculation purposes, so they tend to apply mathematical formats to numbers when the CSV file is opened directly into the program.
You can save your desired date format from Excel to .csv by following this procedure, hopefully an excel guru can refine further and reduce the number of steps: Create a new column DATE_TMP and set it equal to the =TEXT( oldcolumn, "date-format-arg" ) formula.
Very often people face issues when they are opening csv file and that have dates. When date is exported or saved most of the program or applications uses Month-Day-Year. If your locale or Region & Language (Date format : position of day and month) is not the same, then excel do not understand all the dates and create inconsistencies.
On your windows Search type in Region and Language. You will see it under control Panel. Open it and change the date format to match the source file. open the csv file and verify that the data has been correctly interpreted by Excel. You may save the data as excel (use Save as ).
The xls contains dates formatted as dd/mm/yyyy. However, when the 'save as' code is executed the format is lost. The csv file contains mm/dd/yyyy.
Although keeping this in mind http://xkcd.com/1179/
In the end I decided to use the format YYYYMMDD in all CSV files, which doesn't convert to date in Excel, but can be read by all our applications correctly.
159
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
