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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
From Excel to Oracle
- Thread starter Leon1977
- Start date
Different ways to do this. Can not do this directly, however (Which is a pain, years ago Oracle did supply a utility to import an Excel spreadsheet). The easiest way (at least for me) is simply to export the spreadsheet into a csv file. Then use Sql*Loader.
This export can only be done one sheet at a time. So, if you have six different worksheets in an Excel spreadsheet, then you have to have six different csv files. Once the data is put into a csv file, then Sql*Loader can be used to load each file into your table(s). Read the Oracle documentation on how to create a control file for use by Sql*Loader. Sql*Loader is always part of the Database as a utility. So, you are looking at moving your csv files to the db server (a binary Ftp or the like). Sql*Loader can also be part of the Oracle client installation that is typical for a developer to have (Do you have SqlPlus?). To check for Sql*Loader, go to the Dos prompt (Sql*Loader can only be run on the command line regardless of the platform NT, UNIX, or VMS)) and type in "Sqlldr" (assumming you have 8i, different version of Oracle have different names for Sql*Loader) and then Enter. This will tell you if it is installed and will provide a list of options for Sql*Loader. You can also browse the [ORACLE_HOME]\Bin folder for Sqlldr.
The above works, unless the spreadsheet is pretty involved. If your spreadsheet is too complicated, then your are looking at writing VBA/VB to move your data from Excel into Oracle.
This export can only be done one sheet at a time. So, if you have six different worksheets in an Excel spreadsheet, then you have to have six different csv files. Once the data is put into a csv file, then Sql*Loader can be used to load each file into your table(s). Read the Oracle documentation on how to create a control file for use by Sql*Loader. Sql*Loader is always part of the Database as a utility. So, you are looking at moving your csv files to the db server (a binary Ftp or the like). Sql*Loader can also be part of the Oracle client installation that is typical for a developer to have (Do you have SqlPlus?). To check for Sql*Loader, go to the Dos prompt (Sql*Loader can only be run on the command line regardless of the platform NT, UNIX, or VMS)) and type in "Sqlldr" (assumming you have 8i, different version of Oracle have different names for Sql*Loader) and then Enter. This will tell you if it is installed and will provide a list of options for Sql*Loader. You can also browse the [ORACLE_HOME]\Bin folder for Sqlldr.
The above works, unless the spreadsheet is pretty involved. If your spreadsheet is too complicated, then your are looking at writing VBA/VB to move your data from Excel into Oracle.
You may try this Oracle tool
SQL*XL links Excel and Oracle together. SQL*XL provides a full featured front-end to Oracle. SQL*XL adds Oracle access to Excel. Through menu items and dialog screens SQL can be entered. Oraxcel fetches the data into...
SQL*XL links Excel and Oracle together. SQL*XL provides a full featured front-end to Oracle. SQL*XL adds Oracle access to Excel. Through menu items and dialog screens SQL can be entered. Oraxcel fetches the data into...
HaraldBuesching
Programmer
If you have access to Oracle via MS-Access, you should use Access it understands Sheets and Oracle. But it really depends of your data, if the table already exists ....
- Thread starter
- #5
- Status
Similar threads
