sqlloader - invalid number
Attached is my control file and .csv file. I did attach another .xls which is the copy of .csv file for matching the name of the fields with the columns.
I am using .csv thru sqlloader, I keep getting "invalid number" when the data is numeric..
This is happening for columns, BX(Expenses- name of the column) and BB(TRA - name of the column).
Also, I get error on column BF, saying actual length is 4, maximum length is 3. But I see only three characters in.csv file for the column BF(settle_ccy -name of the column). Also, you can see I have used TRIM in my control file for varchar.
This is very urgent issue, please help !
Quick response is appreciated.
stg-citco-intraday-trade-ctl.txt
test-0523-orig.csv
test-0523.xls
I am using .csv thru sqlloader, I keep getting "invalid number" when the data is numeric..
This is happening for columns, BX(Expenses- name of the column) and BB(TRA - name of the column).
Also, I get error on column BF, saying actual length is 4, maximum length is 3. But I see only three characters in.csv file for the column BF(settle_ccy -name of the column). Also, you can see I have used TRIM in my control file for varchar.
This is very urgent issue, please help !
Quick response is appreciated.
stg-citco-intraday-trade-ctl.txt
test-0523-orig.csv
test-0523.xls
flow01
can you provide the table definition of stg_citco_intraday_trade ?
$tradehome points to the right place ?
I used an external table definition csv_load (definition attached (all columns varchar(255) except the one you mentioned) and a directory 'ext' i already had defined for
SQL> select expenses, settle_ccy, tra from csv_load
2 /
EXPENSES SET TRA
---------- --- ----------
0 USD 1
0 USD 1
csv-load.v2.tab.txt
I used an external table definition csv_load (definition attached (all columns varchar(255) except the one you mentioned) and a directory 'ext' i already had defined for
SQL> select expenses, settle_ccy, tra from csv_load
2 /
EXPENSES SET TRA
---------- --- ----------
0 USD 1
0 USD 1
csv-load.v2.tab.txt
How may records are loaded, this is feedback from the sqlldr command.
Have a look at the discard (.dsc) file to see which records are not loaded.
Have a look at the discard (.dsc) file to see which records are not loaded.
ASKER
I tried to basically use a shell script for reading this, here is my shell and then tried to convert .csv file from dos to unix format and then it worked.
Please find attached DDL for the table stg_citco_intraday_trade. The first column is a sequence number for which I created a sequence and also created a trigger (before insert) so that it gets first column loaded there for every insert statement. Am attaching those as well.
I may have to change my shell to ftp .csv file. Could someone suggest how to do that ?
ddl.sql
Please find attached DDL for the table stg_citco_intraday_trade. The first column is a sequence number for which I created a sequence and also created a trigger (before insert) so that it gets first column loaded there for every insert statement. Am attaching those as well.
#!/bin/ksh -xve
date
echo 'Started sqlloader stg_citco_intraday_trade'
if ( test -e "$tradehome/ods/data/csv/test_0523.csv")
then
dos2unix $tradehome/ods/data/csv/test_0523.csv $tradehome/ods/data/csv/test_0523.csv
echo $odspass | sqlldr $odsuser control=$tradehome/ods/ctl/stg_citco_intraday_trade.ctl log=$tradehome/ods/log/stg_citco_intraday_trade_sqlldr.log
cat $tradehome/ods/log/stg_citco_intraday_trade_sqlldr.log
fi
echo 'Ended sqlloader stg_citco_intraday_trade'
dateI may have to change my shell to ftp .csv file. Could someone suggest how to do that ?
ddl.sql
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
useful