THIS is your number one question – and it has been here on my blog since the day I posted it.
You have an Excel file – and you want that data put into a table. I’ll show you how, and we’ll document each step of the way with plenty of pictures.
You will be prepared to import data to an existing table from Excel with confidence after reading this post. Want to build a new table from Excel? We can do that, too.
Warning: This post has a LOT of pictures.
For our example I’ll be using the HR.EMPLOYEES table to create the XLS file for our import. We’ll use that Excel file to populate an empty copy of the EMPLOYEES table in another schema.
Step 0: The Empty Oracle Table and your Excel File
You have an Oracle table and you have one or more Excel files.
You do know how to view multiple objects at once in SQL Developer, right?
Step 1: Mouse-right click – Import Data
Step 2: Select your input (XLSX) file and verify the data
As you select the file, we’ll grab the first 100 rows for you to browse below. This ‘Preview Row Limit’ defines how many rows you can use to verify the IMPORT as we step through the wizard. You can increase it, but that will take more resources, so don’t go crazy.
Also, does your Excel file have column headers? Do we want to treat those as a row to the table? Probably not. If you uncheck the ‘Header’ flag, the column names will become a new row in your table – and probably fail to be inserted.
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.
Step 3: Create a script or import automatically
For this exercise the ‘Insert’ method will be used. Each row processed in the Excel file will result in an INSERT statement executed on the table we’re importing to.
If you choose ‘Insert Script’, the wizard will end with an INSERT Script in your SQL Worksheet. This is a nice alternative if you want to customize the SQL, or if you need to debug/see why the ‘Insert’ method isn’t working.
Step 4: Select the Excel Columns to be Imported
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 modify the column order, which may make the next step a bit easier.
Step 5: Map the Excel Columns to the Table Columns
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.
And remember how we set that preview window to 100 rows? We’re peaking at the data, looking for problems as we try to fit it into your table column. If we find a problem, we’ll mark the columns with those ‘warning’ symbols.
I’ve polluted my Excel file with some values that I know won’t ‘fit.’ When these rows are encountered in the wizard, they’ll be rejected by the database – but the other rows will come in.
Let’s talk about DATES for a second.
And TIMESTAMPS too. In the excel file, you’re probably going to have some date/time fields you want to move into DATE or TIMESTAMP columns. SQL Developer is treating those value as strings – and YOU need to tell SQL Developer the DATE or TIMESTAMP format to use to be able to convert them.
Let’s look at HIREDATE.
See the ‘Format’ drop down selector? SQL Developer has defaulted the DATE format string to ‘DD-MON-RR’ – we try to guess based on the rows we’re looking at in that 100 preview window.
If we have guessed wrong, or were unable to figure it out, you’ll need to input this yourself. The Oracle Docs can help you define the correct DATE Format Model. If you see the little warning graphics next to your Date values in the Data panel, you might have the wrong format.
Step 6: Verify your settings and GO!
Click on the ‘Finish’ button.
If the Wizard runs into any problems doing the INSERTs, you’ll see this:
I’m going to say ‘Yes’ to ignore all the errors. But, if you need EVERY single row – you need to say ‘Cancel’ to start over. Then you can either fix your data in the Excel file, or make changes to your table so the data will fit/work.
After clicking ‘Yes’, we’ll get to the end of our story, and our data!
Step 7: See What Worked and What Didn’t
First, any bad news?
If there were rows rejected by the database, we’ll see those now.
Now let’s go look at our new table data!
Note the ‘Log’ panel. We’ll show you the file we worked with, and how much time it took to load the data.
Just a few more pointers when it comes to Excel
- Storing data in Excel can be…dangerous
- If you have a lot of data or if this will be a repeating process, consider External Tables or SQL*Loader instead
- If you like to put data in Oracle because you understand SQL better than Excel macros, then welcome to the club 😉