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.
You need to uncheck 'Use Array DML' at the last page of import to apply [ToatalRowsAdded +1], commented by JohnDorlon.
My environment
Toad 16.2
Windows 11 pro
I guess the option makes toad import data concurrently.
You can use TotalRowsProcessed with Array DML enabled.
Yes. But it doesn't set numbers sequentially.
ex. It sets '12' to my first row, '34' to second row and so on.
Yes. But it doesn't set numbers sequentially.
ex. It sets '12' to my first row, '34' to second row and so on.
Can you elaborate on that? This doesn't make sense to me because first row, 2nd row, etc, just depends on the "order by" of your select.
When I import excel data with expression ([ToatalRowsAdded +1]), the expression doesn't work as I expected.
Ex. There are some data in excel file like below. (let's say its name is 'tmp.xlsx'):
seq | data
(empty) | something1
(empty) | something2
....
and there's a table called 'TMP':
CREATE TABLE TMP(
SEQ NUMBER(5),
DATA VARCHAR2(50)
);
When I import data from tmp.xlsx with 'Use Array DML' option and 'TotalRowsAdded' expression at SEQ field, the value is randomly assigned.
Below is an example of TMP table's data after import:
[Result]
SEQ | DATA
12 | something1
34 | something2
....
Try TotalRowsProcessed instead of TotalRowsAdded.
If that doesn't help, please let me know and then I'll test it.
It works!!
I appreciate your help.
1 Like