You are not logged in.

Unanswered posts

Important! This site has been replaced. All content here is read-only. Please visit our brand-new community at We look forward to hearing from you there!

#1 2012-04-20 11:31:16


benoitlm said:

inject xls multi-dimension on MySQL

Hi all,

I'm new to Talend and is already looking to develop treatments that, in my opinion, seem to be complicated.

I have a multi-dimensional XLS file that I want to inject into a MySQL database. Here is my file structure

for the 1st sheet in the workbook that represents the month of January:

COL1 | COL2 | COL3 | COL4 | COL5 | COL6 | COL7 | COL8 | COLx ...
  day    prod.1            prod.1             prod.2             prod2
            2007              2008                2007               2008
    1       595                620                 340                 456
    2       459                569                 456                 567
    3       etc.

on the other side, I have a database table with the following strucure:


I wish I could do the following for each line of the file that corresponds to a day of the month:
- Can rework my field "date_data" from the day and year (I already know the month)
- Find the id of the product from its name (eg "prod.1"),
- Finally, add the data for each day of each year, for each product.

Do you have examples of specific treatment to get me?

Thank you in advance for any help you can give me.


#2 2012-04-20 16:01:50

146 posts

phobucket said:

Re: inject xls multi-dimension on MySQL

I don't have the whole solution for you,  but you may want to look into tUnpivotRow from the TalendExchange.

In either rate, the solution below should get you closer to getting the date and the product.

to get the prod use

once you have the day and year, to get the date_data use
TalendDate.parseDate("MM/dd/yyyy", "01/"+ + "/" + row1.year)

You could also look into saving the excel file as delimited file, and inputting the file using tFileInputMSPositional with 3 record types
record 1 pulls in the day and prod line
record 2 pulls in the year line
record 3 pulls in the rest of the records



Board footer

Talend Contributor Agreement - Talend Website Privacy Policy