IllegalArgumentException: Invalid row number outside allowable range

Symptoms

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.

 

Procedure

To work around the problem, you can use one of the two following procedures.

 

Output several Excel files

  1. 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.

  2. 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.

 

Output several Excel sheets

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.

Version history
Revision #:
7 of 7
Last update:
‎05-02-2017 07:16 PM
Updated by:
 
Labels (1)