Import dữ liệu từ excel vào oracle

Trong nhiều trường hợp ta cần đưa dữ liệu từ file vào DATABASE, cụ thể khi ta muốn import dữ liệu từ 1 file dữ liệu nào đó có trong DATABASE. Cách đơn giản nhất đó là viết câu lệnh Insert data vào bảng nhưng cách này rất mất thời gian và có thể làm treo hệ thống. Sau đây tôi sẽ hướng dẫn các bạn Import dữ liệu vào table Oracle bằng file chứa data như CSV, Excel, Txt…

Ta sử dụng phần mềm Oracle SQL Developer được cung cấp miễn phí bởi Oracle, có thể chạy ngay mà không cần cài đặt.

Ví dụ ta có file diemso.csv, cần đưa dữ liệu này vào bảng DIEMSO có các cột tương tự nhau.

Các bước chính để import dữ liệu vào Oracle

B1. Đầu tiên ta nháy đúp vào bảng nó, nó sẽ hiển thị sang màn hình làm việc chính các thông tin của bảng này. Ta có thể xem các thông tin của bảng như: thông tin các cột, thông tin data trong đó, các thông tin khác như index, contrain…

B2. Ta chọn sang tab data, nếu có dữ liệu thì nó sẽ show một phần dữ liệu ra cho ta có thể xem.

B3. Ta chọn Action… ở góc bên phải màn hình. Chọn Import data. Màn hình chọn file sẽ hiện ra để ta đi tìm file để import.

B4. Chọn đến file cần import, ở đây ta chọn đến file diemso.csv

B5. Từ đây ta chủ yếu chọn Next, mọi tham số ta để mặc định. Lưu ý đến màn hình số 4, phần map giữa cột của file csv với cột của TABLE trong DATABASE. Ta cần để ý chọn để map cho đúng cột, vì có thể thứ tự các cột trong csv có thể khác. Với cột có DATA là DATETIME ta cũng cần lưu ý định dạng để hệ thống có thể import vào đúng.
Ví dụ định dạng trong file csv là DD/MM/YYYY thì ta phải nhập định dạng này dưới phần MAP ID[phía bên phải].

B6. Sau đó Next đến bước cuối cùng, ta click verify. Tất cả SUCCESS là OK.

B7. Sau đó nhấn Finish. Chờ 1 lát thế là xong. Cách import này rất nhanh. Thay cho việc gen câu lệnh để insert hàng hoạt dữ liệu vào TABLE.

Trên đây là cách import dữ liệu vào table Oracle thông qua file CSV. Ta cũng có thể thao tác tương tự đối với file txt hoặc file excel… để đưa dữ liệu vào bảng thay vì phải dụng câu lệnh insert.

Có thể bạn quan tâm:

• Tối ưu câu lệnh SQL: Oracle so sánh thời gian
• Oracle so sánh thời gian ngày tháng

Post navigation

TL;DR – Skip to the video!

Hate GUIs, want to do this via the command-line?

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

If you’ve ran this wizard before, you can pick files from previous sessions.

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

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

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.

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

Step 4: Select the Excel Columns to be Imported

The wizard defaults to all of the Excel columns being used, in the order they’re found in the file.

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

The left panel represents the columns in the XLS file. The information on the right shows where that data is going, and how it will be treated.

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.

That ‘ha ha’ value will never make it in as a HIRE_DATE value – unless you’re storing DATES in a VARCHAR2 – and if you’re doing that, you’re doing it WRONG. Always store DATES as a DATE!

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!

The ‘Finish’ button will start the magic.

Click on the ‘Finish’ button.

If the Wizard runs into any problems doing the INSERTs, you’ll see this:

Remember that funny data i put into the Excel file? That’s causing problems now.

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.

Behind that dialog are the INSERT statements we tried to run, but didn’t work. You might be able to edit those manually to fix a few records. But if you have thousands of rejected rows – better to fix at the source.

Now let’s go look at our new table data!

I love the sweet smell of data in the morning!

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 😉
• Here’s 11 more tips on Importing Excel/CSV into your database

The Movie