CODEWITHSUNDEEP
csvsql-loader
Matteo
Matteo

Reputation: 346

Create .csv file through a Control file

I have to create some csv files through a sql loader control file reference. I have written this code:

LOAD DATA
INFILE '$ROOT_FOLDER/xxx/xxx/xxx/example.csv' "str '\n'"
discardfile '$ROOT_FOLDER/xxx/xxx/xxx/example.dsc'
Append

INTO TABLE TABLE1
when (9) = ','
FIELDS TERMINATED BY ','
(
  Field1                       CHAR  "to_date(:Field1,'YYYYMMDD')"
  ,Field2                      CHAR
  ,Field3                      FILLER char
  ,Field4                      FILLER char
  ,Field5                      CHAR
)

When I run the code three csv files are created, two are correct and the last one not.

This is the example of one correct csv:

INT_PROCESS,18.10.2019 05:35:02,18.10.2019 05:35:02,9
Date,Instrument ID,Instrument ID Type,Exchange Code,Close
20191017,XXXX=,F1,F2, -.49900
,GBP3MFSR=,F1,F2,
20191017,AAAA=,F1,F2,2.00000
20191016,BBBB=,F1,F2,1.90000
20191017,CCCC=,F1,F2, -.44800
20191017,DDDD=,F1,F2, -.41000
20191016,EEEE=,F1,F2,.71090
,FFFF=,F1,F2,
,GGGG=,F1,F2,

and this what I have into the wrong one:

Date,Instrument ID,Instrument ID Type,Exchange Code,Close
20191016,XXXX=,F1,F3, -.551
20191016,YYYY=,F1,F2, -.466

How can I fix my code in such a way that I have three csv with the same structure?

Upvotes: 0

Views: 1164

Answers (1)

Gary_W
Gary_W

Reputation: 10360

Ideally you don't want any files but the .log file created. See the .log file for details on the errors, but sqlldr attempts to load the data in the INFILE clause into the table specified. Output will be a .log file, a .bad file for records that caused errors and a discard file that contains records that did not meet your condition criteria. Your goal should be to load without creating a .bad or .dsc file.

Also I think your Field1 entry is wrong as you normally don't insert a DATE datatype into a CHAR column.

You may need to do some reading before continuing: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-sql-loader-control-file-contents.html#GUID-34A050B6-3FD7-4B77-97D2-04C03D359D16

Upvotes: 1

Related Questions