I downloaded the CSV file, but when I reupload it all of the dates are messed up. OpenOffice changes all of the dates to whatever it thinks is appropriate. What settings can I use to stop this so that whatever is downloaded in the CSV stays in the same format?
Hi Piston,
I think that you just set the OO date default to match the server settings.
Ctrl+F11 -> Select Default -> Click on Modify -> Select Numbers Tab -> Select Data - Select Date Format
Essentially, the point is that the date is stored as a number that gets interpreted as a date string when the CSV is produced. This is re-intepreted as a number by the spreadsheet program. The trick is to set the spreadhseet program (Libre office or Excel) to format the dates appropriately for producing the csv for the return trip. On our servers, the best setting is year/month/day. that works on most linux.mysql environments. Look at the csv when you save it and do the same on the back trip.
I did that but in the formula box the number still reads "1974/04/09" even though the cell reads "1974-04-09". Dates in CB need to be YYYY-MM-DD. Will it import with what is in the cell or what is in the forumla box? It's as if the number gets formatted upon opening the file so when you format it again, it's still showing as whatever it was opened as. What other data could it be messing up?
I think the best way is to highlight all of the columns when you import into OpenOffice and choose "Text" from the select box - then they will not have any formatting - what you see is what you get.
You want to treat variables almost always as text. For dates, the issue is if you are generating dates from some other system, you must cause the date variable (which is a big integer in the internals of the spreadsheet) to be formatted for text output in the way that your MYSQL server requires it.