Read excel by column

Highlighted
One Star

Read excel by column

Hi all!
i have to process an excel with multiple sheet and indefinite number of rows/columns (so, with a dynamic schema)
my informations are written by column

|A |B |C |...|n
1 |a1|b1|c1|...|n1
2 |a2|b2|c2|...|n2
3 |a3|b3|c3|...|n3
4 |a4|b4|c4|...|n4
... |...|... |...|...|...
n |an|bn|cn|...|nn

i wish to have the output flow a1 a2 a3 a4 an | b1 b2 b3 b4 bn| c1 c2 c3 c4 cn.......
is there a simple way to READ an EXCEL BY COLUMN? :rolleyes:

i just tryed to use Tturnrow (but is incompatiblewith TOS5.2) tnormalize and tPivotToColumnDelimited (but needs static schema)
furthermore, for my big files would be better directly read it by cloumn instead of processing the whole file for turn column to row..

is there some component able to do this job? :rolleyes:
Thank you !
One Star

Re: Read excel by column

Hi

I come up with one workaround...
I want to get A column row by row and add each row into a string variable.
But as you say, you don't want to turn column to row for performance...
Does anyone have perfect solution?

Regards,
Pedro
One Star

Re: Read excel by column

Hi,

No perfect solution (yet), maybe the Dynamic schema functions in the commercial version of DI are able to do the job here?

I'm currently working on another solution that can read an excel file with an unknown number of rows/columns but this component reads values cell by cell, converting them into key/value pairs, I don't think this meets your requirements.

Regards,
Arno
One Star

Re: Read excel by column

avdbrink wrote:
Hi,

No perfect solution (yet), maybe the Dynamic schema functions in the commercial version of DI are able to do the job here?

I'm currently working on another solution that can read an excel file with an unknown number of rows/columns but this component reads values cell by cell, converting them into key/value pairs, I don't think this meets your requirements.

Regards,
Arno

maybe.. but i'm poor Smiley Wink

did you uploaded that component in talend forge? maybe can help me..
One Star

Re: Read excel by column

Hi,

Sorry for that Smiley Wink

About my custom component: I haven't uploaded it, because it is still work in progress. I hope it will be finished soon, but no guarantee whatsoever Smiley Very Happy

Regards,
Arno
One Star

Re: Read excel by column

I have two excel files which i will be reading it from tFileList. One excel will have id,name,address,phoneno whereas the other will have id,name,phoneno. How do i read both files dynamically and make sure data gets inserted into id,name,address,phoneno ion mysql database. If i define schema as id,name,address,phoneno for excel at set address as null also if that column is missing in one excelsheet phoneno is getting inserted as address. How that can be avoided?

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Have you checked out Talend’s 2019 Summer release yet?

Find out about Talend's 2019 Summer release

Blog

Talend Summer 2019 – What’s New?

Talend continues to revolutionize how businesses leverage speed and manage scale

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog