Issue with data format when loading csv file through sql*loader

whenever I try to upload a csv file through sql*loader it fails with ORA-01858: a non-numeric character was found where a numeric was expected, I have tried all possible date formats and all end with the same error.  Attached is the log file from the last upload attempt.

Any help on resolving this issue would be greatly appreciated.
bluenose55Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Attachment?

Sounds like you are using DATES.  You can add a TO_DATE to the control file.  I've posted an example here before.  Let me see if I can find it.
0
slightwv (䄆 Netminder) Commented:
It's not my post but it was the first one I found.  It should be good:
https://www.experts-exchange.com/questions/29094105/Need-help-on-SQL-loader-record-delimiter.html#a42530599
0
bluenose55Author Commented:
This the specific entry in  the control file.

DATA_HEADING2                        NEXT     *   ,  O(") CHARACTER            
  (FILLER FIELD)
CONFIG_TIME                          NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "to_date(RTRIM(:config_time,'H'),'DD/MM/RR HH24:MI')"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SujithData ArchitectCommented:
Provide a snippet of your data file.
0
bluenose55Author Commented:
Extract from csv

H,FLASH_SERIAL,02F6555D9F09480D
H,CONFIG_TIME,12/04/18 14:21
H,IFS_SERIAL,10/C4EN11-8069
H,CONFIG_CODE,11
H,FIRMWARE_REVISION,212
H,DATE_OF_CAL,20/04/18 07:40
H,PRODUCT,CAT4 Basic
0
slightwv (䄆 Netminder) Commented:
Looks like you have a data issue in the config_time field that isn't matching the date format mask you provided.

Trimming 'H' characters from the end seems odd but it's your data.
0
bluenose55Author Commented:
This something I have inherited, but I have tried loading without the rtrim  and same result.  I have taken the data and used it in an update statement and it loads without issue.
0
SujithData ArchitectCommented:
Do you have a 3 column CSV?
can you provide your full control file?
0
slightwv (䄆 Netminder) Commented:
Column 3 has a LOT of data that isn't a DATE so you will not be to sue TO_DATE on it.

Since you inherited this, has it ever worked or has the input file changed recently?
0
bluenose55Author Commented:
Load DATA
INFILE 'E:\IFS_IMPORT\CALIBRATION_IMPORT\CALIBRATION4\TO_IMPORT\CAT4_CAL_2018100073925_07F4795B9EFB47FF.csv'
Append CONCATENATE 50
INTO TABLE ifsapp.RD_CALIBRATION4_DATA_TAB
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(data_heading1 FILLER,
flash_serial "RTRIM(:flash_serial,'H')",
data_heading2 FILLER,
config_time "to_date(RTRIM(:config_time,'H'),'DD/MM/RR HH24:MI')",
data_heading3 FILLER,
ifs_serial "RTRIM(:ifs_serial,'H')",
data_heading4 FILLER,
config_code "RTRIM(:config_code,'H')",
data_heading5 FILLER,
firmware_revision "RTRIM(:firmware_revision,'H')",
data_heading6 FILLER,
date_of_cal "to_date(RTRIM(:date_of_cal,'H'),'DD/MM/RR HH24:MI')",
data_heading7 FILLER,
product "RTRIM(:product,'H')",
data_heading8 FILLER,
technicianid "RTRIM(:technicianid,'H')",
data_heading9 FILLER,
location1 "RTRIM(:location1,'H')",
data_heading10 FILLER,
location2 "RTRIM(:location2,'H')",
data_heading11 FILLER,
location3 "RTRIM(:location3,'H')",
data_heading12 FILLER,
location4 "RTRIM(:location4,'H')",
data_heading13 FILLER,
location5 "RTRIM(:location5,'H')",
data_heading14 FILLER,
temperture "RTRIM(:temperture,'H')",
data_heading15 FILLER,
relative_humidity "RTRIM(:relative_humidity,'H')",
data_heading16 FILLER,
sequence_file "RTRIM(:sequence_file,'H')",
data_heading17 FILLER,
ITCC_version "RTRIM(:ITCC_version,'H')",
data_heading18 FILLER,
rig_id "RTRIM(:rig_id,'H')",
data_heading19 FILLER,
rig_description "RTRIM(:rig_description, 'T')",
data_heading26 FILLER,
data_heading27 FILLER,
data_heading28 FILLER,
data_heading29 FILLER,
data_heading30 FILLER,
modelnumber1  char,
modeldesc1    char,
sernumb1      char,
lastcaldate1  "to_date(:lastcaldate1,'DD/MM/YYYY')",
duecaldate1   "to_date(rtrim(:duecaldate1,'D'),'DD/MM/YYYY')",
modelnumber2  char,
modeldesc2    char,
sernumb2      char,
lastcaldate2  "to_date(:lastcaldate2,'DD/MM/YYYY')",
duecaldate2   "to_date(rtrim(:duecaldate2,'D'),'DD/MM/YYYY')",
modelnumber3  char,
modeldesc3    char,
sernumb3      char,
lastcaldate3  "to_date(:lastcaldate3,'DD/MM/YYYY')",
duecaldate3   "to_date(rtrim(:duecaldate3,'T'),'DD/MM/YYYY')",
data_heading20 FILLER,
data_heading21 FILLER,
data_heading22 FILLER,
data_heading23 FILLER,
data_heading24 FILLER,
data_heading25 FILLER,
frequency1 "rdinfo.fn_rd_num_or_null(:frequency1)",
ratio1 "rdinfo.fn_rd_num_or_null(:ratio1)",
uncertainty1 "rdinfo.fn_rd_num_or_null(:uncertainty1)",
highlimit1 "rdinfo.fn_rd_num_or_null(:highlimit1)",
lowlimit1 "rdinfo.fn_rd_num_or_null(:lowlimit1)",
passfail1 "RTRIM(:passfail1,'D')",
frequency2 "rdinfo.fn_rd_num_or_null(:frequency2)",
ratio2                "rdinfo.fn_rd_num_or_null(:ratio2)",
uncertainty2         "rdinfo.fn_rd_num_or_null(:uncertainty2)",
highlimit2           "rdinfo.fn_rd_num_or_null(:highlimit2)",
lowlimit2            "rdinfo.fn_rd_num_or_null(:lowlimit2)",
passfail2             "RTRIM(:passfail2,'D')",
frequency3           "rdinfo.fn_rd_num_or_null(:frequency3)",
ratio3                "rdinfo.fn_rd_num_or_null(:ratio3)",
uncertainty3         "rdinfo.fn_rd_num_or_null(:uncertainty3)",
highlimit3           "rdinfo.fn_rd_num_or_null(:highlimit3)",
lowlimit3            "rdinfo.fn_rd_num_or_null(:lowlimit3)",
passfail3             "RTRIM(:passfail3,'D')",
frequency4           "rdinfo.fn_rd_num_or_null(:frequency4)",
ratio4                "rdinfo.fn_rd_num_or_null(:ratio4)",
uncertainty4         "rdinfo.fn_rd_num_or_null(:uncertainty4)",
highlimit4           "rdinfo.fn_rd_num_or_null(:highlimit4)",
lowlimit4            "rdinfo.fn_rd_num_or_null(:lowlimit4)",
passfail4             "RTRIM(:passfail4,'D')",
frequency5           "rdinfo.fn_rd_num_or_null(:frequency5)",
ratio5                "rdinfo.fn_rd_num_or_null(:ratio5)",
uncertainty5         "rdinfo.fn_rd_num_or_null(:uncertainty5)",
highlimit5           "rdinfo.fn_rd_num_or_null(:highlimit5)",
lowlimit5            "rdinfo.fn_rd_num_or_null(:lowlimit5)",
passfail5             "RTRIM(:passfail5,'D')",
frequency6           "rdinfo.fn_rd_num_or_null(:frequency6)",
ratio6                "rdinfo.fn_rd_num_or_null(:ratio6)",
uncertainty6         "rdinfo.fn_rd_num_or_null(:uncertainty6)",
highlimit6           "rdinfo.fn_rd_num_or_null(:highlimit6)",
lowlimit6            "rdinfo.fn_rd_num_or_null(:lowlimit6)",
passfail6             "RTRIM(:passfail6,'D')",
frequency7           "rdinfo.fn_rd_num_or_null(:frequency7)",
ratio7                "rdinfo.fn_rd_num_or_null(:ratio7)",
uncertainty7         "rdinfo.fn_rd_num_or_null(:uncertainty7)",
highlimit7           "rdinfo.fn_rd_num_or_null(:highlimit7)",
lowlimit7            "rdinfo.fn_rd_num_or_null(:lowlimit7)",
passfail7             "RTRIM(:passfail7,'D')",
frequency8           "rdinfo.fn_rd_num_or_null(:frequency8)",
ratio8                "rdinfo.fn_rd_num_or_null(:ratio8)",
uncertainty8         "rdinfo.fn_rd_num_or_null(:uncertainty8)",
highlimit8           "rdinfo.fn_rd_num_or_null(:highlimit8)",
lowlimit8            "rdinfo.fn_rd_num_or_null(:lowlimit8)",
passfail8             "RTRIM(:passfail8,'D')",
frequency9           "rdinfo.fn_rd_num_or_null(:frequency9)",
ratio9                "rdinfo.fn_rd_num_or_null(:ratio9)",
uncertainty9         "rdinfo.fn_rd_num_or_null(:uncertainty9)",
highlimit9           "rdinfo.fn_rd_num_or_null(:highlimit9)",
lowlimit9            "rdinfo.fn_rd_num_or_null(:lowlimit9)",
passfail9             "RTRIM(:passfail9,'D')",
frequency10           "rdinfo.fn_rd_num_or_null(:frequency10)",
ratio10                "rdinfo.fn_rd_num_or_null(:ratio10)",
uncertainty10         "rdinfo.fn_rd_num_or_null(:uncertainty10)",
highlimit10           "rdinfo.fn_rd_num_or_null(:highlimit10)",
lowlimit10            "rdinfo.fn_rd_num_or_null(:lowlimit10)",
passfail10             "RTRIM(:passfail10,'D')",
frequency11           "rdinfo.fn_rd_num_or_null(:frequency11)",
ratio11                "rdinfo.fn_rd_num_or_null(:ratio11)",
uncertainty11         "rdinfo.fn_rd_num_or_null(:uncertainty11)",
highlimit11           "rdinfo.fn_rd_num_or_null(:highlimit11)",
lowlimit11            "rdinfo.fn_rd_num_or_null(:lowlimit11)",
passfail11            "RTRIM(:passfail11,'D')"
)
0
bluenose55Author Commented:
Yes it has work, but since the 4 colums have been added, control file amended to allow for this, the new columns are
config_time,
ifs_serial
config_code
firmware_revision
0
slightwv (䄆 Netminder) Commented:
In your CSV extract above, does the file have the data on separate lines like that?  I'm not seeing how that loads given the control file you posted.

What we really need is something we can actually load up and test on our systems.  That control file and CSV sample don't seem compatible.
0
bluenose55Author Commented:
The cs,v starts off with just the one value but as it work down to about line 20 then it  reads 5 columns of data.
Like so
H,RIG_ID,Axiom CAT 2
H,RIG_DESCRIPTION,ITCC Axiom CAT 2
T,MODELNUMBER,MODELDESC,SERNUMB,LASTCALDATE,DUECALDATE
D,33120A,Hewlett Packard Function Generator,US36007770,07/11/2017,07/11/2018
D,34401A,Hewlett Packard Digital Multimeter,73350,01/08/2017,01/08/2018
D,QL355TP,Thurlby Thandar Power Supply,430832,01/09/2017,01/09/2018
T,FREQUENCY,RATIOMETRICCALVALUE,UNCERTAINTY,HIGHLIMIT,LOWLIMIT,PASSFAIL
D,635.0,0.9830,0.0005,1.10,0.88,Pass
D,22070.0,1.0058,0.0005,1.10,0.88,Pass
D,32758.0,1.0124,0.0005,1.10,0.88,Pass
D,131000.0,1.0076,0.0005,1.10,0.88,Pass
0
slightwv (䄆 Netminder) Commented:
OK, I learned a new SQL*Loader trick today.  It was the CONCATENATE parameter.

You are telling SQL Loader to concatenate 50 rows in the CSV file into one logical row.

If you added rows to the CSV and inserted new fields into the control file, did you change the number of rows to concatenate?

If not, that is likely the problem.  You'll need to figure out the new number to use.
0
bluenose55Author Commented:
I don't think the issue is related to Concatenate, at present I only trying to load one file, saying that I have adjust the concatenate entry several time but is still fails with the a non numeric character found in the date
0
slightwv (䄆 Netminder) Commented:
It is only a theory but if the number of logical rows is off then the error can be caused from later rows.

Everything is a theory unless we can get a complete test case where we can set things up on our side and test.

That would include the destination table definition, the control file (which you've posted) and a complete sample file.
0
SujithData ArchitectCommented:
can you post two logical records from the top of your data file here?
I.e. two records that goes into your database table. It should have the file headers etc. Will try to work out the way your control file is trying to interpret the records.
0
bluenose55Author Commented:
Thanks to all those that have tried to help, I have decided its not worth pursuing an answer to resolve the issue in it current format.  I have revised both the csv file and the control file and my upload works.
0
slightwv (䄆 Netminder) Commented:
I suggest you delete the question.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bluenose55Author Commented:
There was no solution, not progressing at all, so Its best to close it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.