0

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!

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
  • I like where your mind is on this but it's a windows box that I cannot install these tools on (locked down). Was hoping for a way to handle with the control file. We did figure out the issue occurs when the user creates the CSV from excel. May be something we can configure there. Thank you! – flashsplat Nov 13 '18 at 19:00
  • 1
    @flashsplat You can do with powershell in windows.Check this link stackoverflow.com/questions/4248726/… – Sam Nov 13 '18 at 19:33
  • @Sam Ok, i can look into that a little. The given solution there is based on a set length. While they are the same for this example they generally vary. – flashsplat Nov 13 '18 at 20:11
  • I'm going to accept this answer as the problem is with the file and not sqlldr. Thank you for the response. – flashsplat Nov 14 '18 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.