This discussion is archived
13 Replies Latest reply: Nov 1, 2012 11:18 AM by rp0428 RSS

CSV from external table

Rinne Newbie
Currently Being Moderated
When I load data from an external table to a regular table, it seems to append something to the end of the value.

Here is the DDL:
CREATE TABLE EX_table1
(
col_CD VARCHAR2(50 BYTE)
, COl_VAL VARCHAR2(50 BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DIR_EX
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE SKIP 1
BADFILE DIR_CONFIG_BAD_FILE:'BAD.log'
LOGFILE DIR_CONFIG_LOG_FILE:'LOG.log'
FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL
)
LOCATION
(
DIR_CONFIG: 'table1.csv'
)
)
REJECT LIMIT UNLIMITED;

Here are some rows of table1.csv:
col_CD,col_val
GRR,
GRR,3
GRR,4
GRR,5
GRR,63

When I run: SELECT LENGTH(col_VAL), col_VAL FROM ex_table1;
I get this for the rows above:
1,
2, 3
2, 4
2, 5
3, 63

How can I get rid of the extra character that's being appended on col_VAL? I'd like to treat missing value as null. Please help.
  • 1. Re: CSV from external table
    ranit B Expert
    Currently Being Moderated
    try ... not tested
    SELECT LENGTH(col_VAL), TRIM(col_VAL) FROM ex_table1;
    I guess, some whitespace is already present in the CSV (whitespace can't be seen easily).
    TRIM will remove them.... Please let me know.

    Hope that Helps.
    Ranit B.
  • 2. Re: CSV from external table
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    That could be a control character, maybe due to different ways of encoding newlines.
    To find out exactly what character that is:
    SELECT  DUMP (col_val)  AS d
    FROM    ex_table1;
    That will show you the ASCII code for each character, including the extra one at the end.
    Say it shows 13. You can remove that character from the end of the string using
    RTRIM (col_val, CHR (13))
  • 3. Re: CSV from external table
    ranit B Expert
    Currently Being Moderated
    Frank Kulash wrote:
    Hi,

    That could be a control character, maybe due to different ways of encoding newlines.
    To find out exactly what character that is:
    SELECT  DUMP (col_val)  AS d
    FROM    ex_table1;
    That will show you the ASCII code for each character, including the extra one at the end.
    Say it shows 13. You can remove that character from the end of the string using
    RTRIM (col_val, CHR (13))
    Frank,

    Nice 1, but is anything other than whitespace possible over there?
    CHR(13) : Newline is it sensible in CSV values ??
  • 4. Re: CSV from external table
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    ranit B wrote:
    ... is anything other than whitespace possible over there?
    Yes, anything is possible; that's why OP needs to use DUMP. Until OP posts those results, we can't tell if the extra character is whitespace or not.
    CHR(13) : Newline is it sensible in CSV values ??
    Sorry, I don't understand this question.
  • 5. Re: CSV from external table
    ranit B Expert
    Currently Being Moderated
    Frank Kulash wrote:
    CHR(13) : Newline is it sensible in CSV values ??
    Sorry, I don't understand this question.
    I meant, if CSV values will have Newline(s), the file structure may get disrupted right. Wrong values may get inesrted in columns.

    Hope i explained properly.
    -- Thanks Frank.
  • 6. Re: CSV from external table
    Rinne Newbie
    Currently Being Moderated
    Awesome. I get values like:
    Typ=1 Len=1: 13
    Typ=1 Len=3: 49,48,13

    What is the 13? Why did it get in there? Can I get rid of it in the DDL of the external table or do I have to specify it every time I do DML?
  • 7. Re: CSV from external table
    ranit B Expert
    Currently Being Moderated
    *13* stands for ASCII value of 'New Line' character

    Try ...
    select CHR(13) from dual;
    But nothing will be visible.

    Opposite to CHR() is ASCII(), which gives the ASCII value of the input character .


    Edited by: ranit B on Nov 1, 2012 4:11 AM
  • 8. Re: CSV from external table
    Rinne Newbie
    Currently Being Moderated
    That means that I probably can't specify in the DDL of the external table so that when I query it I won't see the control character correct? In other words, whenever I query the external table I have to specify the trim at that time?
  • 9. Re: CSV from external table
    rp0428 Guru
    Currently Being Moderated
    >
    13 stands for ASCII value of 'New Line' character
    >
    No - actually that is a carriage return. 0x0A (10 decimal) is the line feed.
  • 10. Re: CSV from external table
    rp0428 Guru
    Currently Being Moderated
    >
    What is the 13?
    >
    That is 0x0D - a carriage return character
    >
    Why did it get in there?
    >
    Because it is in your data.
    >
    Can I get rid of it in the DDL of the external table or do I have to specify it every time I do DML?
    >
    One possible cause is that the file has CRLF characters but you are loading it on a nix system. On .nix NEWLINE is just LF so the CR will get loaded unless you add RTRIM or LRTRIM to the spec.

    See the Database Utilities doc
    http://docs.oracle.com/cd/B28359_01/server.111/b28319/et_params.htm
    >
    If DELIMITED BY NEWLINE is specified, then the actual value used is platform-specific. On UNIX platforms, NEWLINE is assumed to be "\n". On Windows NT, NEWLINE is assumed to be "\r\n".
  • 11. Re: CSV from external table
    ranit B Expert
    Currently Being Moderated
    Actually you can specify TRIM in case of creating External tables.

    Please Refer -- external table skip rows and trim

    Also from docs -- http://docs.oracle.com/cd/B10500_01/server.920/a96652/ch12.htm
    >
    The following is an example of an external table that uses optional enclosure delimiters. Note that LRTRIM is used to trim leading and trailing blanks from fields. The example is followed by a sample of the datafile that can be used to load it.

    CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
    ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
    ACCESS PARAMETERS (FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '(' and ')'
    LRTRIM)
    LOCATION ('foo.dat'));

    Alvin , Tolliver , 1976
    (Kenneth), (Baer), (1963)
    ( Mary ), Dube , (1973)

    trim_spec

    The trim_spec clause is used to specify that spaces should be trimmed from the beginning of a text field, the end of a text field, or both. Spaces include blanks and other nonprinting characters such as tabs, line feeds, and carriage returns.
    >

    Hope this Helps.
    Ranit B.

    Edited by: ranit B on Nov 1, 2012 4:35 AM
    -- from docs
  • 12. Re: CSV from external table
    Rinne Newbie
    Currently Being Moderated
    I added the two parts in bold. Just by adding \r\n it took care of the problem in one table. But I have the same problem on the last column (T_NAME) on the table below. When I do a dump(T_NAME), I get 13 at the end. What am I missing?

    CREATE TABLE table1
    (
    T_CD VARCHAR2(50 BYTE)
    , T_DESC VARCHAR2(256 BYTE)
    , T_NAME VARCHAR2(100 BYTE)
    )
    ORGANIZATION EXTERNAL
    (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY DIR_INIT
    ACCESS PARAMETERS
    (
    RECORDS DELIMITED BY *'\r\n'* SKIP 1
    BADFILE DIR_INIT_BAD_FILE:'init_BAD.log'
    LOGFILE DIR_INIT_LOG_FILE:'init_LOG.log'
    FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL
    LRTRIM)
    LOCATION
    (
    DIR_INIT'VS_ENGINE_COMPONENT_CODES.csv'
    )
    )
    REJECT LIMIT UNLIMITED;
  • 13. Re: CSV from external table
    rp0428 Guru
    Currently Being Moderated
    >
    I added the two parts in bold. Just by adding \r\n it took care of the problem in one table. But I have the same problem on the last column (T_NAME) on the table below. When I do a dump(T_NAME), I get 13 at the end. What am I missing?
    >
    Don't know.

    What does an examination of that file show?

    Make a copy of the file that just includes one data record and see if it gives the 13 at the end.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points