Toad World® Forums

Data Import Expression Builder - Sequential Numbers

I’m loading data from a spreadsheet into a table. One of the columns in the table requires the row number out of the spreadsheet. As a work around I’ve been creating a series in the xls but I need to stage data daily and that is getting old. Is there a way in the Expression Builder to call a function or create a formula that will insert a sequential number starting with 1? I’ve found little to no documentation on Expression Builder and nothing on its functions or expression language. Any help would be greatly appreciated! -d

1 Like

To do that with the expression builder, under Variables List, there’s one you can use - TotalRowsAdded - it starts at zero, so if you want it to start with 1, use [TotalRowsAdded +1] for your expression.

Another way to do it is put a SEQUENCE.NEXTVAL on the default value for that column on your table, and then don’t map that column in the data import wizard.

Attempted the SEQUENCE.NEXTVAL in the expression builder. It shows up as [SCHEMA.SEQUENCE.NEXTVAL] in the Mappings.

When it runs there is an error of SEQUENCE.NEXTVAL is not a valid floating point value.

The command SELECT SEQUENCE.NEXTVAL FROM DUAL does return a valid result.

Is there a posted example anywhere on how to use the expression builder with sequences?

Thanks!

What I meant in my post above was to actually set the default value on the column in the database.
Then leave that column unmapped during import.

If you want to use expression builder instead, see my post above about TotalRowsAdded.