Mar 28, 2013 11:00:00 PM by Bert Scalzo
In my recent blog titled “Copying Database Data to Microsoft Excel via Toad for Oracle for Oracle” I showed how easily Toad for Oracle can export data into Excel – a favorite tool of many business analysts. But what if instead the data starts out in Excel and I want to get it loaded into Oracle? This too is a very common “use case” scenario many business analysts find a need to often perform – which once again they would like to do for themselves (i.e. without having to ask information systems). Toad for Oracle makes short work of that too.For my example I went to the US Census Department Housing Pattern area and downloaded the housing pattern data for all race/ethnicity groups for year 2000. The Excel spreadsheet file is 2.6 Megabytes and contains some very interesting raw data shown below. There are more columns (26) and rows (2668) than can be seen as indicated below in Figure 1 by the red arrows alongside the horizontal and vertical scroll bars.
Figure 1: US Census Housing Pattern Excel Data
When you have an Excel spreadsheet like this where the data is cleanly organized as columns and rows, then importing it into an Oracle table using Toad for Oracle is very simple. But in the case of this sample Excel data file the first two lines (shown above in Figure 1 by red arrows in the top left corner) and the last three lines will need to be deleted so that the data is truly tabular (and hence easy for Toad for Oracle to parse). Thus I copied the Excel file and deleted those five lines. Finally I also edited the spreadsheet’s column names to remove special characters such as quotes, slashes, asterisks and equal signs which cannot be part of a database column name. Total time to clean the file was less than two minutes.Now I’m ready to have Toad for Oracle auto-magically create an Oracle table and load this spreadsheet into it. So first I open Toad for Oracle’s “Schema Browser” and navigate to “Tables” as shown below in Figure 2. Then I simply invoke Toad for Oracle’s “Create Table” editor by either pressing the “Left Hand Side” (LHS) toolbar icon for “New”, open the “Right Hand Mouse” (RHM) menu and select “Create Table”, or from the “Main Menu” select Database->Create->Table.
Figure 2: Toad for Oracle’s “Schema Browser” - No Tables (Yet)
Regardless of method chosen, that will open Toad for Oracle’s “Create Table” editor as shown below in Figure 3. We simply need to do two things: name the table and invoke the “Load Columns from File” wizard.
Figure 3: Toad for Oracle’s "Create Table” Editor
Pressing the “Load Cols from file” button will launch the “Load Columns from File” wizard shown below in Figure 4. I’ve specified that the input file format is Excel, used my cleaned-up copy of the Census data file, and indicated that spaces in column names are to be converted to underscores (since using spaces in database object names makes life difficult).
Figure 4: Toad for Oracle’s “Create Columns from File” Wizard
Pressing the “OK” button sends me back to the “Create Table” editor as shown below in Figure 5 with Toad for Oracle having parsed the Excel file and auto-magically created all the proper columns – with their names and data types all filled in. And all this was done with absolutely zero effort from me!
Figure 5: Toad for Oracle’s "Create Table" wizard – everything entered
Pressing the “OK” button sends me back to the “Schema Browser” as shown below in Figure 6 with the table created – but note that the data has not yet been loaded. But we’re more than half way home at this point. Now I just need to import the data into my newly created table. Thus I invoke Toad for Oracle’s “Data Import” wizard by opening the “Right Hand Mouse” (RHM) menu and select “Import Data”, or from the “Main Menu” select Database->Import->Import Table Data.
Figure 6: Toad for Oracle’s “Schema Browser” - Table Created
Toad for Oracle’s “Data Import” wizard will open with the table name pre-filled in as shown below in Figure 7 if it’s invoked from the “Schema Browser”, but you’ll need to select the table name if invoked from the main menu. Note that we don’t yet see any sample data since we’ve not yet informed Toad for Oracle what file to look at – which is why we’ll press the “Next”button.
Figure 7: Toad for Oracle's "Import Table Data" wizard – Step #1
In the data import wizard Step #2 I simply choose the Excel file format and name the same file I used during the table creation process as shown below in Figure 8. Strictly speaking the file selected doesn’t have to be the same exact Excel file, but one that’s in the same relative format. I say this in case you have many Excel files of the same format. You could use one Excel file to first create the table and then import each of the others.
Figure 8: Toad for Oracle's "Import Table Data" wizard – Step #2
In the data import wizard Step #3 I must choose some Excel formatted data parsing rules so that Toad for Oracle can accurately import the data as shown below in Figure 9. For example I know that my data begins on row 2 (as row 1 is the column headers), and thus I specify so. Take your time here since this can affect whether the data imports cleanly and without any row loss.
Figure 9: Toad for Oracle's "Import Table Data" wizard – Step #3
In the data import wizard Step #4 I must decide how Toad for Oracle will attempt to match spreadsheet columns to table columns as shown below in Figure 10. Toad for Oracle will at first launch the “Automap” wizard as if you had pressed the “Automap” button. In my case since I know I used this file to create the table and hence the column orderings are exactly the same, I simply specified so via my choice of the second option.
Figure 10: Toad for Oracle's "Import Table Data" wizard – Step #4
In the data import wizard Step #5 Toad for Oracle simply displays the resulting column pairings as shown below in Figure 11. You’ll want to double check those match sets here, and if they’re not correct then you’ll want to go back a step in the wizard and correct the selection. That’s it for here.
Figure 11: Toad for Oracle's "Import Table Data" wizard – Step #5
In the data import wizard Step #6 you’ll need to instruct Toad for Oracle on how to process (i.e. execute) the data loading task as shown below in Figure 12. This is another place where you’ll want to slow down and pick things wisely. In my case I specified to load the data in chunks of 500 rows (i.e. arrays) rather than one at a time. I also chose to do one commit to the database when all done rather than for each row or by the array chunks. These selections will result in a data load that will run far quicker.
Figure 12: Toad for Oracle's "Import Table Data" wizard – Step #6
We’re finally at the end of the data import wizard and pressing the “Execute” button will start Toad for Oracle loading the data and will display a nice progress window as shown below in Figure 13.
Figure 13: Toad for Oracle's "Import Table Data" wizard – progress bar
That’s it – the data is now loaded. Returning to the “Schema Browser” shown below in Figure 14 proves that my Excel spreadsheet data is now in an Oracle table!
Figure 14: Toad for Oracle "Schema Browser" shows Excel Data Now in Table
How to get the most out of Toad for Oracle
Most developers and DBAs use Toad for Oracle to reduce time and effort to develop and manage Oracle databases. But did you know that with Toad for Oracle you can automate administration tasks and proactively manage your databases while embracing performance optimization and risk mitigation? Did you know Toad can now find and control sensitive data across all your Oracle databases? What else can Toad do that you didn’t know about? Which edition will benefit you the most?
Tags: Toad for Oracle
Written by Bert Scalzo
Bert Scalzo is a renowned database expert, Oracle® ACE, author, senior product manager for IDERA's DBArtisan, and formerly a member of Dell Software’s TOAD dev team. With three decades of Oracle® database experience to draw on, Bert’s webcasts garner high attendance and participation rates. His work history includes time at both Oracle Education and Oracle Consulting. Bert holds several Oracle Masters certifications and has an extensive academic background that includes a BS, MS and Ph.D. in computer science, as well as an MBA, and insurance industry designations.
Bert is a highly sought-after speaker who has presented at numerous Oracle conferences and user groups, including OOW, ODTUG, IOUG, OAUG, RMOUG and many others. Bert enjoys sharing his vast knowledge on data modeling, database benchmarking, database tuning and optimization, "star schema" data warehouses, Linux® and VMware®.
As a prolific writer, Bert has produced educational articles, papers and blogs for such well-respected publications as the Oracle Technology Network (OTN), Oracle Magazine, Oracle Informant, PC Week (eWeek), Dell Power Solutions Magazine, The LINUX Journal, LINUX.com, Oracle FAQ, Ask Toad and Toad World.
This popular author is known throughout the industry for his instructive books, which include: