Paste Excel Files into Apex

Doolius

I've searched the forums here and found a few examples of how to paste excel data into a textarea then upload it to the database, but none that I have been able to edit for my personal use.

One example(code below) is from  Application Express: How to Create a Form to Upload Spreadsheet Data  that tells you how to create one from their code, but I haven't been able to edit the SQL to change it from their table/info to my table/info.

The example below works great with their tables, I am just unsure how to edit it. 

http://www.oracle.com/technetwork/developer-tools/apex/howto-create-upload-spreadsheet-for-087062.html

TableName: TestTable

Columns:

ID

Col1

Col2

Col3

Any and all help is greatly appreciated!

Steven

----------------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE process_upload
IS
/***********This is how I get the spreadsheet data into the stage_cars table*/

   PROCEDURE process_cars (p_upload IN VARCHAR2, o_partners OUT VARCHAR2);

/****The tokenize_generic function is used in the following way:
In the spreadsheet there are many drop downs I use for data consistency purposes.
However, in my application, the columns of the CARS table are actually numbers that are foreign keys
pointing back to the Lookup tables.  Therefore, I need some sort of way to convert the TEXT data to the
NUMERIC data that is in the final/production CARS table*/

   FUNCTION tokenize_generic (p_id IN NUMBER,stage_column IN VARCHAR2, stage_table in varchar2,p_table in varchar2,p_column in varchar2)
   RETURN VARChar2;
END process_upload;
/

CREATE OR REPLACE PACKAGE BODY process_upload
IS
/*******************
This is used for the following purpsose:
IN HTML DB, when you have a multi-select list, checkbox item, the values are stored in the same column
seperated by a ':'.  I may have three columns in the spreadsheet that all end up being stored in the same
database column.  So, this allows me to take three values and create a string of characters
seperated by a ':' which is the format i need to use the array functions in HTML DB.

Takes in an array and returns a string of values sep'd by  ':'
****/
   FUNCTION create_string_array (p_array wwv_flow_global.vc_arr2)
      RETURN VARCHAR2
   IS
      v_string   VARCHAR2 (32000) := NULL;
   BEGIN
   --For length of array
      FOR i IN 1 .. p_array.COUNT
      LOOP
         IF p_array ( i ) IS NOT NULL  ---user may have not filled in all values in spreadsheet
         THEN
              --ADD to string the next value in array with a :
            v_string := v_string || p_array ( i ) || ':';
         END IF;
      END LOOP;

      IF LENGTH (v_string) > 0
      THEN
   ---remove last : from string
         v_string := SUBSTR (v_string, 1, LENGTH (v_string) - 1);
      END IF;

      RETURN v_string;
   END create_string_array;

   PROCEDURE process_cars (p_upload IN VARCHAR2, o_partners OUT VARCHAR2)
   IS
      v_car_array   wwv_flow_global.vc_arr2;
      empty_array       wwv_flow_global.vc_arr2;
      v_array           wwv_flow_global.vc_arr2;
      v_upload          VARCHAR2 (32000)        := NULL;
      v_line            VARCHAR2 (32000)        := NULL;
      bad_values        EXCEPTION;
      v_colors       VARCHAR2 (100)          := NULL;
   BEGIN
/***Remove the : from the web address so we can turn string into colon delimited string to use the built in
string to table function     ********/
--SINCE ':' is the key delimiter character, I tried removing any instance where I thought it
--could end up in string...the URL being the main choice

      v_upload := REPLACE (p_upload, 'http://', 'httpQQQ//');
/******  Change the Tabs into :'s in the string
******************/
---CHR(09) is the tab key, replace with a ':'

      v_upload := REPLACE (v_upload, CHR (09), ':');
/**** PARSE OUT THE LINES**********************/

      WHILE v_upload IS NOT NULL
      LOOP
         v_colors := NULL;
         v_line := SUBSTR (v_upload, 1, INSTR (v_upload, CHR (10)) - 1);
/*SET THE ARRAY TO VALUES*/
         v_car_array := wwv_flow_utilities.string_to_table (v_line);

---IF for some reason we didn't get all 17 values we were expecting
         IF v_car_array.COUNT != 8
         THEN
            RAISE bad_values;
         ELSE
            v_array := empty_array;

   --THere could be three spreadsheet columns that map to one column in the database
/*****
In spreadsheet the order is this:
Column 1 - Name
Column 2 - Manufacturer
Column 3 - Color1
Column 4 - Color2
Column 5 - Color3
Column 6 - Trim
Column 7 - MPG
Column 8 - Website

so v_array will correspond to these numbers
**************/
            v_array (1) := v_car_array (3);
            v_array (2) := v_car_array (4);
            v_array (3) := v_car_array (5);
   ----combine them into one string
            v_colors := create_string_array (v_array);
----INSERT INTO THE STAGE_PARTNER TABLE
            BEGIN
               EXECUTE IMMEDIATE 'insert into stage_cars (id,name,manufacturer,colors,trim,mpg,website) values
(NULL,:1,:2,:3,:4,:5,:6)'
                  USING
                  v_car_array (1),
                  v_car_array (2),
                  v_colors,
                  v_car_array (6),
                  v_car_array (7),
                  REPLACE (v_car_array (8), 'httpQQQ//', 'http://')
                  ;
---IF THERE IS AN ERROR THEN PUT INTO ERROR TABLE TO VIEW LATER
            EXCEPTION
               WHEN OTHERS
               THEN
                  EXECUTE IMMEDIATE 'insert into stage_car_error(id,log,time,username) values (NULL,:1,:2,:3)'
                     USING v_line, SYSDATE, v ('APP_USER');
            END;

            v_upload := SUBSTR (v_upload, INSTR (v_upload, CHR (10)) + 1);
         END IF;
      END LOOP;
   END process_cars;

/**********TOKENIZE GENERIC CODE*/

/***************
p_id - id from the stage table
Stage_column - the column from stage table
stage_table - the stage table
p_table - the lookup table we will be using
P_column - the lookup table column the result will be taken from

The result of this would be taking the COLORS column which might have a value of :
'Blue:Red:White'
and turning it into:
'2:3:5'
which is the corresponding primary key values from the colors table.
****************/

FUNCTION tokenize_generic (p_id IN NUMBER,stage_column IN VARCHAR2, stage_table in varchar2,p_table in varchar2,p_column in varchar2)
return varchar2
is
v_array wwv_flow_global.vc_arr2;
v_return VARCHAR2 (100);
begin

/*Retrieve the row with the multi-value column you are turning into numbers*/

EXECUTE IMMEDIATE 'SELECT '||stage_column||'
        FROM '||stage_table||'
       WHERE id = :1' into v_return using p_id;

/***Turn the string into an array. Each : separated value will be in a seperate array slot*/

v_array:= wwv_flow_utilities.string_to_table (v_return);

FOR i in 1..v_array.count
LOOP

/*Query the Lookup table to retrieve primary key value*/

EXECUTE IMMEDIATE 'SELECT ID  FROM '||P_TABLE||' WHERE '||P_COLUMN||' = :1' into v_array(i) using v_array(i);
END LOOP;
v_return := htmldb_util.table_to_string (v_array);
return v_return;
END tokenize_generic;

END process_upload;
/

Mike Kutz

sooo.... we meet again.

quick answer:  Create Page -> Data Loading

This actually produces a few pages in your application such that they act like a "wizard" for (as the name suggest) Data Loading.

1st page allows you to either upload a csv file or copy+paste into a text area

2nd page verifies the "column in csv to column in table" mapping.  You can also specify certain "formats" for the data (eg date formats)

3rd page allows the end-user to verify the data.

4th page is the "hey, i did this.  here is what was done."

MK

Mike Kutz

sooo.... we meet again.

quick answer:  Create Page -> Data Loading

This actually produces a few pages in your application such that they act like a "wizard" for (as the name suggest) Data Loading.

1st page allows you to either upload a csv file or copy+paste into a text area

2nd page verifies the "column in csv to column in table" mapping.  You can also specify certain "formats" for the data (eg date formats)

3rd page allows the end-user to verify the data.

4th page is the "hey, i did this.  here is what was done."

MK

Doolius

Haha why yes we do.

Thanks again. Unfortunately 4.0 doesn't have that function.

Guess this would be a good time to suggest an upgrade to our application.

Steven

Mike Kutz

Don't forget, you can make a prototype on http://apex.oracle.com/ to show off the new features.

MK