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
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.
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.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