Thanks for the question.

Asked: February 17, 2022 - 2:34 am UTC

Last updated: February 24, 2022 - 7:18 am UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

I have a CSV file that's tab delimited. Columns wrapped in double quotes. I am trying everything to get this to import using sqlldr. For a while I was having issues with the date. Now I'm getting an error saying "ORA-12899: value too large for column LASTEDITUSERNAME (actual: 21, maximum: 20)". The LASTEDITUSERNAME isn't 21 characters. It's like 7-8. I'm guessing that's not the actual issue somehow.

In trying anything to get this to work I just copied all 7000 records from the csv file and added it to the control file after BEGINDATA. It worked perfectly. So it works if the data is in the control file, but not if the same data is in a CSV file. I need it to work in the csv file somehow. Just have no idea at this point what the difference might be.

Table
-----

CREATE TABLE inputtable
(
  QUIZDATE          DATE,
  QUIZNAME          VARCHAR2(100 BYTE),
  LASTEDITNAME      VARCHAR2(75 BYTE),
  LASTEDITUSERNAME  VARCHAR2(20 BYTE),
  LASTEDITROLE      VARCHAR2(20 BYTE),
  LASTEDITIP        VARCHAR2(20 BYTE),
  FULLNAME          VARCHAR2(75 BYTE),
  TYPE              VARCHAR2(20 BYTE),
  GRADE             NUMBER,
  ATTEMPTACTIVITY   DATE,
  EVENT             VARCHAR2(50 BYTE),
  CREATEDAT         DATE DEFAULT SYSDATE
);

Sample Data
-----------

"Date" "Column" "Last Edited by: Name" "Last Edited by: Username" "Last Edited by: Role" "Last Edited by: IP Address" "User" "Type" "Value" "Attempt Activity" "Event"
"Feb 14, 2022 11:29:16 AM" "Quiz for abc" "Shaina Johnson" "shajnson" "S" "12.34.56.123" "Shaina Johnson" "Grade" "40.000000000000000" "Feb 14, 2022 11:22:27 AM" "Attempt Grade"
"Feb 14, 2022 10:39:36 AM" "Quiz for def" "Abby Rose" "abbrose" "S" "65.10.56.254" "Abby Rose" "Grade" "100.000000000000000" "Feb 14, 2022 10:39:07 AM" "Attempt Grade"
"Feb 14, 2022 10:38:06 AM" "Quiz for ghi" "Abby Rose" "abbrose" "S" "65.10.56.254" "Abby Rose" "Grade" "90.000000000000000" "Feb 14, 2022 10:37:25 AM" "Attempt Grade"
"Feb 14, 2022 10:34:17 AM" "Quiz for jkl" "Abby Rose" "abbrose" "S" "65.10.56.254" "Abby Rose" "Grade" "70.000000000000000" "Feb 14, 2022 10:32:17 AM" "Attempt Grade"

Control File Calling the csv file (DOESN't Work)
---------------------------------

options (skip=1)
load data 
infile '.\input.csv'
BADFILE '.\output.bad'
DISCARDFILE '.\output.dsc'

truncate
into table inputtable
Fields terminated by X'09' 
optionally ENCLOSED BY '"' 
trailing nullcols
(
  QUIZDATE "to_date(:QUIZDATE,'MON DD, YYYY FMHH:MI:SS AM')",
  QUIZNAME CHAR(4000),
  LASTEDITNAME CHAR(4000),
  LASTEDITUSERNAME CHAR(4000),
  LASTEDITROLE CHAR(4000),
  LASTEDITIP CHAR(4000),
  FULLNAME CHAR(4000),
  TYPE CHAR(4000),
  GRADE,
  ATTEMPTACTIVITY "to_date(:ATTEMPTACTIVITY,'MON DD, YYYY FMHH:MI:SS AM')",
  EVENT CHAR(4000)
)

Control File with the data included (WORKS Great)
-----------------------------------

options (skip=1)
load data 
infile *
BADFILE '.\output.bad'
DISCARDFILE '.\output.dsc'

truncate
into table inputtable
Fields terminated by X'09' 
optionally ENCLOSED BY '"' 
trailing nullcols
(
  QUIZDATE "to_date(:QUIZDATE,'MON DD, YYYY FMHH:MI:SS AM')",
  QUIZNAME,
  LASTEDITNAME,
  LASTEDITUSERNAME ,
  LASTEDITROLE,
  LASTEDITIP,
  FULLNAME,
  TYPE,
  GRADE,
  ATTEMPTACTIVITY "to_date(:ATTEMPTACTIVITY,'MON DD, YYYY FMHH:MI:SS AM')",
  EVENT
)
BEGINDATA
"Date" "Column" "Last Edited by: Name" "Last Edited by: Username" "Last Edited by: Role" "Last Edited by: IP Address" "User" "Type" "Value" "Attempt Activity" "Event"  
"Feb 14, 2022 11:29:16 AM" "Quiz for abc" "Shaina Johnson" "shajnson" "S" "12.34.56.123" "Shaina Johnson" "Grade" "40.000000000000000" "Feb 14, 2022 11:22:27 AM" "Attempt Grade"  
"Feb 14, 2022 10:39:36 AM" "Quiz for def" "Abby Rose" "abbrose" "S" "65.10.56.254" "Abby Rose" "Grade" "100.000000000000000" "Feb 14, 2022 10:39:07 AM" "Attempt Grade"  
"Feb 14, 2022 10:38:06 AM" "Quiz for ghi" "Abby Rose" "abbrose" "S" "65.10.56.254" "Abby Rose" "Grade" "90.000000000000000" "Feb 14, 2022 10:37:25 AM" "Attempt Grade"  
"Feb 14, 2022 10:34:17 AM" "Quiz for jkl" "Abby Rose" "abbrose" "S" "65.10.56.254" "Abby Rose" "Grade" "70.000000000000000" "Feb 14, 2022 10:32:17 AM" "Attempt Grade"

Any idea on why it works in one, but not the other? Am I calling the input file wrong somehow? This isn't my first sqlldr script, but it certainly is giving me the most problems. Thanks!

and Connor said...

Because of the dramas of tabs/spaces etc, can you please email the CSV file to asktom_us@oracle.com with a subject of: "Question 9546119100346461186".

Then ping this question back to us

Then we can try replicate the issue here.

=============================

OK, this had me befuddled for a while but your control file is fine. What is not "fine" is the encoding of your file. Your file is UTF-16. This is why when we copy the data into your control file and save it, it now works because the encoding is going to change. For example, I did exactly what you did - took the original file, opened it and then saved as a new file...

Old file:

X:\tmp>od -x old.csv
0000000 feff 0022 0046 0065 0062 0020 0031 0034
0000020 002c 0020 0032 0030 0032 0032 0020 0031
0000040 0031 003a 0032 0039 003a 0031 0036 0020
0000060 0041 004d 0022 0009 0022 0051 0075 0069
0000100 007a 0020 0066 006f 0072 0020 0061 0062
0000120 0063 0022 0009 0022 0053 0068 0061 0069
0000140 006e 0061 0020 004a 006f 0068 006e 0073
0000160 006f 006e 0022 0009 0022 0073 0068 0061
0000200 006a 006e 0073 006f 006e 0022 0009 0022
0000220 0053 0022 0009 0022 0031 0032 002e 0033
...
...

New file:

X:\tmp>od -x new.csv
0000000 4622 6265 3120 2c34 3220 3230 2032 3131
0000020 323a 3a39 3631 4120 224d 2209 7551 7a69
0000040 6620 726f 6120 6362 0922 5322 6168 6e69
0000060 2061 6f4a 6e68 6f73 226e 2209 6873 6a61
0000100 736e 6e6f 0922 5322 0922 3122 2e32 3433
0000120 352e 2e36 3231 2233 2209 6853 6961 616e
0000140 4a20 686f 736e 6e6f 0922 4722 6172 6564
0000160 0922 3422 2e30 3030 3030 3030 3030 3030
0000200 3030 3030 2230 2209 6546 2062 3431 202c
0000220 3032 3232 3120 3a31 3232 323a 2037 4d41
0000240 0922 4122 7474 6d65 7470 4720 6172 6564
0000260 0922 0d09 220a 6546 2062 3431 202c 3032
...
...

It was the cut/paste that fixed the data, even it looks the same to the naked eye. So sqlldr never really had a chance :-)

The solution is easy

load data 
CHARACTERSET UTF16           <<<=====
infile ...

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

from sql*loader Express mode

Rajeshwaran Jeyabal, February 17, 2022 - 8:21 am UTC

Team,

using sql*loader express mode, was able to get loaded without columns having default values.
but when then the columns having default values introduced it got error.
so how can we use sql*loader express mode for columns having default values ?

demo@XEPDB1> CREATE TABLE INPUTTABLE
  2  (
  3    QUIZDATE          DATE,
  4    QUIZNAME          VARCHAR2(100 BYTE),
  5    LASTEDITNAME      VARCHAR2(75 BYTE),
  6    LASTEDITUSERNAME  VARCHAR2(20 BYTE),
  7    LASTEDITROLE      VARCHAR2(20 BYTE),
  8    LASTEDITIP        VARCHAR2(20 BYTE),
  9    FULLNAME          VARCHAR2(75 BYTE),
 10    TYPE              VARCHAR2(20 BYTE),
 11    GRADE             NUMBER,
 12    ATTEMPTACTIVITY   DATE,
 13    EVENT             VARCHAR2(50 BYTE)
 14  );

Table created.

D:\Trash> type par.txt
TABLE=INPUTTABLE log=log.txt
data=data.txt
skip=1
date_format='MON DD, YYYY FMHH:MI:SS AM'
terminated_by=' '
optionally_enclosed_by='"'

D:\Trash>sqlldr demo/demo@pdb1 parfile=par.txt

SQL*Loader: Release 21.0.0.0.0 - Production on Thu Feb 17 13:43:55 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-800: SKIP specified and ignored
Express Mode Load, Table: INPUTTABLE
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table INPUTTABLE:
  4 Rows successfully loaded.

Check the log files:
  log.txt
  log.txt_xt
for more information about the load.

demo@XEPDB1> select count(*) from inputtable ;

  COUNT(*)
----------
         4

demo@XEPDB1> drop TABLE INPUTTABLE purge;

Table dropped.

demo@XEPDB1> CREATE TABLE INPUTTABLE
  2  (
  3    QUIZDATE          DATE,
  4    QUIZNAME          VARCHAR2(100 BYTE),
  5    LASTEDITNAME      VARCHAR2(75 BYTE),
  6    LASTEDITUSERNAME  VARCHAR2(20 BYTE),
  7    LASTEDITROLE      VARCHAR2(20 BYTE),
  8    LASTEDITIP        VARCHAR2(20 BYTE),
  9    FULLNAME          VARCHAR2(75 BYTE),
 10    TYPE              VARCHAR2(20 BYTE),
 11    GRADE             NUMBER,
 12    ATTEMPTACTIVITY   DATE,
 13    EVENT             VARCHAR2(50 BYTE),
 14    CREATEDAT         DATE DEFAULT SYSDATE not null
 15  );

Table created.

demo@XEPDB1>

D:\Trash>sqlldr demo/demo@pdb1 parfile=par.txt

SQL*Loader: Release 21.0.0.0.0 - Production on Thu Feb 17 13:44:48 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-800: SKIP specified and ignored
Express Mode Load, Table: INPUTTABLE
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table INPUTTABLE:
  0 Rows successfully loaded.

Check the log files:
  log.txt
  log.txt_xt
for more information about the load.

looked into the log file it shows this
(looks like it was expecting this filed CREATEDAT from the datafile, but it doesnot exists) - so how to overcome this error ?


Field Definitions for table SYS_SQLLDR_X_EXT_INPUTTABLE
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Reject rows with all null fields

  Fields in Data Source: 

    QUIZDATE                        CHAR (255)
      Date datatype DATE, date mask MON DD, YYYY FMHH:MI:SS AM
      Terminated by " "
      Enclosed by """ and """
      Trim whitespace from left and right
    QUIZNAME                        CHAR (255)
      Terminated by " "
      Enclosed by """ and """
      Trim whitespace from left and right
    LASTEDITNAME                    CHAR (255)
      Terminated by " "
      Enclosed by """ and """
      Trim whitespace from left and right
    LASTEDITUSERNAME                CHAR (255)
      Terminated by " "
      Enclosed by """ and """
      Trim whitespace from left and right
    LASTEDITROLE                    CHAR (255)
      Terminated by " "
      Enclosed by """ and """
      Trim whitespace from left and right
    LASTEDITIP                      CHAR (255)
      Terminated by " "
      Enclosed by """ and """
      Trim whitespace from left and right
    FULLNAME                        CHAR (255)
      Terminated by " "
      Enclosed by """ and """
      Trim whitespace from left and right
    TYPE                            CHAR (255)
      Terminated by " "
      Enclosed by """ and """
      Trim whitespace from left and right
    GRADE                           CHAR (255)
      Terminated by " "
      Enclosed by """ and """
      Trim whitespace from left and right
    ATTEMPTACTIVITY                 CHAR (255)
      Date datatype DATE, date mask MON DD, YYYY FMHH:MI:SS AM
      Terminated by " "
      Enclosed by """ and """
      Trim whitespace from left and right
    EVENT                           CHAR (255)
      Terminated by " "
      Enclosed by """ and """
      Trim whitespace from left and right
    CREATEDAT                       CHAR (255)
      Date datatype DATE, date mask MON DD, YYYY FMHH:MI:SS AM
      Terminated by " "
      Enclosed by """ and """
      Trim whitespace from left and right
KUP-04021: field formatting error for field CREATEDAT
KUP-04023: field start is after end of record
KUP-04101: record 1 rejected in file D:\Trash\data.txt
KUP-04021: field formatting error for field CREATEDAT
KUP-04023: field start is after end of record
KUP-04101: record 2 rejected in file D:\Trash\data.txt
KUP-04021: field formatting error for field CREATEDAT
KUP-04023: field start is after end of record
KUP-04101: record 3 rejected in file D:\Trash\data.txt
KUP-04021: field formatting error for field CREATEDAT
KUP-04023: field start is after end of record
KUP-04101: record 4 rejected in file D:\Trash\data.txt
KUP-04021: field formatting error for field CREATEDAT
KUP-04023: field start is after end of record
KUP-04101: record 5 rejected in file D:\Trash\data.txt

February 24, 2022 - 7:15 am UTC

That's why its called express mode :-) Its for the most common set of use cases.

To "fill in missing fields" you need "TRAILING NULLCOLS" which is not part of the express mode options.

I tried the following (so you don't have to)

- using FIELD_NAMES does not resolve it
- making the column invisible does not resolve it

I think you'll need to go back to standard SQLLoader, or just use the GENERATE_ONLY option to get an external table and modify the insert accordingly.

Thank you!

A reader, February 17, 2022 - 3:30 pm UTC

Thank you so much for figuring that out. I see now at the bottom of VS Code it does say UTF-16 LE for that file. I briefly had played around with the characterset option, but didn't know what I was doing exactly. Not sure I would have figured that out.

For completeness I did initially run into an error. I just had to change

Fields terminated by 'X'09'' to
Fields terminated by '\t'

and it worked.

Thanks again!

February 24, 2022 - 7:18 am UTC

Glad we could help. It was a nice puzzler

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

from sql*loader Express mode

Thank you!

More to Explore

Utilities

Sqlldr issue - loads data from control file, but not csv

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

from sql*loader Express mode

Thank you!

More to Explore

Utilities