yr_k
asked on
Import Excel file into oracle db
Hi,
How to import Excel file (table) into oracle (9i) database (otherthan SQL loader)?
Thanks
How to import Excel file (table) into oracle (9i) database (otherthan SQL loader)?
Thanks
Using Access
---------------
Open a blank or an existing Access Database
Go to file/ Get external Data / Import
In the "Files of type:" drop down list, choose Microsoft Excel, find your file and click "Import", follow the Wizard clicking next, and click finish to import the Excel's table into Access.
In Windows go to control Panel/ Administrative Tools / Data Sources ODBC / System DSN tab
click add
Scroll down if necessary to select the Oracle Driver installed in your machine
click OK, it will take you to Oracle ODBC Driver Configuration dialog box
In Data Source Name: type the database name
Description: Up to you
TNS Service Name: Select the database you wish to access from the drop down list
User ID: Your user ID
On your right, click button "Test Connection" to make sure you can connect.
Click OK, you're done here.
Now, go to your Access database
Go to file/ Get external Data / Link Table
In the Link window "bottom left corner" select ODBC databases from "Files of Type:" drop down list
In "Select Data Source" window select the tab Machine Data Source and then select the data source name you created,
click OK, the dialog box for connecting to the database will appear, enter your password, click OK, then you'll see a list of tables in the database, select the one you wish to update or if you are selecting more than one, just click on the name -you don't need to use Shift - , click OK and you have linked the Oracle table to Access.
Go to Queries and create a new query using your local Access Table ( the one you imported from Excel ), make it an Append query using the "Query Type" button. When the Append dialog box is displayed, select the ORACLE table from the drop down list "Table Name", click OK
Now, in the datasheet view drag the columns you wish to append from your local table to the empty fields right below, then update "Append to: " to go with your ORACLE table's corresponding fields
Click the run " ! " button, enter your password and your're done.
You may need to update the ODBC timeout if running for too long: Go to View / Properties and change the value of ODBC timeout to 3600.
Hope this helps!
---------------
Open a blank or an existing Access Database
Go to file/ Get external Data / Import
In the "Files of type:" drop down list, choose Microsoft Excel, find your file and click "Import", follow the Wizard clicking next, and click finish to import the Excel's table into Access.
In Windows go to control Panel/ Administrative Tools / Data Sources ODBC / System DSN tab
click add
Scroll down if necessary to select the Oracle Driver installed in your machine
click OK, it will take you to Oracle ODBC Driver Configuration dialog box
In Data Source Name: type the database name
Description: Up to you
TNS Service Name: Select the database you wish to access from the drop down list
User ID: Your user ID
On your right, click button "Test Connection" to make sure you can connect.
Click OK, you're done here.
Now, go to your Access database
Go to file/ Get external Data / Link Table
In the Link window "bottom left corner" select ODBC databases from "Files of Type:" drop down list
In "Select Data Source" window select the tab Machine Data Source and then select the data source name you created,
click OK, the dialog box for connecting to the database will appear, enter your password, click OK, then you'll see a list of tables in the database, select the one you wish to update or if you are selecting more than one, just click on the name -you don't need to use Shift - , click OK and you have linked the Oracle table to Access.
Go to Queries and create a new query using your local Access Table ( the one you imported from Excel ), make it an Append query using the "Query Type" button. When the Append dialog box is displayed, select the ORACLE table from the drop down list "Table Name", click OK
Now, in the datasheet view drag the columns you wish to append from your local table to the empty fields right below, then update "Append to: " to go with your ORACLE table's corresponding fields
Click the run " ! " button, enter your password and your're done.
You may need to update the ODBC timeout if running for too long: Go to View / Properties and change the value of ODBC timeout to 3600.
Hope this helps!
You can use sqlplus worksheet.!
Sorry about the above post it was a mistake.
ASKER
Hi Henka,
thanks for steps through SQL Server.
However, I dont have SQL Server. is there any way with Oracle tools or Toad
Thanks
thanks for steps through SQL Server.
However, I dont have SQL Server. is there any way with Oracle tools or Toad
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't know Toad. In Oracle there is SQLloader there ...
In TOAD click on Import/ Table Data
ASKER
thanks for all
I would like to accept both makhan & Henka
I would like to accept both makhan & Henka
download the sql developer, from http://www.oracle.com, it's free and it has an option to import data
regards
regards
not work with unicode data
The information in this article applies to:
Oracle ODBC Driver - Version: 9.2
This problem can occur on any platform.
Goal
To transfer data from an Excel spreadsheet into the Oracle database using the Oracle ODBC Driver and Data Transformation Services.
Fix
Instructions:
==========
1. Open SQL Server Enterprise Manager
2. Click on the + to expand the tree for the database server.
You will need to expand it three times.
Note: If the SQL Server does not appear here, you will need to right click and go through the Wizard for SQL Server Registration.
- The first level of the tree is called Microsoft Sql Server,The second level of the tree is called SQL Server Group,and the third level of the tree is your SQL Server Database.
For these instructions we will call our SQL Server Database, 'local'.
3. Under the local Database, click on the + to expand the tree control for 'Data Transformation Services'
4. Under Data Transformation Services, right click and select "New Package"
5. A DTS Package screen will appear. Drop a 'Other Connection' onto the workspace.
a. Select the Oracle in ODBC Driver from the Data Source list.
b. Select your DSN in the User/System DSN pull down list.
c. In the Username field type in the userid.
d. In the Password filed type in the password.
e. Click on the OK button.
6. In the DTS Package screen. Drop an Excel 8.0 Connection onto the workspace.
a. In the File Name click on the ... button and navigate to an excel file. Click Open to open the file.Then click on the OK button in the Connection Properties screen.
7.In the DTS Package screen, create a Transform between the Other Connection and Excel by clicking on the Excel icon, hold down the control key and click on the Other Connection icon. Right click and select Transform Data Task.
8. Right-click on the Transform Data Task Link and click properties. Select the table you are trying to insert the excel data into. Then click on the transformations tab and make sure that the data is being mapped correctly to each column. Once that is correct click on the OK button.
9. Now do a right click on the Transform Data Task icon (it's the arrow) and click on Execute. This will now take the data from the Excel spreadsheet and insert it into the Oracle database.