topic Transform xls to csv without knowing format in Talend Studio

Transform xls to csv without knowing format

Fernandez — Sat, 16 Nov 2024 03:27:17 GMT

Hi,

A customer will release daily many xls files with different formats (number of columns can be different) and I have to tranform them into csv files.

How can I do that without defining the schema of xls file into tFileInputExcel ?

Thanks

Re: Transform xls to csv without knowing format

Anonymous — Mon, 27 Jan 2020 13:59:28 GMT

You need to give more information I'm afraid. If you are converting them to CSV files, then it sounds like the XML files have at least one loop in them. Could there be more than one loop? What are the constraints that you are having to work within? I don't believe any of the standard Talend components will do this for you I'm afraid. However, you can almost certainly use a bit of Java to potentially achieve this.

Re: Transform xls to csv without knowing format

Fernandez — Mon, 27 Jan 2020 14:10:13 GMT

Hi @rhall

I have to convert XLS files and not XML files.

But I don't know the number of columns of each XLS file I will have to convert every day.

Re: Transform xls to csv without knowing format

Anonymous — Mon, 27 Jan 2020 14:21:15 GMT

Sorry, I'm not firing on all cylinders today. XLS makes it easier. Do you know the maximum number of columns that could be received? If so, set up your input schema to be the maximum number of columns and inside your job whittle those down to only those which are populated with data.

Re: Transform xls to csv without knowing format

Fernandez — Wed, 29 Jan 2020 15:50:18 GMT

Thanks for feedback.

But I don't see how to whittle those down to only those which are populated with data...

With which component between tFileInputExcel and tFileOuputDelimited ?

Re: Transform xls to csv without knowing format

Anonymous — Thu, 30 Jan 2020 10:41:55 GMT

There isn't a component to do this, but you can do it with a bit of Java code. Using something like a tJavaFlex you can test the contents of each of the columns from left to right. The first one that is empty will be where your columns stop. Now if you want, you can prepare your CSV row while doing this and simply output 1 column from this component and send it to your output.