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!
importing excell file to oracle
- Thread starter bilalch
- Start date
I never tried to do excel, I assume you could do it easily, but I would do it with Access. Import the excel into an access table, create the table structure in Oracle, then create an ODBC connection from access to your oracle table, and from access do an append query from access into the oracle table. You can probably go straight from excel to oracle through an odbc connection, but I dont know for sure.
- Thread starter
- #3
1. get your excel worksheet
2. open MS Access and create a blank/empty database
3. In Access click on file - get external data, choose type excel(*.xls) and browse to your worksheet
4. Access will walk you through the import. For example, if your first row contains the column names just check it and tell access to store it in a new table
5. At this point you should be able to import your excel data into an access table
6. Create a table in your Oracle database with appropriate columns to hold the data
7. In Access go back to file - get external data and choose link tables
8. use the drop down to select ODBC connections, if you dont have one already, when the dialog box comes up, click on NEW. Select the Microsoft ODBC for Oracle driver, enter a name for your ODBC connection, click next and then finish and the driver will come up and ask you for a username, password and server, enter your username, password and alias for the Oracle database.
9. You must have your oracle client installed correctly, tnsnames etc. confifured properly and you must use the right connect information, ODBC connections are a pain but will work eventually if all the bits and pieces are installed correctly.
10. Once you have an ODBC connection to Oracle, use an append query in Access to append the rows from the access table into the oracle table. The oracle table, when linked with odbc behaves just like it was an Access table.
that's all there is to it.. its not as bad as it seems, if you have all your clients, drivers, and all that stuff installed correctly...
good luck
2. open MS Access and create a blank/empty database
3. In Access click on file - get external data, choose type excel(*.xls) and browse to your worksheet
4. Access will walk you through the import. For example, if your first row contains the column names just check it and tell access to store it in a new table
5. At this point you should be able to import your excel data into an access table
6. Create a table in your Oracle database with appropriate columns to hold the data
7. In Access go back to file - get external data and choose link tables
8. use the drop down to select ODBC connections, if you dont have one already, when the dialog box comes up, click on NEW. Select the Microsoft ODBC for Oracle driver, enter a name for your ODBC connection, click next and then finish and the driver will come up and ask you for a username, password and server, enter your username, password and alias for the Oracle database.
9. You must have your oracle client installed correctly, tnsnames etc. confifured properly and you must use the right connect information, ODBC connections are a pain but will work eventually if all the bits and pieces are installed correctly.
10. Once you have an ODBC connection to Oracle, use an append query in Access to append the rows from the access table into the oracle table. The oracle table, when linked with odbc behaves just like it was an Access table.
that's all there is to it.. its not as bad as it seems, if you have all your clients, drivers, and all that stuff installed correctly...
good luck
- Status
Similar threads
- Locked
- Question