Getting Started with Toad Data Point

Hi, This article covers some useful tips when using Toad Data Point. I will illustrate how easy it is to load Excel data into either your database of choice (in my case, the Oracle database) or to the Local Storage.

Local Storage

Toad Data Point comes with its own internal database called Local Storage. Technically, it is a small MySQL database and if you are MySQL savvy, you can tune this environment a bit. I don’t use it for large objects, and I don’t recommend that you do either. This database is on your workstation so disk storage might become an issue.

In a class, this is a convenient way to load data and perform the learning exercises; for instance, learning Query Builder and mixing Excel data with database data in a single query. Yes, Toad DP can access your Excel spreadsheets as just data!

This tip mostly shows how to import spreadsheet data to your Local Storage.

Accessing Excel, Access, or other data stores thru any ODBC connection will be slow. I suggest you load your Excel data into your database of choice or to Local Storage first, especially if you are going to be using it for more than one job or if you are going to automate the process (maybe another tip in the near future).

Importing Data from Excel

Connect to your database or import to Local Storage. In the connection manager, you navigate to Local Storage.

Navigation Manager and Local Storage

Use the ImportExport button    on the ribbon menu and Import the EMP.xls spreadsheet. This starts the Import wizard.

 EMP and DEPT Spreadsheets

 The above illustration shows the two sample data spreadsheets we will work with. Email me and ask for these if you want to walk through this example.

Import Export Wizard

Select File on the left and Database Table on the right. Make sure your connection is correct (in circle). Point this either at one of your database connections or to Local Storage.

If you are importing to a database (not Local Storage), your account will need ‘create table’ or resource privileges if the table does not exist.

Click Next.

  File Selection for Import

Select the EMP spreadsheet for import. Click ‘Open’ to continue.

 Data and Column Preview

Now you are seeing the File Preview panel. There are a lot of options here. You really want empty fields to be set to Null Values, as I’ve designated here. When importing and letting Toad DP create the table, I usually use the column names from the spreadsheet.

Rename Columns

This panel allows you to rename the columns if you desire. If you remove columns, the data in them won’t be imported.

Selecting a Target

This is where you select either an existing table or a new table. Again, if you are not using Local Storage, your database account will need the proper privileges to do this step (if you are creating or dropping/recreating the table).

Local Storage Database

If you are importing to your Local Storage connection, make sure you have a ‘database’ set up for your table/data. In Local Storage, these databases are like folders and you can think of them as such. This is simply a way of organizing your saved data on your workstation. Quest could be a bit more consistent with their names, but note the red circles, these are the folders I’ve created. You can create and clean up these folders/databases from the Navigation Manager.

Confirm Input and Target 

Saving options

You might wish to save these settings as a template. You would use this to automate this entire process. Automation allows you to perform all of these tasks with the single click of a mouse, or to schedule these using Toad Intelligence Central; another article, perhaps.

Select 'Import Data Now'

Selecting ‘Import Data Now'  

You should now see the result of the import. Also, there is a link to review the log file. This log file is useful if there were any problems with the import.

Now, you can work with the data as any other data store.

Toad DP also allows you to just create a connection to the Excel spreadsheet. This feature uses ODBC and the Toad DP version (32 bit vs 64 bit) needs to match your Microsoft Office installation/product. If it does not, the options to do the import will be grayed out.

Remember, ODBC connections are slow. You would also need to insure that the spreadsheet is always named correctly and in the right location if you were to include this kind of connection in any kind of automation.

Conclusion

Toad Data Point is a powerful data analysis tool that can connect and mix data from over 30 different data stores. Toad Data Point has many of the fine features of Toad built into it (such as templates, auto replace, insights, history, query builder, and more) along with its ability to capture, format, export to Excel (even in pivot format), automate into work flows, and share with others. Toad Data Point is an affordable option when reviewing Business Analysis tools.

This article has been an excerpt from my book: Toad Data Point Quick Start, available on Amazon.com.

I have a customizable 2-day Toad Data Point class that is also available on-site or over the web. Contact me for details…

I hope you find these tips useful in your day to day use of Toad Data Point.