Use the number of characters as delimiter sql

Question

I have this exercise: having this type of text file

10012020/07/057
10022020/07/055
10032020/07/100
10042020/07/079
10052020/07/103
10062020/07/098
10072020/07/104
10082020/07/101
10092020/07/104
10102020/07/063

Insert the records in an external table (like the one below) using the number of characters as delimiter.

CREATE TABLE Ftest ( 
  idF   VARCHAR(255),
  dt   DATE,
  n      VARCHAR(255)
)
ORGANIZATION EXTERNAL
  (
    TYPE oracle_loader
  DEFAULT DIRECTORY desktop    
  ACCESS PARAMETERS
  ( 
    RECORDS DELIMITED BY '\n'
    BADFILE     CHARACTER
      DISCARDFILE CHARACTER
      LOGFILE     CHARACTER
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL     
      (
        idF     CHAR(255),
        dt      CHAR(255) date_format DATE mask 'YYYY/MM/DD',
        numero  CHAR(255)
      )  
  )
  LOCATION ('Test.csv')
)
REJECT LIMIT UNLIMITED;

Taking the first record, I have: 10012020/07/057 so

Field 1 (idF) = 1001 [4 characters]
Field 2 (dt)= 2020/07/05 [10 characters]
Field 3 (n)= 7 [1 characters]

I have no idea what sql commands to use to specify the delimiter for each field. Do you have any suggestions? Or a solution to modify the file before it is inserted in the table, but always in sql language?

Are you using Oracle or SQL Server? (Please remove the redundant tag). — Dale K, Jul 09 '20 at 09:23

score 0 · Accepted Answer · answered Jul 09 '20 at 09:41

This is, obviously, Oracle. Therefore: as there are no delimiters, you'll have to fetch data positionally. Here's how:

SQL> CREATE TABLE Ftest (
  2    idF        VARCHAR(4),
  3    dt         DATE,
  4    numero     VARCHAR(1)
  5  )
  6  ORGANIZATION EXTERNAL
  7    (
  8      TYPE oracle_loader
  9      DEFAULT DIRECTORY EXT_DIR
 10      ACCESS PARAMETERS
 11      (
 12        RECORDS DELIMITED BY newline
 13        LOGFILE 'ftest.log'
 14        fields
 15          (idf    position(1:4),
 16           dt     position(5:14) date 'yyyy/mm/dd',
 17           numero position(15:15)
 18          )
 19      )
 20    LOCATION ('exercise.txt')
 21  )
 22  REJECT LIMIT UNLIMITED;

Table created.

Does it work?

SQL> select * from ftest;

IDF  DT       N
---- -------- -
1001 05.07.20 7
1002 05.07.20 5
1003 10.07.20 0
1004 07.07.20 9
1005 10.07.20 3
1006 09.07.20 8
1007 10.07.20 4
1008 10.07.20 1
1009 10.07.20 4
1010 06.07.20 3

10 rows selected.

SQL>

Just in case you didn't know: line #9 mentions directory. It is an Oracle object which is created by user SYS and points to directory located on the database server. Looking at your sample code, as if you wanted to load data from your desktop. It won't work (unless your PC is also a database server, and you named that directory desktop).

Furthermore, SYS has to grant privileges to user which will be using the directory, e.g.

grant read, write on directory ext_dir to scott;

If the logfile is indeed located on the client, not on the server, `sqlldr` can be used with the same definition — , Jul 09 '20 at 11:20
Thanks a lot, it works! I set the desktop directory to find the files more easily. However you have been very useful, thank you very much. :) — heathcliff1927, Jul 09 '20 at 15:02

Use the number of characters as delimiter sql

1 Answers1