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.
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.
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.
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:
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.
This automation will upload the contents of the Excel files in that folder to table, then delete the files from the upload folder.
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.
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.
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.
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.
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.
Nikki to blue box text below
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.
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!
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.