3 Replies Latest reply on Jan 16, 2014 9:42 AM by ascheffer

    Ignore first row when loading CSV to APEX

    Cashy

      Hi Guys, I have used the method described here http://avdeo.com/2008/05/21/uploading-excel-sheet-using-oracle-application-express-apex/ to load a CSV file into my oracle table and it is working OK. Ideally, I would like to keep the column headers in the CSV file and ignore them when the file is uploaded.

       

      
      DECLARE 
        v_blob_data         BLOB; 
        v_blob_len          NUMBER; 
        v_position          NUMBER; 
        v_raw_chunk         RAW(10000); 
        v_char              CHAR(1); 
        c_chunk_len         number                  := 1; 
        v_line              VARCHAR2 (32767)        := NULL; 
        v_data_array        wwv_flow_global.vc_arr2; 
        v_rows              number; 
        v_seq               number := 1;
        v_batch             number; 
      
      
        BEGIN 
      
      
         SELECT max(import_batch_id) +1
         INTO v_batch
         FROM XXMEL_PVS_BSH_ORDER;
        
         --delete from XXMEL_PVS_BSH_ORDER;
            
         -- Read data from wwv_flow_files 
         select blob_content 
         into v_blob_data
         from wwv_flow_files
         where last_updated = (select max(last_updated) 
                               from wwv_flow_files 
                               where UPDATED_BY = :P2_CURRENT_USER);
             
      
      
         
         -- Read data from wwv_flow_files
         -- SELECT blob_content 
         --  INTO v_blob_data
         --  FROM wwv_flow_files WHERE FILENAME = 'Book1.csv';
          
                          
          v_blob_len := dbms_lob.getlength(v_blob_data); 
          v_position := 1;
        
           --SELECT XXMEL_PVS_BSH_ORDER_S.nextval
           --INTO v_seq
           --FROM dual;    
      
      
           -- Read and convert binary to char 
          WHILE ( v_position <= v_blob_len ) 
           LOOP
              v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position); 
              v_char :=  CHR(xxmel_hex_to_decimal(RAWTOHEX(v_raw_chunk))); 
              v_line := v_line || v_char; 
              v_position := v_position + c_chunk_len; 
              -- When a whole line is retrieved 
              
            IF :P2_IMPORT_TYPE = 'FORECAST'
              THEN
              
               IF v_char = CHR(10) 
                THEN 
                   --raise_application_error(-20001,'v_char:'||CHR(v_char));
                   -- Convert comma to : to use wwv_flow_utilities
                   v_line := REPLACE (v_line, ',', ':');
                   -- Convert each column separated by : into array of data 
                    v_data_array := wwv_flow_utilities.string_to_table (v_line); 
                   -- Insert data into target table 
               
                EXECUTE IMMEDIATE 'INSERT INTO XXMEL_PVS_BSH_ORDER 
                                  (IMPORT_ID
                                  ,IMPORT_BATCH_ID
                                  ,IMPORT_DATE
                                 , REQUIREMENT_PERIOD
                                 , PURCHASING_DOCUMENT
                                 , MATERIAL
                                 , DELIVERY_DATE
                                 , SCHEDULED_QTY
                                 , RESOURCE_NAME
                                 , ORIGINAL_DELIVERY_DATE
                                 , FORECAST_SET
                                 , LOADED_BY
                                 , IMPORT_TYPE
                                 --, CUSTOMER
                                 ) 
                                   VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)' 
                   USING
                   v_seq--:P2_IMPORT_ID
                   ,NVL(v_batch,1)
                   ,sysdate
                   ,v_data_array(1)
                   ,v_data_array(2)
                   ,v_data_array(3)
                   ,v_data_array(4)
                   ,v_data_array(5)
                   ,v_data_array(6)
                   ,v_data_array(7)
                   ,v_data_array(8)
                   ,:P2_CURRENT_USER
                   ,:P2_IMPORT_TYPE
                   --,:P2_CUSTOMER
                   ;
                  
                v_line := NULL;
                v_seq:= v_seq + 1;
                   
               END IF;
               
             ELSIF :P2_IMPORT_TYPE = '21 LITRE'
              THEN 
               
              --SELECT XXMEL_PVS_BSH_ORDER_S.nextval
              --INTO v_seq
              --FROM dual;
              
              
               IF v_char = CHR(10) 
                THEN 
                   --raise_application_error(-20001,'v_char:'||CHR(v_char));
                   -- Convert comma to : to use wwv_flow_utilities
                   v_line := REPLACE (v_line, ',', ':');
                   -- Convert each column separated by : into array of data 
                    v_data_array := wwv_flow_utilities.string_to_table (v_line); 
                   -- Insert data into target table 
               
                EXECUTE IMMEDIATE 'INSERT INTO XXMEL_PVS_BSH_ORDER 
                                  (IMPORT_ID
                                  ,IMPORT_BATCH_ID
                                  ,IMPORT_DATE
                                 , PO_NUMBER
                                 , MATERIAL
                                 , REQUIREMENT_DATE
                                 , SCHEDULED_QTY
                                 , EDI_ORDER_NO
                                 , CUSTOMER
                                 , SHIP_TO
                                 , SCHEDULED_SHIP_DATE
                                 , LOADED_BY
                                 , IMPORT_TYPE
                                 
                                 ) 
                                   VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12, :13)' 
                   USING
                   v_seq--:P2_IMPORT_ID
                   ,NVL(v_batch,1)
                   ,sysdate
                   ,v_data_array(1)
                   ,v_data_array(2)
                   ,v_data_array(3)
                   ,v_data_array(4)
                   ,v_data_array(5)
                   ,v_data_array(6)
                   ,NVL(v_data_array(7),'bshgb consignee germany')
                   ,v_data_array(8)
                   ,:P2_CURRENT_USER
                   ,:P2_IMPORT_TYPE
                   ;
                  
                 v_line := NULL;
                 v_seq:= v_seq + 1;
                 
               END IF;  
                 
            ELSIF :P2_IMPORT_TYPE = '36 LITRE'
              THEN 
               
               IF v_char = CHR(10) 
                THEN 
                   --raise_application_error(-20001,'v_char:'||CHR(v_char));
                   -- Convert comma to : to use wwv_flow_utilities
                   v_line := REPLACE (v_line, ',', ':');
                   -- Convert each column separated by : into array of data 
                    v_data_array := wwv_flow_utilities.string_to_table (v_line); 
                   -- Insert data into target table 
               
                EXECUTE IMMEDIATE 'INSERT INTO XXMEL_PVS_BSH_ORDER 
                                  (IMPORT_ID
                                  ,IMPORT_BATCH_ID
                                  ,IMPORT_DATE
                                 , PO_NUMBER
                                 , MATERIAL
                                 , REQUIREMENT_DATE
                                 , SCHEDULED_QTY
                                 , EDI_ORDER_NO
                                 , CUSTOMER
                                 , SHIP_TO
                                 , SCHEDULED_SHIP_DATE
                                 , LOADED_BY
                                 , IMPORT_TYPE
                                 ) 
                                   VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)'  
                  USING
                   v_seq--:P2_IMPORT_ID
                   ,NVL(v_batch,1)
                   ,sysdate
                   ,v_data_array(1)
                   ,v_data_array(2)
                   ,v_data_array(3)
                   ,v_data_array(4)
                   ,v_data_array(5)
                   ,v_data_array(6)
                   ,NVL(v_data_array(7),'bshgb consignee germany')
                   ,v_data_array(8)
                  ,:P2_CURRENT_USER
                  ,:P2_IMPORT_TYPE
                   ;
                  
                 v_line := NULL;
                 v_seq:= v_seq + 1; 
                 
                   
               END IF; 
      
      
            END IF;
               
           END LOOP;
            
        END;
      
      
      BEGIN
       xxmel_pvs_pkg.remove_chr;
      END;
      

       

      I have tried changing the start position in the above code but cant get it to work.

       

      Can somebody please advise what needs to be changed from the above code in order for it to start inserting from row 2?

       

      Thanks

      Chris