Export Time from MySQL DB to Excel Sheet

One Star

Export Time from MySQL DB to Excel Sheet

Hello everyone,
I am having some difficulties with a very simple job in talend. My job consists of a MySQL DB Input linked directly to an Excel Output.
I am having problems with a time column (of the date format HH:mm:ss). The column is exported to Excel, however for some reason it always adds a date to the data. So every row entry for the time column in excel would read: 1/1/1970 HH:mm:ss.
This creates problems when I try to make charts in Excel. The date has no meaning and is in no way attached to my data. I don't know where this date comes from. Does anyone no how I can stop this from happening?
Regards,
Matt
One Star

Re: Export Time from MySQL DB to Excel Sheet

On the schema definition for the database, there is a column called Date Pattern.
Make sure that it is set to just display the date or time ONLY.
My example photo shows just the date - but you can set it to be just the time as well.
The problem may also come in on the Excel side. You may want to format the Excel column to just display the time.
One Star

Re: Export Time from MySQL DB to Excel Sheet

I have the Date pattern set to only "HH:mm:ss". There is no date stored in the database in this column.
I have tried formatting the excel column to only display time, however this only affects the display. The erroneous date is still stored in the cell even though it is not displayed. This causes problems for me when i try to manipulate and plot the data.
Thank you for your suggestions! These were the first things I tried before I came to look for help here.
Are there any other ideas out there?
One Star

Re: Export Time from MySQL DB to Excel Sheet

Well... that got me to thinking.
I forgot that there is also a schema associated with the tFileOutputExcel component.
I went and changed it so that it matches the schema of the tDB2Input and it worked.
See attached. I did not have to make any changes to the Excel spreadsheet.
The screen shot was taken right after I opened it.
One Star

Re: Export Time from MySQL DB to Excel Sheet

Now I am somewhat confused!
As far as I can see, my MySQL schema matches my Excel Output Schema exactly (See attached). Yet I still have the 1/1/1970 date added to my time column in excel.
I have found that if I instead output to a delimited file, then import the delimited file to excel I achieve my desired result. This will be my solution for the short term. SMaz, can you see anything different about my schema?
Thanks for your time and help!
One Star

Re: Export Time from MySQL DB to Excel Sheet

Your schema looks OK to me...
Why not add a tLogRow at the end and dump the output to the console so you can see if it's being written out as only HH:MMSmiley FrustratedS or if, somehow, Talend is adding the date in. But, I doubt it is Talend. The 1/1/1970 date sounds suspiciously like a Bill Gates problem.
Also, if there are a lot of rows, you may not want to dump them all.
If you press the button to the right of the SQL Query, it'll bring up the SQL Query Builder and there's a check box where you can limit how many rows are retrieved via your query. Or, you could just kill the job from the Run window.
Highlighted
One Star

Re: Export Time from MySQL DB to Excel Sheet

Hey SMaz,
I was always suspicious that this was a Bill Gates problem as you said , but the tlogrow confirmed it 100%. Thank you for the suggestion.
I don't know why Excel adds a random date to my data, but it is definitely just another annoying Microsoft 'quirk'. Until I find a better solution I will continue to output to a delimited file and then import to excel.
Thanks for your help!
One Star

Re: Export Time from MySQL DB to Excel Sheet

try 'format cell' option in excel it self and change the formatting to date only. it will not add a random date to your time value. which as far as I know is the integer part of number-equivalent of time.