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;
/