Transposition of the excel table

[dshirvanyan]

 Hi, everyone! 

I'm new to JasperETL and I need help.

My task is to load data from excel file in mysql DB. I've created a metadata scheme for my excel file. Then using tMap I try to create a mapping scheme for the DB. But my problem is, that the columns in excel contain not only the useful data, but also headers for other columns. Please, look at my excel file. 

Is there any ability to address directly to cells? For example, I need to put the data from the call B10 into the field project_type of the table projects in my DB. I see that tMap loads from excel file the whole columns and there's no ability to address to the row(s) of the column.

So I need some transposition of my excel file. I need to fetch the data from cells B6-F6 to the field project_number in my DB,  B7-F7 to the field customer_name and so on. And by the way the number of columns varies. And the count of rows Tasks, Project members varies too. so I don't know how to fetch this data within the columns and how to make condition of the count of read columns. I know that there is an ability to stop reading if there's an empty row, but not a column!  So I'll appreciate any help! Thank you.

/uploads/projects/jasperetl/image/excel.JPG

 

Post Edited by dshirvanyan at 07/02/2010 06:57

[pkienner]

Change you excel file !

Everything else will cost you much more resources.

;-)

Philippe

[jprochaz]

I need to do the same thing, except instead of using an input file, I have to transpose an output of a tMap element. Is there a way to create what would essentially be a pivot:

account   letter_series   count

01             1                        12

01             2                        1

01             3                        20

would become:

account     letter_1   letter_2   letter_3

01               12            1               20

There is an element in File - Output, tPivotToColumnsDelimited, but it will only work for export into a text file. Is there any way to duplicate this function but pass the results into another element within the same job?