Transposing an Excel date to a MySQL long

Translated submission in the German forum. You can read the original entry here .

Hi,

I want to import data from an Excel workbook into a MySQL database table.

This means an Excel date field with the format "dd.mm.yyyy" must be input to a MySQL long integer with the format "yyyymmdd".

Is there an easy way to do this?

Thanks in advance,
Alfred Schmidt

hi alfred,

you can do that with the help of the format heater and the built-in adapter data type conversion .

1. if your excel field is defined as a date, you do not need to do anything further on the read side, so proceed with step 2. however, if the date field is defined as a string, you have to ensure the adapter format settings comply with the actual date format in the excel file. normally you need no special settings here either, because flowheater uses the standard locale settings of the computer when creating a new definition. for a german setup this is already set to dd.mm.yyyy.
2. now you have to first convert the date into a string. to do this you use the format heater with settings as in the screenshot. the format heater now converts the date into a string e.g. 20101005.
3. you simply connect the output string so formatted to the mysql long field . at this point the data type conversion of flowheater cuts in and the value is automatically converted from a string to a number.

to make it easy to visualize we have written a brief example for you, see the attachment.