8 Replies Latest reply: Feb 4, 2013 1:21 AM by toki79 RSS

    Sqlldr Import "Ragged" File

    toki79
      Hi,

      I have to import a flat file that has record of different length. Is this possible at all?

      I thought I would succeed with the TRAILING NULLCOLS Option, but I failed.
      LOAD DATA
      INFILE '/apps2/RDE/INCOMING/SAS_GB_MB.POS_D.CSV'  
      BADFILE '/apps2/RDE/ctl/bad/121130_P243_T_SAS_GB_MB_POS.bad'
      DISCARDFILE '/apps2/RDE/ctl/dis/121130_P243_T_SAS_GB_MB_POS.dis'
      INSERT
      INTO TABLE T_SAS_GB_MB_POS_121130
       WHEN RCRD_TP_NUM = '1'
      FIELDS TERMINATED BY ";"
      TRAILING NULLCOLS
      (
           RCRD_TP_NUM  ,
           MSG_TP_MT  ,
           TXN_TP_ID  ,
           FEE_CURR  ,
           TXN_KIND  ,
           TXN_DT  "TO_DATE(:TXN_DT, 'YYYYMMDD')",
           TXN_ID  ,
           BUCHUNGSKZ  ,
           FEE_SUM  ,
           FEE_COUNT  ,
           FEE_ID1  ,
           FEE1  ,
           FEE_ID2  ,
           FEE2  ,
           FEE_ID3  ,
           FEE3  ,
           FEE_ID4  ,
           FEE4  ,
           FEE_ID5  ,
           FEE5  ,
           FEE_ID6  ,
           FEE6  ,
           FEE_ID7  ,
           FEE7  ,
           FEE_ID8  ,
           FEE8  ,
           FEE_ID9  ,
           FEE9  ,
           FEE_ID10  ,
           FEE10  
      )
        • 1. Re: Sqlldr Import "Ragged" File
          SomeoneElse
          You haven't told us what isn't working or what errors you got.
          • 2. Re: Sqlldr Import "Ragged" File
            hm
            It failed!

            What exactly failed?

            Please provide the definition of T_SAS_GB_MB_POS_121130 and an example of SAS_GB_MB.POS_D.CSV to reproduce the error.
            Do you have a loader.log-file? what is the exact error?

            By the way: You posted in the SQL and PL/SQL-forum, but there is a special forum for loader-Problem.
            Probably you may get a better answer there (but I guess, that they also will ask for more information).


            Here is the sqlloader-forum:

            Export/Import/SQL Loader & External Tables

            Edited by: hm on 31.01.2013 05:53
            • 3. Re: Sqlldr Import "Ragged" File
              toki79
              I'm sorry.

              Now I first answer here to avoid a cross posting.

              My question was if such files can be loaded at all, because if no, I had a goodl argument to get the interface changed.

              Error:
              Record 2: Rejected - Error on table T_SAS_GB_MB_POS_121130, column FEE_ID3.
              ORA-01722: invalid number

              Here are 2 sample records of the flatfile, with different length:

              1;103;0;EUR;7;20130128;20130128/1101;0;75.00;2;1;72.50;6;2.50
              1;103;0;EUR;23;20130129;20130129/0084;0;8.00;1;1;8.00

              BR
              Tobias

              Edited by: toki79 on Jan 31, 2013 2:04 PM
              • 4. Re: Sqlldr Import "Ragged" File
                Solomon Yakobson
                Post table T_SAS_GB_MB_POS_121130 structure.

                SY.
                • 5. Re: Sqlldr Import "Ragged" File
                  Solomon Yakobson
                  Most likely column TXN_ID in table T_SAS_GB_MB_POS_121130is declared as NUMBER while load file field TXN_ID has value 20130128/1101.

                  SY.
                  • 6. Re: Sqlldr Import "Ragged" File
                    toki79
                    no
                    TABLE T_SAS_GB_MB_POS_121130
                     Name                                      Null?    Type                        
                     ----------------------------------------- -------- ----------------------------
                     RCRD_TP_NUM                               NOT NULL NUMBER(1,)                  
                     MSG_TP_MT                                          NUMBER(3,)                  
                     TXN_TP_ID                                          NUMBER(3,)                  
                     FEE_CURR                                           VARCHAR2(3)                 
                     TXN_KIND                                           NUMBER(10,)                 
                     TXN_DT                                             DATE                        
                     TXN_ID                                             VARCHAR2(16)                
                     BUCHUNGSKZ                                         NUMBER(1,)                  
                     FEE_SUM                                            NUMBER                      
                     FEE_COUNT                                          NUMBER                      
                     FEE_ID1                                            NUMBER                      
                     FEE1                                               NUMBER(12,2)                
                     FEE_ID2                                            NUMBER                      
                     FEE2                                               NUMBER(12,2)                
                     FEE_ID3                                            NUMBER                      
                     FEE3                                               NUMBER(12,2)                
                     FEE_ID4                                            NUMBER                      
                     FEE4                                               NUMBER(12,2)                
                     FEE_ID5                                            NUMBER                      
                     FEE5                                               NUMBER(12,2)                
                     FEE_ID6                                            NUMBER                      
                     FEE6                                               NUMBER(12,2)                
                     FEE_ID7                                            NUMBER                      
                     FEE7                                               NUMBER(12,2)                
                     FEE_ID8                                            NUMBER                      
                     FEE8                                               NUMBER(12,2)                
                     FEE_ID9                                            NUMBER                      
                     FEE9                                               NUMBER(12,2)                
                     FEE_ID10                                           NUMBER                      
                     FEE10                                              NUMBER(12,2)
                    • 7. Re: Sqlldr Import "Ragged" File
                      hm
                      As I can see in your profile you come from germany.

                      I guess your problem comes from your german nls-settings.

                      There are numbers like 75.00 in your data-file.

                      In germany the decimal separator ',' is used.

                      I guess your control file would work, when you change the decimal separator in all numbers from '.' to ',' (eg. 75.00 -> 75,00)

                      If you don't want that you could

                      - change the nls-settings at your client
                      or
                      - change your loader control-file like this:
                      LOAD DATA
                      INFILE '/apps2/RDE/INCOMING/SAS_GB_MB.POS_D.CSV'  
                      BADFILE '/apps2/RDE/ctl/bad/121130_P243_T_SAS_GB_MB_POS.bad'
                      DISCARDFILE '/apps2/RDE/ctl/dis/121130_P243_T_SAS_GB_MB_POS.dis'
                      INSERT
                      INTO TABLE T_SAS_GB_MB_POS_121130
                       WHEN RCRD_TP_NUM = '1'
                      FIELDS TERMINATED BY ";"
                      TRAILING NULLCOLS
                      (
                           RCRD_TP_NUM  ,
                           MSG_TP_MT  ,
                           TXN_TP_ID  ,
                           FEE_CURR  ,
                           TXN_KIND  ,
                           TXN_DT  "TO_DATE(:TXN_DT, 'YYYYMMDD')",
                           TXN_ID  ,
                           BUCHUNGSKZ  ,
                           FEE_SUM "to_number(:FEE_SUM,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE_COUNT  ,
                           FEE_ID1  "to_number(:FEE_ID1,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE1  "to_number(:FEE1,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE_ID2  "to_number(:FEE_ID2,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE2  "to_number(:FEE2,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE_ID3  "to_number(:FEE_ID3,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE3  "to_number(:FEE3,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE_ID4  "to_number(:FEE_ID4,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE4  "to_number(:FEE4,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE_ID5  "to_number(:FEE_ID5,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE5  "to_number(:FEE5,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE_ID6  "to_number(:FEE_ID6,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE6  "to_number(:FEE6,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE_ID7  "to_number(:FEE_ID7,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE7  "to_number(:FEE7,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE_ID8  "to_number(:FEE_ID8,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE8  "to_number(:FEE8,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE_ID9  "to_number(:FEE_ID9,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE9  "to_number(:FEE9,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE_ID10  "to_number(:FEE_ID10,'9999999999999990D00','nls_numeric_characters='',.''')",
                           FEE10 "to_number(:FEE10,'9999999999999990D00','nls_numeric_characters='',.''')" 
                      )
                      The first parameter of nls_numeric_characters is the group-separator, the second is the decimal-separator. So you overwrite the german setting.
                      • 8. Re: Sqlldr Import "Ragged" File
                        toki79
                        Hi,

                        thanks a lot for your suggestions.
                        Meanwhile I found out that there was indeed a mistake in converting the numbers and there was also an issue with the linefeeds.
                        So, records of different length are no problem at all!

                        BR

                        Tobias