TL;DR – Skip to the video!

Detest GUIs, want to do this via the control-line?

THIS is your number one question – and it has been here on my blog since the mean solar day I posted information technology.

You have an Excel file – and you lot want that information put into a tabular array. I'll show y'all how, and we'll document each step of the way with plenty of pictures.

Yous will exist prepared to import information to an existing table from Excel with confidence afterward reading this postal service. Want to build a new table from Excel? We can do that, too.

Warning: This post has a LOT of pictures.

For our case I'll be using the 60 minutes.EMPLOYEES table to create the XLS file for our import. Nosotros'll utilise that Excel file to populate an empty copy of the EMPLOYEES table in another schema.

Pace 0: The Empty Oracle Tabular array and your Excel File

You accept an Oracle table and you have one or more Excel files.

You do know how to view multiple objects at in one case in SQL Programmer, right?

Step 1: Mouse-correct click – Import Data

Step 2: Select your input (XLSX) file and verify the data

If y'all've ran this magician before, you can option files from previous sessions.

As you select the file, we'll grab the commencement 100 rows for you to browse below. This 'Preview Row Limit' defines how many rows yous can utilise to verify the IMPORT as we step through the wizard. You can increase it, merely that will accept more than resources, so don't go crazy.

Also, does your Excel file accept column headers? Do we want to care for those as a row to the table? Probably not. If y'all uncheck the 'Header' flag, the column names volition 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 just import a certain subset of the spreadsheet. Use the 'Skip Rows' choice to go the right information.

Pace iii: Create a script or import automatically

As the magician progresses, we'll keep the File Contents preview panel handy and so you don't have to alt+tab dorsum and forth from Excel to SQL Developer.

For this practise the 'Insert' method will be used. Each row candy in the Excel file will event in an INSERT statement executed on the table nosotros're importing to.

If you choose 'Insert Script', the magician will stop with an INSERT Script in your SQL Worksheet. This is a dainty alternative if you desire to customize the SQL, or if you lot need to debug/see why the 'Insert' method isn't working.

Working with CSV? You'll get even more methods – great for VERY LARGE information sets.

Footstep 4: Select the Excel Columns to be Imported

The wizard defaults to all of the Excel columns beingness used, in the order they're establish in the file.

You lot may accept an Excel file with 100 columns merely your table only has 30. This is where y'all tell SQL Developer what columns are to be used for the import. You can too modify the column order, which may make the next step a bit easier.

Step five: Map the Excel Columns to the Table Columns

The left panel represents the columns in the XLS file. The information on the correct shows where that data is going, and how information technology volition be treated.

If you're not paying attending and but letting the sorcerer guide you abode, then now is the time to wake upwards. There'southward a practiced take a chance the column order of the Excel file won't match the definition of your table. This is where you will tell SQL Programmer what columns in the spreadsheet match up to what columns in the Oracle table.

And recall how nosotros ready that preview window to 100 rows? Nosotros're peaking at the data, looking for problems as nosotros endeavor to fit it into your table column. If we find a problem, we'll marking 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 volition 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/fourth dimension fields you want to move into Engagement or TIMESTAMP columns. SQL Developer is treating those value as strings – and Y'all demand to tell SQL Developer the DATE or TIMESTAMP format to use to exist able to convert them.

Let's expect at HIREDATE.

That 'ha ha' value will never make information technology in as a HIRE_DATE value – unless y'all're storing DATES in a VARCHAR2 – and if y'all're doing that, you're doing it Incorrect. Always store DATES as a DATE!

Run into the 'Format' drop down selector? SQL Developer has defaulted the Date format string to 'DD-Monday-RR' – nosotros try to guess based on the rows nosotros're looking at in that 100 preview window.

If nosotros have guessed wrong, or were unable to figure information technology out, you lot'll need to input this yourself. The Oracle Docs can help you define the correct Engagement Format Model. If you run into the fiddling warning graphics adjacent to your Date values in the Data panel, you might have the incorrect format.

Step 6: Verify your settings and Go!

The 'End' push button will start the magic.

Click on the 'Cease' button.

If the Wizard runs into whatever problems doing the INSERTs, you'll see this:

Call back that funny data i put into the Excel file? That'south causing problems now.

I'm going to say 'Yes' to ignore all the errors. Just, if y'all need EVERY single row – you lot need to say 'Cancel' to start over. Then you can either fix your information in the Excel file, or brand changes to your table and then the data will fit/work.

After clicking 'Yes', we'll get to the end of our story, and our data!

Stride 7: Run into What Worked and What Didn't

First, any bad news?

If there were rows rejected by the database, we'll run across those now.

Behind that dialog are the INSERT statements we tried to run, but didn't piece of work. You might exist able to edit those manually to ready a few records. Simply if yous have thousands of rejected rows – amend to fix at the source.

Now allow's get wait at our new table data!

I love the sweetness olfactory property of data in the morn!

Notation the 'Log' console. We'll show you lot the file we worked with, and how much time it took to load the data.

Simply a few more pointers when it comes to Excel

  • Storing data in Excel tin can be…dangerous
  • If yous accept a lot of information or if this will be a repeating process, consider External Tables or SQL*Loader instead
  • If yous similar to put data in Oracle because you lot empathise SQL better than Excel macros, then welcome to the club 😉
  • Hither's 11 more than tips on Importing Excel/CSV into your database

The Movie