Feeding your database: Techniques for building automated Excel imports

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.

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.
   
   
2. 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.
   
   
   
   
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.
   
   
   
   
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.
   
   
   
   
6. You can run the imports now if you want, but remember 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.
   
   
   
   
   
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
   
   
   
   
   
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:

    
   
   

   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.

    

   2. 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.

    

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

    

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

    

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

    

   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:

• Extend your Toad Data Point free trial from 30 days to 90 days
• Video: Toad Data Point Import Wizard
• Video: Toad Data Point Automation Series: Blog #6 – Using the Export Wizard
• Video: Automate FTP upload and download in Toad Data Point
• Video: Toad Data Point Cross-Platform Compare and Synch
• New to Toad Data Point? Get started fast with these Toad Data Point resources:
  • Watch this Data Preparation Made Easy webinar series. 
  • Blog: Guide to using Toad Data Point Workbook

Nikki to blue box text below

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!