One Star

tfileinputexcel empty row

Hello.
I want to do something which seems quite simple to me.
I have a tfileinputexcel component and a tadvancedXMLoutput.
In my excel file, some rows can sometimes be empty if the data isn't available.
I want that in this case, the XML bounds doesn't appear in the final XML file.
For example:
With a Excel like this
    ID        B    C     D
classic 111 222 333
c_empty 111 333

I want to get this :
<A id="classic">
<b>111</b>
<c>222</c>
<d>333</d>
</A>
<A id="c_empty">
<b>111</b>
<d>333</d>
</A>

The schema of the xlsx has the 'nullable' check, but i get a "IllegalStateException: Bad cell type" when it tries to convert the empty cell into 'string/numeric/...'
What is missing ?
Thanks.
14 REPLIES
Community Manager

Re: tfileinputexcel empty row

Hello
The schema of the xlsx has the 'nullable' check, but i get a "IllegalStateException: Bad cell type" when it tries to convert the empty cell into 'string/numeric/...'

Set the data type as string, see my screeshots.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tfileinputexcel empty row

I thought this should work, but i already have string for all my fields.
It seems that the empty cells in Excel are considered not as 'null' but as "".
So i got the same schema as you but not still the exception...

The URGENCY field is empty in Excel.


Community Manager

Re: tfileinputexcel empty row

Hello
Have you still got the same error even you have string for all my fields? Please show us the whole error message.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tfileinputexcel empty row

Démarrage du jobOP_SK a 11:03 24/12/2009.
connecting to socket on port 4178
connected
log4j:WARN No appenders could be found for logger (org.openxml4j.opc).
log4j:WARN Please initialize the log4j system properly.
Exception in component tFileInputExcel_3
java.lang.IllegalStateException: Bad cell type (3)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluate(XSSFFormulaEvaluator.java:102)
at sk.op_sk_0_1.OP_SK.tFileInputExcel_3Process(OP_SK.java:3147)
at sk.op_sk_0_1.OP_SK.runJobInTOS(OP_SK.java:5926)
at sk.op_sk_0_1.OP_SK.main(OP_SK.java:5822)
disconnected
Job OP_SK terminé à 11:03 24/12/2009.

With debugger it stops here:
	for (int i = 0; i < 70; i++) {
if (i + start_column_tFileInputExcel_3 < actual_end_column_tFileInputExcel_3) {
org.apache.poi.ss.usermodel.Cell cell_tFileInputExcel_3 = row_tFileInputExcel_3
.getCell(i + start_column_tFileInputExcel_3);
if (cell_tFileInputExcel_3 != null) {
switch (evaluator_tFileInputExcel_3.evaluate(
cell_tFileInputExcel_3).getCellType()) {
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
temp_row_tFileInputExcel_3 = cell_tFileInputExcel_3
.getRichStringCellValue()
.getString();
break;
case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
if (org.apache.poi.ss.usermodel.DateUtil
.isCellDateFormatted(cell_tFileInputExcel_3)) {
temp_row_tFileInputExcel_3 = cell_tFileInputExcel_3
.getDateCellValue().toString();
} else {
...

I updated the previous post with screenshots.
Community Manager

Re: tfileinputexcel empty row

Hello guy
I see the first line is the header in you excel file, so do you set the header as 1 on tFileInputExcel componnet?
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: tfileinputexcel empty row

I am getting the same message
Exception in component tFileInputExcel_2
java.lang.IllegalStateException: Bad cell type (3)
field type is set as string, it is nullable and also the header is set to 1.
What could be the error?
Six Stars

Re: tfileinputexcel empty row

Seems an exception risen Apache POI code (maybe due to inconsistent call)
Just as a test, try to remove all custom formatting and all formulas in excel and retry with all as strings.
Six Stars

Re: tfileinputexcel empty row

OT: shong - for curiosity - are you using a specific software to make screenshots and annotation?
Community Manager

Re: tfileinputexcel empty row

To emaxt6, i am using snagit 8 to capture screenshots.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars

Re: tfileinputexcel empty row

ok thanks shong seems a nice tool.
One Star

Re: tfileinputexcel empty row

We are facing an Issue while extracting data from Excel 2007 File.
connecting to socket on port 3420
connected
Exception in component tFileInputExcel_2
java.lang.IllegalStateException: Bad cell type (3)
Did you come across this error.
Talend Version: 3.2.3 Integration Suite
Source: Excel 2007 (.xlsx)
Target: Mysql
I have 9 columns with blank cells here and there. At Talend level: I designated all columns as Nullable and Strings.
Community Manager

Re: tfileinputexcel empty row

Hello vamsi27
Can you try to check/uncheck the 'Don't validate the cells' box on advanced settings panel and test again?.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tfileinputexcel empty row

Hello Shong,
We are using .xlsx file as source.
I see the option you suggested for xls file but not xlsx file.
One Star

Re: tfileinputexcel empty row

Solution for the above Issue: (Anders Geving From Linked in pointed me to this link)
http://www.talendforge.org/bugs/view.php?id=11286
Download the latest patch. This fixed my issue.
I added the latest Java Jet file to the components folder in talend and it worked for me.