Accessing New Csv in Automation to create a "Master Table"

Toad Data Point
1

I'm new to toad DP and I would like to know if what I want to do is possible in automation and if so how one would go about doing it.

We get new csv's for a single dataset often and right now we manually import the csv and manually combine it to a "Master Table" that has all the previous csv's combined.

I was wondering if in toad automation we could handle this, I've been trying by creating an exe to get the file path to any new csv that was added to the directory. But now I don't know what can be done, in theory I would like to import the new file and run an SQL script and export that result as a new csv.

2

Your requirement to take in a (new) CSV file and append its contents to an existing master table can be easily done. The hard part here might be how you let TDP know about any "new" CSV files, especially if they don't "land" in the same saving location, presumably on the OS file system.

If the CSV files always have the same file names, and new CSV files replace the old CSV files, then this can be automated in one step. You would basically test your CSV import/s by using the Import wizard to append the CSV contents to your master table, making sure that you save the Export Template file at the end of the wizard. Then, in the Automation Designer, drag the Import task into your automation flow and use that same Import template. You can even schedule the automation job to run at specific times, if appropriate.

If the CSV file names change, or their creation times are ad hoc, or the number of files change from time to time, this gets more complicated. Easiest thing to do may be to create some batch logic on the OS side to create a staging file location, and move any new CSV files to that area, always using consistent CSV file names so that the Import Wizard task can point to these same files from run to run.

While it is possible to add more sophisticated logic to the automation flow to recognize variable file names, etc., it's best to try to keep the logic as simple as possible.

For example, next easiest solution to try might be to define an automation job that

  • Checks for new CSV files in a consistent location
  • IF no new files are present, then end the automation
    ELSE execute your canned Export task,
  • Upon successful import into your Master table, delete or archive the existing CSV files.

Hope this gives you some ideas.