You may get the following error when writing records into an Excel file with a tFileOutputExcel component:
Exception in component tFileOutputExcel_1 java.lang.IllegalArgumentException: Invalid row number (1048854) outside allowable range (0..1048575)
This is because you are trying to insert more than the allowable number of rows into an Excel file. For more details, please see this Wikipedia article. This is a limitation of Microsoft Excel.
To work around the problem, you can use one of the two following procedures.
Export the data into several CSV files using the Split output in several files option on the tFileOutputDelimited component. Each output CSV file can contain up to 1048576 rows.
Iterate over each CSV file and write the data to several Excel files. An example Job is available in the attachments list.
Note: The example Job exports from Talend Open Studio for Data Integration 5.1.1 r84309. To import the Job into Talend Studio, use version 5.1.1 or higher.
Another solution is to output the data into several sheets in the same Excel file. Each sheet can contain up to 1048756 rows. An example Job is available in the attachments list.
This job will consume a lot of memory, and you may get the following exception while reading a large data set.
java.lang.OutOfMemoryError: Java heap space
To solve this problem by allocating more memory to run the ETL Job, please refer to this page.