1 2 Previous Next 24 Replies Latest reply: Jun 23, 2014 7:55 PM by swesley_perth RSS

    CSV Upload of more than 50 columns in APEX

    Doolius

      I'm needing a way to upload a CSV file that has more than 50 columns.

       

      Everything that I have been able to find have only allowed 50 columns because they are using collections I think.

       

      I have created a table that I want to upload the CSV data into instead of a collection thinking that will allow me to exceed the 50 column limit.

      The table and file will have the exact same structure.

       

      Can anyone help with this or point me in the right direction?

       

      Thanks

      Steven

       

      APEX: 4.0.2

      THEME: SAND

      SERVER: Oracle-Application-Server-10g/10.1.3.1.0 Oracle-HTTP-Server

        • 1. Re: CSV Upload of more than 50 columns in APEX
          TexasApexDeveloper

          There are different routines to do this:

            1) Using a file upload to a blob/clob and then cutting the file up into the required pieces: http://avdeo.com/2008/05/21/uploading-excel-sheet-using-oracle-application-express-apex/

            2) Using an external table with your database, thus you can have a CSV file and query it like a table... External tables in Oracle 

                Oracle external tables – A few examples | Oracle Apps Notes

           

           

          Thank you,

           

          Tony Miller
          LuvMuffin Software
          Ruckersville, VA

          • 2. Re: CSV Upload of more than 50 columns in APEX
            Doolius

            Thanks for the reply.

            I've actually tried to implement the first URL in my application, but I get the error:

            ORA-01006: bind variable does not exist

             

            And haven't figured out why that is coming up just yet.

            Here is the code if someone wants to take a look at it:

            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_sr_no number := 1;
             BEGIN
             delete from TEMP_UPDATE;
             -- Read data from wwv_flow_files</span> 
            select
             blob_content
             into v_blob_data
             from wwv_flow_files
             where name like '%Book2%' or name like '%book2'; 
             --last_updated = (select max(last_updated) from wwv_flow_files where UPDATED_BY = :APP_USER)
             --and id = (select max(id) from wwv_flow_files where updated_by = :APP_USER);
             v_blob_len := dbms_lob.getlength(v_blob_data);
             v_position := 1;
             -- Read and convert binary to char</span> 
            WHILE ( v_position <= v_blob_len ) LOOP v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position);
             v_char :=  chr(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 </span> 
            IF v_char = CHR(10) THEN 
            -- Convert comma to : to use wwv_flow_utilities </span> 
            v_line := REPLACE (v_line, ',', ':');
            -- Convert each column separated by : into array of data </span> 
            v_data_array := wwv_flow_utilities.string_to_table (v_line);
             -- Insert data into target table </span> 
             EXECUTE IMMEDIATE 'insert into TEMP_UPLOAD (ONE,TWO,THREE,FOUR,FIVE) values (:1,:2,:3,:4,:5)'
             USING v_sr_no, v_data_array(1), v_data_array(2), v_data_array(3), v_data_array(4), v_data_array(5); 
             -- Clear out 
             v_line := NULL; v_sr_no := v_sr_no + 1; END IF; END LOOP; END;
            

            Thanks for the other URLs.

             

            Steven

            • 3. Re: CSV Upload of more than 50 columns in APEX
              ascheffer

              In line 36 you have 5 bind variables.

              In line 37 you are "using" 6 values.

              • 4. Re: CSV Upload of more than 50 columns in APEX
                Doolius

                Ahh duh! Thank you!

                 

                That fixed that issue, but now I'm getting an error that the table or view doesn't exist.

                Only thing I really changed was where I added TEMP_UPDATE and also the INSERT statement.

                I'll have to look into that more tomorrow.

                 

                Thanks for the help so far!

                 

                Steven

                • 5. Re: CSV Upload of more than 50 columns in APEX
                  Doolius

                  I fixed the other error and the spreadsheet gets uploaded to my table.

                  However, it gets loaded incorrectly.

                  Spreadsheet

                  ONETWOTHREEFOURFIVE
                  dogyellowskygrasscat
                  pigblueclouddirtlion
                  llamaredwindwaterpanther

                   

                  Table

                  ONETWOTHREEFOURFIVE
                  ONETWOTHREEFOURFIVEdog
                  ONETWOTHREEFOURFIVEdog
                  ONETWOTHREEFOURFIVEdog

                   

                  Is there something wrong with the LOOP or the way the values are put into the arrays?

                   

                  Working Code:

                  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_sr_no number := 1;
                  BEGIN
                  delete from TEMP_UPDATE;
                  -- Read data from wwv_flow_files</span>
                  select
                  blob_content
                  into v_blob_data
                  from wwv_flow_files
                  where name like '%Book2%' or name like '%book2';
                  --last_updated = (select max(last_updated) from wwv_flow_files where UPDATED_BY = :APP_USER)
                  --and id = (select max(id) from wwv_flow_files where updated_by = :APP_USER);
                  v_blob_len := dbms_lob.getlength(v_blob_data);
                  v_position := 1;
                  -- Read and convert binary to char</span>
                  WHILE ( v_position <= v_blob_len )
                  LOOP
                  v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position);
                  v_char :=  chr(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 </span>
                  IF v_char = CHR(10) THEN
                  -- Convert comma to : to use wwv_flow_utilities </span>
                  v_line := REPLACE (v_line, ',', ':');
                  -- Convert each column separated by : into array of data </span>
                  v_data_array := wwv_flow_utilities.string_to_table (v_line);
                  -- Insert data into target table </span>
                  EXECUTE IMMEDIATE 'insert into TEMP_UPDATE (PCFN,TCN,BOOKING_NUMBER,CUSTOMER,CARRIER_SCAC) values (:1,:2,:3,:4,:5)'
                  USING v_data_array(1), v_data_array(2), v_data_array(3), v_data_array(4), v_data_array(5);
                  -- Clear out
                  --v_line := NULL; v_sr_no := v_sr_no + 1;
                  END IF;
                  END LOOP;
                  END;
                  

                   

                  Steven

                  • 6. Re: CSV Upload of more than 50 columns in APEX
                    ascheffer

                    add the "Clear out" again, at least the v_line:= null;

                    • 7. Re: CSV Upload of more than 50 columns in APEX
                      Doolius

                      Ahh thank you! Works perfectly now.

                      I think the only other question that I have right now is that I added another column to my table called USERNAME.

                      I want to be able to transfer the username of the user who uploaded the csv file (aka the CREATED_BY column of the wwv_flow_files table)

                      Unless the username, but instead will equal the 'apex_public_user' name. (not entirely sure how the wwv_flow_files table works)

                       

                      I thought that adding:

                       EXECUTE IMMEDIATE 'insert into TEMP_UPDATE (PCFN,TCN,BOOKING_NUMBER,CUSTOMER,CARRIER_SCAC, USERNAME)
                      values (:1,:2,:3,:4,:5,:6)'
                       USING v_data_array(1), v_data_array(2), v_data_array(3), v_data_array(4), v_data_array(5),CREATED_BY;
                      

                      to the insert statement would do it, but it wouldn't accept it.

                       

                      Then I added a variable:

                      v_sr_user VARCHAR(20) := :APP_USER
                      

                      and changed the code to:

                       EXECUTE IMMEDIATE 'insert into TEMP_UPDATE (PCFN,TCN,BOOKING_NUMBER,CUSTOMER,CARRIER_SCAC,USERNAME)
                      values (:1,:2,:3,:4,:5,:6)'
                      USING v_data_array(1), v_data_array(2), v_data_array(3), v_data_array(4), v_data_array(5), V_SR_USER;
                      

                      It set the value of USERNAME to 'nobody'.

                       

                      How can I track what records were updated by what user when the data is extracted from the CSV file so I can have a report to show the user of only the records they uploaded?

                       

                      Thanks Steven

                      • 8. Re: CSV Upload of more than 50 columns in APEX
                        Doolius

                        Scratch that.

                        I made a new column on the report where the user downloads the spreadsheet to track the USERNAME.

                        Final Code for others:

                        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_sr_no number := 1;  
                        BEGIN  
                        delete from TEMP_UPDATE;  
                        -- Read data from wwv_flow_files</span>  
                        select  
                        blob_content  
                        into v_blob_data  
                        from wwv_flow_files  
                        where name like '%Book2%' or name like '%book2';  
                        --last_updated = (select max(last_updated) from wwv_flow_files where UPDATED_BY = :APP_USER)  
                        --and id = (select max(id) from wwv_flow_files where updated_by = :APP_USER);  
                        v_blob_len := dbms_lob.getlength(v_blob_data);  
                        v_position := 1;  
                        -- Read and convert binary to char</span>  
                        WHILE ( v_position <= v_blob_len )  
                        LOOP  
                        v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position);  
                        v_char :=  chr(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 </span>  
                        IF v_char = CHR(10) THEN  
                        -- Convert comma to : to use wwv_flow_utilities </span>  
                        v_line := REPLACE (v_line, ',', ':');  
                        -- Convert each column separated by : into array of data </span>  
                        v_data_array := wwv_flow_utilities.string_to_table (v_line);  
                        -- Insert data into target table </span>  
                        EXECUTE IMMEDIATE 'insert into TEMP_UPDATE (PCFN,TCN,BOOKING_NUMBER,CUSTOMER,CARRIER_SCAC,USERNAME) values (:1,:2,:3,:4,:5,:6)'  
                        USING v_data_array(1), v_data_array(2), v_data_array(3), v_data_array(4), v_data_array(5), v_data_array(6);  
                        -- Clear out  
                        v_line := NULL; v_sr_no := v_sr_no + 1;  
                        END IF;  
                        END LOOP;
                        DELETE FROM WWV_FLOW_FILES WHERE NAME LIKE '%Book2%'; 
                        DELETE FROM TEMP_UPDATE WHERE PCFN LIKE 'P%'; 
                        END;
                        

                        I added in code at the end to remove the file from the WWV_FLOW_FILES table after the data is extracted.

                        And also added in code to remove the line with the column headers in it because I wasn't sure how to increment in order to not take out the column headers.

                        If anyone knows how please update the code accordingly.

                         

                        Thanks

                        Steven

                        • 9. Re: CSV Upload of more than 50 columns in APEX
                          Mike Kutz

                          External Tables would be the fastest.

                           

                          But, in order for them to work in a multi-user environment like APEX, you'd have to

                          • copy the file to the server (new file name)
                          • grab a lock
                          • ALTER TABLE -- without losing the lock
                          • parse, bind, and execute the desired SQL
                          • remove file from server/wwv_flow_files (as desired)
                          • release lock

                           

                          I've got a Process Plugin that does the above that seems to be ready.

                          It could probably use some code-review before I post it on apex-plugins.

                          (Let me know if someone beat me to it.)

                           

                          MK

                          • 10. Re: CSV Upload of more than 50 columns in APEX
                            Doolius

                            I wouldn't mind trying that process out.

                             

                            What I was going to try and do is (because I don't know what the file name of the file the user uploaded will be ) is every couple of hours, have a process that removes records in the wwv_flow_files table that have a USERNAME of apex_public_user and a created_on date of 10 minutes ago from the time it runs.

                            I figured that that would take care of emptying out the wwv_flow_files table of junk from the users, but still leave records in there for users who are currently uploading files.

                             

                            I'm still looking for a way to skip the first row on upload.

                            My way of deleting rows in my final table that started with a column name worked fine until I tried to add in a date column.

                            So if anyone has a solution for that I would greatly appreciate it.

                             

                            Steven

                            • 11. Re: CSV Upload of more than 50 columns in APEX
                              ascheffer

                              It isn't that hard to skip the first in your CSV-parsing

                              And it isn't that hard too to add a ON-submit process after the file uploading, which can use the filename of the uploaded file.

                              • 12. Re: CSV Upload of more than 50 columns in APEX
                                Doolius

                                Any chance you could show me how to skip the first row?

                                In the comments it said to add code before the while loop, so I did but it's not working like expected.

                                Code below: (Added code has spaces before and after it)

                                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_sr_no number := 1;  
                                BEGIN  
                                delete from TEMP_UPDATE;  
                                -- Read data from wwv_flow_files</span>  
                                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 = :APP_USER)  
                                and id = (select max(id) from wwv_flow_files where updated_by = :APP_USER);  
                                v_blob_len := dbms_lob.getlength(v_blob_data);  
                                v_position := 1;  
                                -- Read and convert binary to char</span>
                                
                                v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position);  
                                v_char :=  chr(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 </span>  
                                IF v_char = CHR(10) THEN  
                                -- Convert comma to : to use wwv_flow_utilities </span>  
                                v_line := REPLACE (v_line, ',', ':');  
                                -- Convert each column separated by : into array of data </span>  
                                v_data_array := wwv_flow_utilities.string_to_table (v_line);
                                end if;
                                
                                WHILE ( v_position <= v_blob_len )  
                                LOOP  
                                v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position);  
                                v_char :=  chr(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 </span>  
                                IF v_char = CHR(10) THEN  
                                -- Convert comma to : to use wwv_flow_utilities </span>  
                                v_line := REPLACE (v_line, ',', ':');  
                                -- Convert each column separated by : into array of data </span>  
                                v_data_array := wwv_flow_utilities.string_to_table (v_line);  
                                -- Insert data into target table </span>  
                                EXECUTE IMMEDIATE 'insert into TEMP_UPDATE (PCFN,TCN,USERNAME,ORIGIN,BOOKING_NUMBER,CARRIER_SCAC,RDD) values (:1,:2,:3,:4,:5,:6,:7)'  
                                USING 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) ;  
                                -- Clear out  
                                v_line := NULL; v_sr_no := v_sr_no + 1;  
                                END IF;  
                                END LOOP;
                                DELETE FROM WWV_FLOW_FILES WHERE last_updated = (select max(last_updated) from wwv_flow_files where UPDATED_BY = :APP_USER)  
                                and id = (select max(id) from wwv_flow_files where updated_by = :APP_USER); 
                                --DELETE FROM TEMP_UPDATE WHERE PCFN LIKE 'P%'; 
                                --UPDATE TEMP_UPDATE SET USERNAME = REPLACE(USERNAME, ' ', '');
                                END;
                                

                                As far as the on submit process, I have a username passed into a variable, would it be possible to change the filename to whatever I wanted to when the user uploads it?

                                • 13. Re: CSV Upload of more than 50 columns in APEX
                                  ascheffer

                                  add a variable

                                  v_first_line_done boolean := false;

                                  add after line 43

                                  if v_first_line_done then

                                  add before line 53

                                  else

                                    v_first_line_done := true;

                                  end if;

                                   

                                  What's the point in changing the filename before you gonna delete it?

                                  Did you know that you can get the name of the uploaded file from your file browse item, something like:

                                  file_name := apex_util.get_session_state( 'P1_BROWSE' );

                                  • 14. Re: CSV Upload of more than 50 columns in APEX
                                    Doolius

                                    Thanks. I'll give that a try.

                                     

                                    And the reason I wanted to change the name of the file to match the user that uploaded it is because the code looks like it just deletes the most recently uploaded/updated file.

                                    I haven't been able to test the process yet to see what happens if multiple users upload files at the same time and/or a second after to see if it will still work correctly.

                                    1 2 Previous Next