>Does anyone have a tested method of uploading bulk data to a database? I
>have a need (annually) for the admin person to be able to take data from an
>Excel spreadsheet and have it inserted into an Oracle database. Any sample
>code would be greatly appreciated. I've played with a CFC that makes Java
>calls but without success.
>
>Thanks in advance,
>
>--
>Chris Tilley
With Oracle, 9i or newer your best bet is to set up an externally organized
table. It's basically stored SQL loader which allows you to access an external
text file as you would any read-only oracle table via SQL. You would export
your Excel data into csv format, then set up the EOT to point to that file.
You can then overwrite that file at any time with new data. Here's an example
of the PL/SQL to create one of these. Most of it will be cut and paste -
you'll need to create a directory in Oracle (pointer to the filesystem
directory you will put the CSV in) and fill it in for "MYDATA_DIR"
CREATE TABLE EXT_ACCOUNT_BALANCE
(
ACCOUNT VARCHAR2(20 BYTE),
BEGIN_BAL NUMBER
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY MYDATA_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY 0x'0a' CHARACTERSET WE8ISO8859P9
LOGFILE 'beginbalance.log'
BADFILE 'beginbalance.bad'
READSIZE 1048576
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
account
CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
begin_bal
CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
LOCATION (MYDATA_DIR:'beginbalance.csv')
)
;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252253
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
