How to load data from Excel into Oracle using Toad®

Are you trying to figure out how to load data from Excel into your database?

The problem: load data to Oracle

Many business analysts rely heavily upon Microsoft Excel as one of their primary sources for important business data. At some point they will need to load data into an Oracle database, and thus you will often hear them make requests such as the following:

• CopyingExcel data to Oracle
• ImportingExcel data to Oracle
• LoadingExcel data to Oracle
• TransferringExcel data to Oracle

They key point is that regardless of how the business users ask, the request is essentially the same. Some very common ways they obtain such loading of the database data include:

Business user sending a formal data load request to their information systems people to perform and deliver the loaded Oracle table. However, this process can take time – sometimes more than the business user can wait.

Business user truly self-servicing by loading the database data themselves using a powerful tool like Quest®Toad® for Oracle, and then working with that data in Toad or some other database tool. This approach is quick and easy, plus Toad for Oracle integrates easily into the business analyst’s workflow.

In my recent blog titled “Copying database data to Microsoft Excel via Toad for Oracle” I showed how easily Toad for Oracle can export data into Excel — a favorite tool of many business analysts.

Toad for Oracle makes short work of this use case as well — how to load data from Excel into Oracle. For my example, I chose to use the Internet Movie Database (IMDB) and thus downloaded the movies title basic data. I then unzipped the file to obtain the 606 megabyte tab delimited text file named data.tsv. I then renamed that data file to movies.tsv so as not to forget what the file contains.

The solution to load data into Oracle

Many business analysts strongly favor the second choice of using Toad for Oracle to self-service and load data. In this blog I’ll review some common scenarios that facilitate easily copying Excel data to Oracle using Toad. The techniques that I’ll be showing work essentially the same in both of Toad’s primary interfaces for this task: the Schema Browser and Main-Menu–>Database –>Create –> Table. I’ll demonstrate using the Schema Browser, which is often the most common choice for this task.

Figure 1: Create a new table to load data

Now in Figure 2 below I’m inside the create table screen. First I chose to check the box for display advanced features, otherwise I would not see displayed the options which will be required. Second, I chose to open a file to read for the table’s column names. It uses the first row of data for that. Third, I chose my tab delimited text file. Finally, I pressed OK. Since my file was over 600 megabytes, it took a few seconds to process.

Figure 2: Method to populate the table columns to load data.

In Figure 3 below I now have all the columns for my table. Note that Toad has set the default datatype to VARCHAR2 for all columns. Moreover Toad also has set each column to be optional. You may need to modify these default choices as you know your data far better than Toad. Then press the OK button to create the table and load data.

Figure 3: Resulting population of table columns.

In Figure 4 below I have now created the table required to hold all the data for the IMDB movies data file. Remember, the file is over 600 megabytes in size, so the data load will likely take a few moments. But we’re more than half-way home at this point. Now I just need to import the data into my newly created table.

Figure 4: Resulting table creation.

In Figure 5 below I have now invoked Toad for Oracle’s Data Import wizard by opening the Right Hand Mouse (RHM) menu and select Import –> Data, or Main Menu –>Database–>Import–>Import Table Data. As you can now see, I am setup to initiate loading of the MOVIES table. However this is a multi-step wizard where we’ll need to specify additional information before the table can be loaded.

Figure 5: Page 1 of the data import wizard.

In Figure 6 below I am now on the second page of the data import wizard where I identify the file type and name. If you look back at Figure 2, you will see that we’ve answered two of the three key parameters. Note that we’ve yet to say that it’s a tab delimited file. Remember too that the first line in the file has the column names, so we’ll need to be able to tell Toad to skip over those.

Figure 6: Page 2 of the data import wizard.

In Figure 7 below I am now on the third page of the data import wizard where I identify the file as tab delimited text.

Figure 7: Page 3 of the data import wizard.

In Figure 8 below I am now on the fourth page of the data import wizard where I identify that Toad should start reading the table data at line 2 because the first row contains the column names. You should take your time on this page of the wizard to make sure you review the dates, times, and numbers settings based upon how your data is formatted in the text file.

Figure 8: Page 4 of the data import wizard.

In Figure 9 below I am now on the fifth page of the data import wizard where Toad has read a sample of the data rows and displays them for you to verify. Moreover, Toad displays a pop-up to see how you want to map the columns of data to the table columns. In this case it really does not matter. But had I created the columns in the table in a different order or reordered them, then the choice would have to be by matching the names.

Figure 9: Page 5 of the data import wizard.

In Figure 10 below I am now on the sixth page of the data import wizard where Toad displays the results of your selection from the prior page’s pop up where you specified the data file field to table column mapping. Again if I had reordered the columns in the table, I would have had to make changes on this page to properly align the fields to columns.

Figure 10: Page 6 of the data import wizard.

In Figure 11 below I am now on the seventh page of the data import wizard where Toad displays the field to column matching results performed upon the sample data with the column header row removed. Note that the columns displayed here are sized based upon the column name size. I’ll need to press the size cols to data button if I wish to be able to see the column values displayed in their entirety.

Figure 11: Page 7 of the data import wizard.

In Figure 12 below I am now on the eighth page of the data import wizard where Toad allows one to specify a host of parameters or options that control the actual database transaction processing. As you can see, I have said to perform batch array inserts of 500 rows, to truncate the table before loading, and to commit after every 500 rows. These choices will affect how long the actual data load takes. Remember that my data file is over 600 megabytes, so setting these parameters is critical. Now when I press the execute button, the data import process will begin.

Figure 12: Page 8 of the data import wizard.

In Figure 13 below I am now executing the data import process. The first time I ran this I got errors because the default column data types and size were VARCHAR(32) and the movies titles are much longer than that. I just chose to abort, increased the column lengths, and restarted the wizard. It then ran to completion without error. Did you note that even on my small VM running on a desktop PC that I’m getting loads of over 66,000 records per second!

Figure 13: Page 9 of the data import wizard.

Conclusion

If you are a business user who wants to copy from Excel to Oracle, use Toad. You can easily do automatic table creation and data loading as shown above. Thus you can self-service without requesting help from your information systems people. No other database tool makes copying data from Excel to Oracle quicker and easier than Toad. Plus no matter whether you’re using Toad freeware or the commercial version of Toad, the process is essentially the same.