How to Import from Excel to Oracle with SQL Developer
This question comes up about as frequently as the ‘how do I export data to XLS?’ question. It’s pretty simple once you’ve ran through the process a few times. But you may be here because you’ve never ran through the process before.
This post will step you from beginning to end. You should be prepared to import data to an existing table from Excel with confidence after reading this post. You can use SQL Developer to create a new table for your Excel data, but that will be covered in a subsequent post.
Warning: This post has a LOT of pictures.
For our example I’ll be using HR.EMPLOYEES to build the XLS file. I have created a blank copy of the table in another schema and want to import the data from my excel file over.
You do know how to view multiple objects at once in SQL Developer, right?
Does your Excel file have column headers? Do we want to treat those
as a row to the table? Probably not. The default options take care of
this. You can also choose to preview more than 100 rows.
Here’s what it looks like if you uncheck the ‘Header’ box
Sometimes your Excel file has multiple headers, or you may need to
only import a certain subset of the spreadsheet. Use the ‘Skip Rows’
option to get the right data.
For this exercise the ‘Insert’ method will be used.
You may have an Excel file with 100 columns but your table only has 30. This is where you tell SQL Developer what columns are to be used for the import. You can also change up the column order, which may make the next step a bit easier.
If you’re not paying attention and just letting the wizard guide you home, then now is the time to wake up. There’s a good chance the column order of the Excel file won’t match the definition of your table. This is where you will tell SQL Developer what columns in the spreadsheet match up to what columns in the Oracle table.
SQL Developer is telling you it doesn’t know how to reconcile the data for this DATE column. We need to know what the DATE FORMAT is.
So we need to go back to the Column definition wizard and inspect the HIRE_DATE column settings.
You need to look at how the dates are stored in the spreadsheet and write them in terms that Oracle can understand. This will be used on the INSERTs via a TO_DATE() function that will turn your Excel string into an actual DATE value.
After correcting this, go back to the Verification screen and see if that fixes the problem.
Click on the ‘Finish’ button.
Note the ‘Log’ panel. SQL Developer is processing the records in batches of 50. No errors and the data is there!
This question comes up about as frequently as the ‘how do I export data to XLS?’ question. It’s pretty simple once you’ve ran through the process a few times. But you may be here because you’ve never ran through the process before.
This post will step you from beginning to end. You should be prepared to import data to an existing table from Excel with confidence after reading this post. You can use SQL Developer to create a new table for your Excel data, but that will be covered in a subsequent post.
Warning: This post has a LOT of pictures.
For our example I’ll be using HR.EMPLOYEES to build the XLS file. I have created a blank copy of the table in another schema and want to import the data from my excel file over.
Step 0: The Empty Oracle Table and your Excel File
You have an Oracle table and you have one or more Excel files.
Data here but not there!?!
Step 1: Mouse-right click – Import Data
Yes, it's that easy.
Step 2: Select your input (XLS) file
Yes, we also support XLSX, CSV, etc
Step 3: Verify the data being read
Mind the headers!
Here’s what it looks like if you uncheck the ‘Header’ box
Sometimes you may want the column headers as a row in the table?
Step 4: Create a script or import automatically
Script or do it for me?
Step 5:
Choose the Excel columns to be imported
Step 6:
If you’re not paying attention and just letting the wizard guide you home, then now is the time to wake up. There’s a good chance the column order of the Excel file won’t match the definition of your table. This is where you will tell SQL Developer what columns in the spreadsheet match up to what columns in the Oracle table.
Step 7: Verify your settings
Hit the ‘verify’ button. Fix any mistakes.
Ruh roh raggy!
So we need to go back to the Column definition wizard and inspect the HIRE_DATE column settings.
You need to look at how the dates are stored in the spreadsheet and write them in terms that Oracle can understand. This will be used on the INSERTs via a TO_DATE() function that will turn your Excel string into an actual DATE value.
After correcting this, go back to the Verification screen and see if that fixes the problem.
Step 8:
Everything looks right!
Step 9: Verify the import look at your new table data
The data is there and the dates look right!
4 comments
Thank u very much..
ReplyDeleteCan you do this in TOAD too?
ReplyDeleteWhy not use external tables?
ReplyDeleteThank u very much..
ReplyDelete