-1

I have csv file as: 1,2,3 control file: fields terminated by ',' however, when I load the data, I got ORA-01722 invalid number. if I add , at the end of the file like 1,2,3, then all three columns will be loaded, is there any option to load all three columns without ending comma?

xiaoyunwu
  • 69
  • 6
  • 5
    I wonder if you have some non-printing character at the end of some or all records. Generally, there's no need to have the trailing comma for sqlldr to work. – Dave Costa Aug 19 '21 at 20:43
  • Here's a working example, which might help as a reference: https://stackoverflow.com/questions/53317198/read-data-from-csv-fileseperated-by-comma-data-using-sql-loader-when-there – Christian Aug 19 '21 at 20:47
  • 1
    It could be a carriage return, if the file was created in WIndows and loaded in Unix/Linux. [This is very similar](https://stackoverflow.com/a/36409049/266304); there the extra character made the value too big, but with a number column it would cause this error. – Alex Poole Aug 19 '21 at 22:05
  • 1
    Thank you all very much for the help. I did save in windows and loaded into Unix. I checked my file, it has control M, carriage return, once I deleted \r, I was able to load successfully. Thank you all. – xiaoyunwu Aug 20 '21 at 00:52
  • 1
    @xiaoyunwu - glad you solved your poblem. Why not turn your comment into a proper answer and accept it? Then your question will show as "answered" in searches, which will make it more useful to any Seeker who comes along with the same problem? (Accepting your own answer is not only allowed it is encouraged.) – APC Aug 20 '21 at 10:04

1 Answers1

1

Thank you all very much for the help. I did save in windows and loaded into Unix. I checked my file, it has control M, carriage return, once I deleted \r, I was able to load successfully. Thank you all.

xiaoyunwu
  • 69
  • 6