Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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...