how to keep data from being formatted incorrectly

9 years 7 months ago #155889 by ThePiston
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?

CB 2.3, CBsubs 4.3, PHP 7.1, J! 3.9.X

Please Log in to join the conversation.

9 years 7 months ago #155894 by wolverton
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

Please Log in to join the conversation.

9 years 7 months ago #155897 by jciconsult
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.

Please Log in to join the conversation.

9 years 7 months ago #155905 by ThePiston
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?

CB 2.3, CBsubs 4.3, PHP 7.1, J! 3.9.X

Please Log in to join the conversation.

9 years 7 months ago #155908 by ThePiston
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.

CB 2.3, CBsubs 4.3, PHP 7.1, J! 3.9.X

Please Log in to join the conversation.

9 years 7 months ago #155909 by jciconsult
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.

Please Log in to join the conversation.

Moderators: beatnantjciconsultwolvertonkrileon
Time to create page: 0.426 seconds

Facebook Twitter LinkedIn