topic Re: tfileinputexcel empty row in Talend Studio

tfileinputexcel empty row

Anonymous — Sat, 16 Nov 2024 13:37:56 GMT

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.

Re: tfileinputexcel empty row

Anonymous — Thu, 24 Dec 2009 09:56:40 GMT

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

Re: tfileinputexcel empty row

Anonymous — Thu, 24 Dec 2009 10:06:24 GMT

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.

Re: tfileinputexcel empty row

Anonymous — Thu, 24 Dec 2009 10:10:14 GMT

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

Re: tfileinputexcel empty row

Anonymous — Thu, 24 Dec 2009 10:16:11 GMT

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.

Re: tfileinputexcel empty row

Anonymous — Fri, 25 Dec 2009 02:17:37 GMT

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

Re: tfileinputexcel empty row

Anonymous — Thu, 18 Feb 2010 20:42:13 GMT

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?

Re: tfileinputexcel empty row

Anonymous — Sat, 20 Feb 2010 14:56:54 GMT

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.

Re: tfileinputexcel empty row

Anonymous — Sat, 20 Feb 2010 14:59:26 GMT

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

Re: tfileinputexcel empty row

Anonymous — Sun, 21 Feb 2010 01:46:56 GMT

To emaxt6, i am using snagit 8 to capture screenshots.
Best regards
shong

Re: tfileinputexcel empty row

Anonymous — Sun, 21 Feb 2010 14:17:39 GMT

ok thanks shong seems a nice tool.

Re: tfileinputexcel empty row

Anonymous — Tue, 06 Apr 2010 01:07:30 GMT

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.

Re: tfileinputexcel empty row

Anonymous — Tue, 06 Apr 2010 04:37:53 GMT

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

Re: tfileinputexcel empty row

Anonymous — Tue, 06 Apr 2010 06:55:23 GMT

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

Re: tfileinputexcel empty row

Anonymous — Wed, 07 Apr 2010 18:41:07 GMT

Solution for the above Issue: (Anders Geving From Linked in pointed me to this link)
https://community.talend.com/t5/Design-and-Development/How-can-add-extra-jdbc-settings-to-a-MySQL-connection/td-p/65450
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.