topic converting one .xlsx file into multiple .csv file in Talend Studio

converting one .xlsx file into multiple .csv file

_AnonymousUser — Sat, 16 Nov 2024 11:42:12 GMT

Hi,
I have one .xlsx file consisting multiple sheets in that(eg.sheet1,sheet2,sheet3...),i want to convert data of sheet1 into main1.csv file likewise data of sheet2 into main2.csv file, till sheet10..that is need to generate 10 .csv file from one .xlsx file,so in talend how to do that.

Thanks,

Re: converting one .xlsx file into multiple .csv file

_AnonymousUser — Thu, 27 Mar 2014 12:13:40 GMT

Hi,
Waiting for your reply.

Thanks.

Re: converting one .xlsx file into multiple .csv file

Anonymous — Thu, 27 Mar 2014 14:30:38 GMT

Did you try doing something like this?
yourExcelFile--(Main)-->tMap--(Main)-->tFileInFileInputDelimited(1..n)
What I'm suggesting here is that you create an output in tMap for each one of you sheets and then create n FileInputDelimeted where you get every output in a seperate .csv file.

Re: converting one .xlsx file into multiple .csv file

willm1 — Fri, 28 Mar 2014 16:36:51 GMT

Here's a recipe for reading a directory full of Excel files, looping through each file, then through each sheet in every file (automatically - without caring what the sheet names are), then getting the contents and writing them out to seperate .csv files. In looking at the attached screenshots, pay attention to the File Name text fields - except for the tFileList component, all the others use Talend variables...
I'm fond of the tHash components because they're faster and save me the hassle of writing and reading from disk. If you don't like them, you can replace them with delimited files.
My example uses 1 Excel file that has 2 sheets, each with 4 rows of records (hence the count of 8 records on the screenshot).
What the job does: it loops through all the files and sheets, and appends the records including two fields (File Name and Sheet Name) to the buffer at the end of the first subjob. In the 2nd subjob, we filter the columns and rows and only get the distinct Files and Sheet names. At the end of the 2nd subjob, I have 2 records of the two sheet names.
On the 3rd subjob, we read in the 2 rows of sheet names, then we look up all the 8 records in our tHash from Subjob 1. Because we're iterating through each sheet name, we can then write at the end of the mapping (where we filter based on the sheet name) to respective .csv files...

Re: converting one .xlsx file into multiple .csv file

Anonymous — Sat, 29 Mar 2014 09:46:21 GMT

Hi sushma12345:
One word to your request. This forum depends one the free work of the members and you cannot expect an answer immediately. Sometimes we do other things like doing our job.
You work for a large company Steria Mummert I guess. I am pretty sure they have explained to you how to behave if you need help.
willm has provided a very useful answer. At minimum I would expect you get him an answer how it works for you.

Re: converting one .xlsx file into multiple .csv file

Anonymous — Mon, 31 Mar 2014 08:55:36 GMT

You might also use the custom components tFileExcelWorkbookOpen and tFileExcelSheetList. Both components has useful parameter like SHEET_NAME. You could iterate through the sheets to create separate files.