10 Replies Latest reply on May 21, 2011 6:42 AM by lily

    Upload data from csv to table in Apex

    593275
      Hi All,

      I want to upload csv file data to oracle table dynamically for more than 50 columns .
      Given below url is working well for 50 clomns. But I have requirement upto 100 columns.Please go through this url.

      http://htmldb.oracle.com/pls/otn/f?p=38131:1:3738150661527672



      Regards
      Danalaxmi
        • 1. Re: Upload data from csv to table in Apex
          Mike Mac
          Hi Danalaxmi

          I have also used this method for a while. If you look carefully in the code, you will find the 50 column limit.

          In the "parse_file" procedure I changed the 50 column limit to 80.....
          if (l_num_columns > 80) then
          raise_application_error(-20000,'Max. of 80 columns allowed, id='||p_file_name);
          end if;

          This works fine, but I have now changed my approach and written a procedure to put the uploaded file onto the server's filesystem and I use an external table to access the data - better for my application and also handles uploading images to the server without clogging up the database and using up the precious 4Gb of data allowed in XE!

          It took me absolutely ages to perfect, so I thought I'd save you (and others) all the heartache!! ;)

          1. In the page where you're uploading include an anonymous PL/SQL block :-
          -----------------------------------------------
          IF ( :P13_FILE_NAME is not null ) THEN
          BEGIN
          SAVE_IMG_TO_FILE(:P13_FILE_NAME,'FILESYSTEM.CSV');
          DELETE from APEX_APPLICATION_FILES WHERE name = :P13_FILE_NAME;
          END;
          END IF;
          --------------------------------------------
          Notice I clean up - deleting the uploaded file after its on the server filesystem..

          2. Create a database directory pointing to the filesystem diretory where you will store the file.
          3. Now for the tricky bit - write a procedure to take the uploaded image and put it onto the filesystem.
          -----------------------------------------
          CREATE OR REPLACE procedure SAVE_IMG_TO_FILE( p_image_name in varchar,
          p_image_save_name in varchar)
          as
          l_file UTL_FILE.FILE_TYPE;
          l_buffer RAW(32767);
          l_amount BINARY_INTEGER := 32767;
          l_read_amount BINARY_INTEGER :=0;
          l_pos INTEGER := 1;
          l_filename varchar2(100);
          l_mime_type varchar2(100);
          l_blob_len INTEGER;
          l_blob BLOB;
          BEGIN
          select ai.blob_content
          INTO l_blob
          from FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ ai
          where ai.NAME = p_image_name;

          l_blob_len := DBMS_LOB.getlength(l_blob);

          -- Open the destination file.
          l_file := UTL_FILE.fopen('NAME_OF_DIRECTORY',p_image_save_name,'wb', 32767);

          -- Read chunks of the BLOB and write them to the file
          -- until complete.
          WHILE l_pos < l_blob_len LOOP
          l_read_amount := least(l_amount,l_blob_len - l_pos);
          DBMS_LOB.read(l_blob, l_read_amount, l_pos, l_buffer);
          UTL_FILE.put_raw(l_file, l_buffer, TRUE);
          l_pos := least(l_pos + l_amount, l_blob_len);
          END LOOP;

          -- Close the file.
          sys.UTL_FILE.fclose(l_file);

          EXCEPTION
          WHEN OTHERS THEN
          -- Close the file if something goes wrong.
          IF UTL_FILE.is_open(l_file) THEN
          sys.UTL_FILE.fclose(l_file);
          END IF;
          --RAISE;
          END SAVE_IMG_TO_FILE;
          /
          ------------------------------------------------------------
          4. Add a "Save file to Server" button that calls the Anonymous PL/SQL block

          5. Build a database External Table that references the 'FILESYSTEM.CSV' in the directory
          6. Write whatever SQL code you want to reference the CSV file as a table....

          This works much better for my application as I also use this to save images to the filesystem.

          It is approriate if you have a fixed structure of the CSV file and you want to reference the data for processing - and in this case tou don't need the first two lines of the CSV to have the column names and the column definitions.

          Simple but very effective.

          Enjoy!

          Mike
          • 2. Re: Upload data from csv to table in Apex
            614439
            Hi Mike

            Mmmm, this is interesting. I am using the parse_file procedure to upload to a table with 49 columns currently. I had wanted to cope for potential expansion by increasing the 50 column limit but as I had not used collections before I read the documentation. This suggested that 50 was the limit for collections. I guessed that I was ok with 50 given that it would probably be rare that a user would want to provide data for all 49 columns in an upload and so didn't test it any further than 50.

            I am interested to know if you actually implemented the 80 column version and had it working so that I may build this into my Developer's documentation as a potential future solution.

            Regards

            Karen
            • 3. Re: Upload data from csv to table in Apex
              593275
              Hi Mike,


              As You menitoned that You have tried for 80 columns..and It worked well also.. I too did the same in parse file procedure instead of 50 I have mentioned now 80 but still this code is not working for 80 columns.. It is restricting to 50 columns only and I am getting the same error .... So please can you assist me in this case....

              I dont have any fix structure for the csv file so i cannot use External table concept.... So please suggest me in the above code itself.



              Thanks & Regards
              Danalaxmi
              • 4. Re: Upload data from csv to table in Apex
                593275
                HI Mike,


                Please give me solutions is we have 100 columns using collection methis.. not by external tables concept.


                Thanks in Advance

                Danalaxmi
                • 5. Re: Upload data from csv to table in Apex
                  614439
                  Hi Danalaxmi

                  Seems that the 50 limit is there then for the collections. So unless you can split your uploaded file in some way to accommodate that I'm not sure how else you would do it using collections. I guess one way would be to split the file so that you can create more than one collection and store the primary key in each collection, do an insert to the table on the first collection and an update on the second and subsequent collections. Otherwise, parse all of the file into a PL/SQL table and insert to the destination table directly.

                  Hope this makes sense.

                  Karen
                  • 6. Re: Upload data from csv to table in Apex
                    Santhosh Tirunahari-Oracle
                    All :

                    I used 2 collection to solve the 100 col's problem. I made little changes in the code.....


                    csv_to_array(l_records(i),l_record);
                    l_seq_id := htmldb_collection.add_member(p_collection_name,'dummy');
                    FOR i IN 1..49
                    LOOP
                    htmldb_collection.update_member_attribute(
                    p_collection_name=> p_collection_name,
                    p_seq => l_seq_id,
                    p_attr_number => i,
                    p_attr_value => l_record(i)
                    );
                    END LOOP;


                    csv_to_array(l_records(i),l_record);
                    l_seq_id1 := htmldb_collection.add_member(p_collection_name1,'dummy1');
                    FOR i IN 50..l_record.count
                    LOOP
                    htmldb_collection.update_member_attribute(
                    p_collection_name=> p_collection_name1,
                    p_seq => l_seq_id1,
                    p_attr_number => i,
                    p_attr_value => l_record(i)
                    );
                    END LOOP;

                    But still i am getting the same error the collection will not allow more than 50 columns.
                    i tried to access 49 col's in 1st collection. still i am facing the same error.

                    could you guide me,
                    how to change the above code
                    or how use second collection in the code.

                    If col = <100 and col > 50
                    My plan is to store 49 col's in one table remain col in another table then merging
                    2 tables in one table.

                    Looking forward for quick reply.......

                    Santhosh Kumar T

                    null
                    • 7. Re: Upload data from csv to table in Apex
                      636218
                      I am trying to install the files from your link, but the first one tells me that it cannot because they were exported from a different workspace.

                      I am new to Apex and would greatly appreciate any assistance.

                      Thanks.

                      ~CJ
                      • 8. Re: Upload data from csv to table in Apex
                        dmcghan
                        DarthDaddy,

                        I was just looking over this thread. Are you having problems with the 50 column limit? ARe you loading the files yourself or are the users of an application? How often will the files be loaded?

                        Regards,
                        Dan
                        • 9. Re: Upload data from csv to table in Apex
                          ApexNewbie
                          Has any found a resolution to this issue? I am facing the same issue.
                          Does Apex 4.0 offer anything that would help with this limitation?
                          We have an application that needs to upload from a csv file as well.
                          • 10. Re: Upload data from csv to table in Apex
                            lily
                            the url http://htmldb.oracle.com/pls/otn/f?p=38131:1:3738150661527672

                            can not be open


                            Unexpected error, unable to find item name at application or page level.
                                 Error     ERR-1002 Unable to find item ID for item "P0_AFTER_SUBMIT" in application "38131".
                            OK