13 Replies Latest reply: Nov 1, 2012 1:18 PM by rp0428 RSS

    CSV from external table

    Rinne
      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
          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
            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
              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
                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
                  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
                    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
                      *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
                        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
                          >
                          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
                            >
                            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
                              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
                                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
                                  >
                                  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.