[Solved] Calc and ODBC

[rspeth]

I am using Open Office Calc to load and Excel file into a MySQL database via ODBC.
I have Open Office 2.3.1
MySQL 5.0.41
MySQL ODBC connector 3.51.22
I have the same problem with a version 4 MySQL database.
I don't know if this is a calc issue or a MySQL issue but I thought I would start here.
I am opening a file that is in excel format.
I have attached a file with a test case.
I open the file then open my data sources.
I then select all of the data including the first line with the column names.
I then drag that up to the data source I want and tell Open Office Calc to creat me a new table in the datasource.
I modify the table name.
I select definitions and data.
I leave append blank.
I select Create primany key and leave the name as ID
I select next
I select all of the existing columns uning the >> button.
I select next
I change the field type on ActivationDT, ClosedDt, Bill_Creation_Dt, Stop_Billing and CurrentDT to Date
These are the only changes I make to the data.
I then select create.
Everyting imports into the database.
However the data seem to get changed slightly.
The most obvious example is the data called CurrentDt.
In the test data it is 12-19-2007.
When it gets to the database it is 12-21-2007
I have not done a lot of testing but it appears that date close to the beginning of the year (any year) are right and that they get changed toward the end of the year.
Any Ideas.
Thanks
RON

[DrewJensen]

The problem comes in when the date is transfered - this is due to a difference between what the two applications understand to be the first possible date. The epoch it is called.

The way to handle this is as follows:

File>New>Database
Select Connecting to Existing database
Select in the drop down box 'Spreadsheet'
Next
Browse to your excel file and select it
Finish

Now open this Base file and the sheets will be displayed as tables - the dates will be correct.
Drag this table ( the sheet ) to the Base file that connects to the MySQL database - the dates will not be mishandled in this approach. There was never a reason to bring Calc into this mix actually.

Drew

[rspeth]

Drew,

Hey thanks.
I did not know how to do that before but it worked great.

Thanks again,
RON