Re: Excel to Oracle (to Temp Table)

From: george lewycky <gelewyc_at_nyct.com>
Date: 22 Jan 2003 14:09:37 -0800
Message-ID: <68aecc05.0301221409.972166b_at_posting.google.com>

"Paul W Smith" <pws_at_cowboys.btinternet.co.uk> wrote in message news:<b0jh1a$nl$1_at_knossos.btinternet.com>...
> I have a client who wants a worksheet built which will be used to create a
> temporary table in an Oracle database. Is this a simple task?
>
> Can the 'upload' (table creation) be done from Excel or would the procedure
> have to be run as an Import from Oracle?
>
> Any assistance would be gratefully received.
>
> PWS

1. create a .csv delimited flat file from excel

TEXT WRITE PROGRAM (excel macro)
http://tushar-mehta.com/excel/software/text_write_program/index.html has a real useful Macro &#8220;Text Write Program&#8221; to delimit &#8220;,&#8221; for a text file to be loaded into oracle using SQL, etc Excel will only do commas !!!

2. ftp the file or copy the file to upload to desired machine (if needed)
3. use sqlloader to load the data into the desired table/columns

Procedure for Porting the Customized Customer spreadsheet into Oracle Ø Save the Excel spreadsheet as a .CSV file to delimit the file so that Oracle&#8217;s SQL code can segregate each field

Ø	Download the file to the PC
Ø	Customized SQL code has been written and tested using Oracle’s

SQL*Loader which has been tested to upload the data to Oracle&#8217;s table: RA_CUSTOMERS_INTERFACE_ALL
In a DOS Window run the dos program SQLLDR.EXE

> sqlldr apps/apps_at_vis ß all one word!!!

Control = loadtbag.ctl < enter a filename containing the code
>

Loadtbag.ctl is shown below:

load data
infile 'c:\MY SQL\oracust.txt' ß flat file of the DATA !!!!

replace					ß replace or append 
into table tbagcust			ß Oracle table created above
fields terminated by '","'       ß delimiter
(AUTH,CUST, NAME,                ß all fields

ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,
CITYCOUNTRY,STATE,ZIP,PHONE,CONTACT,DATEADDED )

sample of the data file
NOTE: the first field DOESN&#8217;T have &#8220;,&#8221; preceding it, only succeeding it !!!!!!
This is due to fields terminated by '","' which is the delimiter separating the fields just like we do for spreadsheets

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8

********************************* Top of Data
**********************************
1","0066","ABC BUS MANUFACTURING    ","1506 30TH STREET        

","NORTHWEST

1","0068","ABC RAIL PRODUCTS CORP.  ","11TH & WASHINGTON STREETS","
1","0070","ABCO REFRIGERATION SUPPLY","49-70 31ST STREET        ","
1","0090","ABEE PRINTING CORP.      ","305-1 KNICKERBOCKER AVE. ","

If you use append data must already be on the table

Other options were explored for loading this data and SQL*Loader was found to be the most effective for the amount of data being processed.

good luck

george

O'Reilly has a good book on SQL*Loader Received on Wed Jan 22 2003 - 23:09:37 CET