Toad World Blog

Feeding your database: Techniques for building automated Excel imports

Jul 8, 2020 9:00:00 AM by Mathew Phan

Companies these days are collecting and storing more and more data than ever before from all kinds of sources. As a result, importing data has a lot more variables to consider.

Man working on computerized robot. Feeding your database: Different techniques for automating Excel imports

Running a query and saving the results to a file is the most common automation task that I run across when talking to Toad Data Point users. Do you know what the next most common task I get asked about the most? You may have already guessed, the next most common task I get asked about is importing data from a file to a database. This is how I would import batches of Excel files into Toad Data Point.

 

How to import data from Excel to sql server

Let's say your customers upload Excel data files to your company's FTP site. The files can be different names, have different timestamps, but generally have the same kind of data within them. You need to take this data and update an existing aggregate table in a reporting database. Your company has grown so fast, that it's impossible to manually do these imports and updates anymore. You want to be able to automate importing these Excel data files into a reporting database every night.

Importing Excel data into a database can be a chore. This entire scenario can be done using features in Toad Data Point and can be automated to run entirely on a schedule. Let's break this task down into small bite-size pieces. 

 

The Import Wizard's Folder option: importing multiple Excel files, different timestamps in their file name, but same data format

In a single, straight-forward import, I can use the Import Wizard as outlined in this video from Robert Pound, Toad Data Point Import Wizard. The video shows I can also pair multiple files and tables together (File A to Table A, File B to table B) using the wizard.

However, in the above scenario, what if I have several files, that all go in the same table(s)? In this case, multiple Excel files that have a different timestamps in their file name, but the same format of data within them. Well that's a great use case to use the Folder option in Import Wizard. Here's how to do that:

  1. Launch the Import Wizard. You can find it in the main toolbar and drop down directly to it.
    Alt text: In the menu bar, click ImportExport, then Import Wizard.
  2. In the Import Wizard, select Add Folder.

    Alt text: In the Import Wizard, select Add Folder.

  3. Browse the folder. You can set up wild cards and filter as necessary to select only the files you want for this import task. All these files will go to the same single table.

    Select the files you want to import to the same single table.

  4. Click Next and map the fields to the table if needed. Toad will pick an Excel file to use as an example. The rest of the steps should be similar to Robert's video.

    You can choose a target database, select an existing table and map the fields to the table.

  5. This will complete the export of files to one table. If you need other files to go to another table, click on Add Folder again. Otherwise, click on next.

    Add the import source, click next.

  6. You can run the imports now if you want, but remember to save a template so that you can reuse these settings again.

    Don’t forget to save a template so that you can reuse these settings again.

Automating the Import Wizard: upload the contents of the Excel files in that folder to table, then delete the files from the upload folder

Even with the saved *.tim template, I have to open template in Toad, and it opens the Import Wizard, then I click on Next, Next, Next to run it. Can I automated it to run by itself? Yes!

In this video, Toad Data Point Automation Series: Blog #6 – Using the Export Wizard, Debbie Peabody goes over automating the Export wizard, but it's pretty much the same steps to automate the Import Wizard.

  1. Here's a screenshot of an import. I select import wizard for my automation job, then load up the .tim file I created earlier. Click on Run to run it now, or schedule to have it run regularly.

    Pictured is a screenshot of an import. Select import wizard for the automation job, then load up the .tim file that was created earlier. Click on Run to run it now, or schedule to have it run regularly.


  2. You may also want to create a one line batch file, which deletes all the files after you've completed the import. Knowing a little scripting opens up a whole new level of automation

    You can use script to create a one line batch file, which deletes all the files after you’ve completed the import.


  3. Add the batch file to the automation if you want.

    3.	Add the batch file to the automation if you want.

    This automation will upload the contents of the Excel files in that folder to table, then delete the files from the upload folder.

    Automate Excel file download from FTP site to your computer then upload and import multiple Excel files

    Ok, now, that's fine for a local folder, but what if my customers upload Excel files to an FTP site? Check out this video from Debbie on that exact topic, Automate FTP upload and download in Toad Data Point. You can set up an automation that downloads files from the FTP site to a folder on your computer, and then run the import wizard folder option on it, similar to my screenshots above.

    Alternatively, UNC file paths are supported in the Toad Data Point automation scripts. So, if the FTP site is available to your computer through UNC file paths, you can access the folder over LAN instead of FTP. You may want to add a batch file prior to the import, like the following:

     If the FTP site is available to your computer through UNC file paths, you can access the folder over LAN instead of FTP. You may want to add a batch file prior to the import.

    You may need to do some advanced security commands to get through firewalls and provide authentication.

     

    Using Data Compare and Sync to update tables

    Now, what if I don't want to just append data to an existing table, but I want to run an update, which may include DML like update, delete, inserts? The Import Wizard only appends to existing table or creates new tables.

    Well, this is a great use case for the Data Compare and Sync features in Toad Data Point. Check out this video from Amit Parikh and Kuljit Singh on that, Toad Data Point Cross-Platform Compare and Synch.

    Like the Import Wizard, the Data Compare and Sync can be configured to run using the Toad Data Point Automation designer.

     

    Putting it all together: Try this challenge task 

    Now let's combine all of these items! I've given you the building blocks. I challenge you to take what you learned here and build out this task. The scenario again:

    Your customers upload Excel data files to your company's FTP site. The files can be different names, have different time stamps, but generally have the same kind of data within them. You need to take this data and update the existing table in a reporting database. Your company has grown so fast, that it's impossible to manually do these imports and updates anymore. You want to be able to automate importing these files into a reporting database every night.

    1. First use the Toad Data Point Automation to FTP to the site and download the files to a folder.

     

    1. Next use the Toad Data Point Import Wizard to create a job that selects the Excel files and inserts the data into a table in Local Storage. Save this as a template and use it as the next step in Automation.

     

    1. Then, in the Automation, add a data compare that compares the staging table to the target table. Create a sync script from here.

     

    1. Lastly, take the sync script and execute it in the Toad Data Point Automation. Schedule this to run.

     

    1. Bonus, set up email alerts to tell you when the automation is complete or if there were any issues.

      Automation Script_1.tas. Toad Automation Script.

     

    Hopefully with the skills and techniques you've learned in this blog post, you can now import Excel data into your database in batches and automate the process so you can focus on other tasks.


Questions?

I hope this blog is useful to you. If you have any questions, please post questions to the Toad Data Point forum on Toad World.

 

Related links:

 

How to get the most out of Toad Data Point

Learn how Toad Data Point can help you access and prepare data faster. Seamlessly access more than 50 data sources—both on premises and in the cloud—and switch between these data source with near zero transition times.

Whether you are currently a Toad Data Point customer or just getting started with our free 30-day trial, learn more and access the Toad Data Point User Guide.

If Toad Data Point is helping you connect, query and prepare data for faster business insights, buy it now or contact a sales representative.

 

 

Help your peers and share this blog

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!

 

 

Tags: Toad Data Point Automation Excel Import

Mathew Phan

Written by Mathew Phan

Mathew Phan began his career as a technical support engineer for Toad for Oracle soon after graduating from the University of California, Irvine, where he obtained a bachelor's degree in computer and information science. Mat has devoted his entire 14-year career with the Toad family of database solutions and held various positions helping to shape the direction and growth of Toad. Early in his tenure, his focus was on Oracle databases from a developer point of view. Now as a systems consultant, he continues to work with customers to build effective solutions that meet their needs.