Google Spreadsheet importing CSV with arbitrary date formatting?
I'm trying to figure out what are the options for dealing with CSV file which has inconsistent locale dates formatting? The spreadsheet set to recognize MMM-dd-yyyy as a valid date (via spreadsheet settings) and CSV file has dd-MMM-yyyy. a) Can I write a custom CSV loader which will process/fix the data before putting it into a spreadsheet? b) Can I access (via API) a raw cell text (avoiding automatically converted 'wrong' date string) and fix it as necessary? c) any other solution? Thanks in advance!
If you write your own script to produce CSV, make your dates in this format and then Google Sheets will automatically recognize them and you can format them however you want:
YYYY-MM-DD hh:mm:ss
Here's an example date (January 22, 2020 at 1:34:22pm):
2020-01-22 13:34:22
I don't think there's a nice solution to this problem. But I can imagine some workarounds that might work for you.
You could upload the file without converting to your Google Docs/Drive and then have a Google Apps Script read it and load into your spreadsheet.
Or you could just insert an apostrophe before each date using a regular text editor on your computer, doing a search and replace. The apostrophe prevents the content of a cell of being parsed by the spreadsheet and is left as is (i.e. a text).