You are not logged in.

Unanswered posts

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

benoitlm
Guest

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:

id_data
date_data
value_data
fk_id_prod

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.

Sincerely,

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

phobucket
Member
146 posts

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
StringHandling.RIGHT(row1.prodfield,
(StringHandling.LEN(row1.prodfield)-
StringHandling.INDEX(row1.prodfield,"."))-1)

once you have the day and year, to get the date_data use
TalendDate.parseDate("MM/dd/yyyy", "01/"+ row1.day + "/" + 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

Thanks,
Ben

Offline

Board footer

Talend Contributor Agreement - Talend Website Privacy Policy