Stop at empty row in Excel

[Anonymous]

Hi,
I am running a job importing rows from an excel spreadsheet into a DB (PostgreSQL) table.
This used to work fine ... but now, I am using another spreadsheet, and the process does not stop at the last row.
It carries on, read the next row and the SQL INSERT fails because it's trying to insert NULL values in NON NULL columns.
What should I do to make the tMap to stop when finding an empty row ?
(and why did it work before and not any more ? Surely, that's something with the Excel spreadsheet !)

cheers,
L@u

[Anonymous]

add a filter

[Anonymous]

OK,
but why does it not stop ?
What's the condition making the process of reading lines to stop ? Because still, it does not read all the rows in the spreadsheet !

[Anonymous]

that's a good question, what version of TOS do you use?
java or perl?

[Anonymous]

2.4.3 (r19069) - Java
I have to look into the doc for filters: I did not manage to make it work.
Also, as in the data schema (of the Excel sheet) the first column is marked as Key (and not check Nullable), how come the row with a NULL value is processed ?

[Anonymous]

in your tMap you can check nullable and after check filter and put YourIDColumn!=null

[Anonymous]

OK, I did it and that works !
Still, I don't understand why I have to declare NULLABLE a column which is not null, to filter out null values afterwards ! !
I would have thought that was all the purpose of declaring a column NOT NULLABLE ... but obviously I don't know TOS well enough.
Still strange also that in some cases, the reading process carries on with empty lines !

[Anonymous]

When you check nullable, the int become an object (Integer) so it can be null

[Anonymous]

Hi everyone, even if the thread is rather old, I'm posting here for the sake of completeness and in the hope this will help somebody.
I've experienced myself this problem in very similar conditions just a few days ago. It seems to be related to the "new" (by then!) .xlsx formats. Something has changed so that reading files in the "old" xls format works fine.
You should check if in those apparently empty rows there is some kind of formatting that avoids excel consider them really empty (borders, fonts, etc). Sometimes an user might select the entire sheet and apply Calibra or Arial font family.
In my case, I noticed that the job was loading more 11.500 while in fact the file contained less than 300 rows of data.
I decided to make an attempt by deleting some blank rows and saw that the number of total processed rows decreased. So I went on by deleting 11000 rows that looked empty but in fact were somehow different, and everything worked fine.
Another way to solve this could be re-save the file in the previous .xls format, as this issue seems to refer to newer formats only. Ah, you should delete entire rows, not cells.