Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Urgent!!!! Export data from Excel to Oracle
- Thread starter jcasas23
- Start date
SantaMufasa
Technical User
K'zaz,
If this is really urgent (as your subject implies), then probably the quickest way to import Excel data into Oracle is the following (presuming that your Oracle table has the same number of columns as your Excel spreadsheet):
1) Save the Excel spreadsheet to tab-delimited file.From Excel, do an <alt-F><alt-A> (File Save As)[Save as type]"Text (tab delimited)(*.txt)" Yada.sql
2) Edit the tab-delimited file. From MS Word, <alt-F><alt-O> (File Open) Yada.sql.
3) Transform the contents of the tab-delimited file to syntactically correct SQL Insert statements.
Step 1: Get rid of double quotes: <alt-E><alt-E>"Find what": " <- double quotes "Replace with": <- nothing. [Replace All]
Step 2: Get rid of any ampersands <alt-E><alt-E>"Find what": & <- ampersand "Replace with": and . [Replace All]
Step 3: Replace <tab>-chrs <alt-E><alt-E>"Find what": ^t <- tab character "Replace with": ',' <- a single-quote, a comma, and a single-quote [Replace All]
Step 4: Prepare INSERT statements <alt-E><alt-E>"Find what": ^p <- carriage returns "Replace with": ');^pINSERT INTO my_table VALUES (' [Replace All]
Step 5: Do a quick visual check for correctness Look at your first and last lines for INSERT fragments.
Step 6: Save the edited file Be sure to save the file as a plain text file.
Step 7: Run the edited file as a SQL script. Log into SQL*Plus and "@yada.txt". Provided you edited properly, the script should INSERT all your Excel data into your Oracle table.
There are other alternatives such as preparing the file to read in as a SQL*Loader file or even using ODBC to read your Excel spreadsheet as a data source, but if you have never done those things before and if this is way urgent, then this is probably your quickest method, especially if this is a one-time adventure.
Let us know how this goes.
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa
(aka Dave of Sandy, Utah, USA @ 23:54 (26Jul04) UTC (aka "GMT" and "Zulu"), 16:54 (26Jul04) Mountain Time)
If this is really urgent (as your subject implies), then probably the quickest way to import Excel data into Oracle is the following (presuming that your Oracle table has the same number of columns as your Excel spreadsheet):
1) Save the Excel spreadsheet to tab-delimited file.From Excel, do an <alt-F><alt-A> (File Save As)[Save as type]"Text (tab delimited)(*.txt)" Yada.sql
2) Edit the tab-delimited file. From MS Word, <alt-F><alt-O> (File Open) Yada.sql.
3) Transform the contents of the tab-delimited file to syntactically correct SQL Insert statements.
Step 1: Get rid of double quotes: <alt-E><alt-E>"Find what": " <- double quotes "Replace with": <- nothing. [Replace All]
Step 2: Get rid of any ampersands <alt-E><alt-E>"Find what": & <- ampersand "Replace with": and . [Replace All]
Step 3: Replace <tab>-chrs <alt-E><alt-E>"Find what": ^t <- tab character "Replace with": ',' <- a single-quote, a comma, and a single-quote [Replace All]
Step 4: Prepare INSERT statements <alt-E><alt-E>"Find what": ^p <- carriage returns "Replace with": ');^pINSERT INTO my_table VALUES (' [Replace All]
Step 5: Do a quick visual check for correctness Look at your first and last lines for INSERT fragments.
Step 6: Save the edited file Be sure to save the file as a plain text file.
Step 7: Run the edited file as a SQL script. Log into SQL*Plus and "@yada.txt". Provided you edited properly, the script should INSERT all your Excel data into your Oracle table.
There are other alternatives such as preparing the file to read in as a SQL*Loader file or even using ODBC to read your Excel spreadsheet as a data source, but if you have never done those things before and if this is way urgent, then this is probably your quickest method, especially if this is a one-time adventure.
Let us know how this goes.
Mufasa(aka Dave of Sandy, Utah, USA @ 23:54 (26Jul04) UTC (aka "GMT" and "Zulu"), 16:54 (26Jul04) Mountain Time)
- Status
Similar threads
- Locked
- Question
- Locked
- Question
