sqlldr not recognizing new line/row

Question

Our organization switched to a new tool and now our text files are generated with only a trailing CR instead of a CR & LF. Now sqlldr thinks all of the data in the file is 1 row. How can I fix this?

Here is our typical setup:

    ------------------------------
    -- CREATE THE CONTROL FILE:
    ------------------------------
    SET HEADING OFF PAGES 0 FEEDBACK OFF VERIFY OFF TERM OFF;
    SPOOL &&client-ws.ctl

    SELECT  'load data ' || CHR (10) || 
            'infile * ' || CHR (10) || 
            'TRUNCATE ' || CHR (10) || 
            'into table TMP_ID_LIST' || CHR (10) || 
            'FIELDS TERMINATED BY ''    '' ' || CHR (10) || 
            '( ' || CHR (10) || 
            'ID )' FROM DUAL;

    SPOOL OFF;

    ------------------------------
    -- LOAD FROM FILE INTO TABLE:
    ------------------------------
    HOST sqlldr control=&&client-ws.ctl  data=&&input_file log=&&client-ld.log userid=&&userid/&&password@&&db silent=header direct=y

In the paricular case i'm trying to solve, it is only 1 column. Example data:

    b55369fd-3919-4baf-808c-ceacfe5afaac
    b8c54bf7-7fba-4569-bd75-54f7fc9d5210
    75c48437-8acf-4c34-ab76-a0d5c282d81f
    d62272fe-5132-42ec-aea8-f084151168d4

Halp pweez :D - Thanks!

Answer 1

The obvious solution is to have your tool generate the output in the proper format.

If that is not possible, just use dos2unix or unix2dos on the file before processing, depending on the direction you need. Both utilites are provided as part of the dos2unix package that can be installed from the default Linux repositories, or downloaded seperately.

Other alternatives involve perl, sed or tr: https://en.wikipedia.org/wiki/Unix2dos

unix2dos:

perl -i -p -e 's|[\r\n]+|\r\n|g' file

or

sed -i -e 's/\r*$/\r/' file

dos2unix:

tr -d '\r' < file > file2 # For ASCII and other files which do not contain multibyte characters (Not utf-8 safe).

or

perl -i -p -e 's/\r//g' file

or

sed -i -e 's/\r//g' file

Answer 2

This is old, but I wanted to post how we fixed this. Turned out, the users were saving the CSV from excel as Macintosh CSV. Yes. They are noobs. Once we flogged them for this behavior, we had no more issues.

Thanks!