Toad World® Forums

Weekly Automation Script

Hi,

Running in SQL Server Env.

Using TOAD Data Point 3.6.

How feasible is the following?

Once a week, I want to look in to a given Microsoft Shared Folder and process the latest file (Excel Workbook).

Processing Steps - Once Excel Workbook is detected

A) Truncate Table_X

B) Import Excel Workbook into Table_X

C) Execute SQL script to update Reporting_Period column in Table_X

D) Execute SQL script to do ETL processing on Table_X

Can I use TOAD to detect the presence of a new weekly excel workbook?

Thanks,

Rick

I presume we’re talking about Automation. if this is the case then the answer is: Yes, you can. You might want to create a batch file with ‘if exist’ command. This batch file should produce non-zero exit code if file isn’t found. Run this batch file with Run activity. If file exists, then its Run_1_RCODE variable will contain 0. You can check its value in If…Else activity and decide if you need to continue processing your data.

In Run activity returns non-zero exit code it will produce an error you can ignore by pressing Continue On Error in the Settings activity.

I attached an example script as well as a batch file you can use as a starting point for your script. .bat extension is not allowed to upload so I changed it to txt. Please rename it back to .bat when you download them.

Thanks,

Igor.

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/36/4863.Script_5F00_1.tas:550:0]

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/36/3480.IfXlsExists.bat.txt:550:0]

Yep, Talking about an Automation script.

Thank You

Hi Igor,

Your response helps a lot.

Another question or two.

If I develop a Windows Batch script to look in a specific Shared Folder for the existence of a file (file name changes).

How do I pass that file name to my TOAD Automation script?

Once I get that file name in to the Automation script I will then Import the file using the Import Wizard Activity.

Once I complete all my Automation Activities I will then execute a Run Program Activity to execute a Windows Batch script to move the file to a “Processed Shared
Folder”.

Thanks,

Rick

Rick Girardi

Business Intelligence Analyst

Enterprise Risk Management

Wilbur-Ellis Company

345 California Street, 27th Floor

San Francisco, CA 94104

Office: 415-772-4029

Fax: 415-772-4011

rgirardi@wilburellis.com

From: Igor Manokhin [mailto:bounce-IgorM@toadworld.com]

Sent: Tuesday, November 04, 2014 12:00 PM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] Weekly Automation Script

RE: Weekly Automation Script

Reply by Igor Manokhin

I presume we’re talking about Automation. if this is the case then the answer is: Yes, you can. You might want to create a batch file with ‘if exist’ command. This batch file should produce non-zero
exit code if file isn’t found. Run this batch file with Run activity. If file exists, then its Run_1_RCODE variable will contain 0. You can check its value in If…Else activity and decide if you need to continue processing your data.

In Run activity returns non-zero exit code it will produce an error you can ignore by pressing Continue On Error in the Settings activity.

I attached an example script as well as a batch file you can use as a starting point for your script. .bat extension is not allowed to upload so I changed it to txt. Please rename it back to .bat
when you download them.

Thanks,

Igor.

http://www.toadworld.com/cfs-file.ashx/__key/communityserver-discussions-components-files/36/0753.IfXlsExists.bat.txt

http://www.toadworld.com/cfs-file.ashx/__key/communityserver-discussions-components-files/36/3480.Script_5F00_1.tas

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

How does your file name change? Is it the same name with todays date or timestamp appended? If the file name is something like StandardName20141106_0830.xls you can use pattern matching. Like if exsist StandardName%1*.xls. %1 would be the 1st variable that you put on the argument line in the Toad RunProgram step. In a prior step you would create and populate the value of that variable. You would probably also need a generic named file say CurrentFile.xls to use as the import file and a step to copy StandardName%1*.xls to CurrentFile.xls each day before importing (the import template needs the same name each time).

If you have multiple files or totaly non standard file names there is a way to capture any file names in a folder to a text file. Load the text file into a database table and step through the rows with a Toad Loop and copy, process, and archive each file no matter what the name is.

Hi Greg,

The file will be manually placed in to the Shared folder.

I could have the user name the file as – MM-DD-YYYY

Or use the original file from the user which is a unique file name.

What TOAD Data Point Activity allows me to capture the file name from the Windows Batch process and get it back in to the TOAD Data Point Automation script?

In reading you email … looks like I need to use Toad RunProgram.

Thanks,

Rick

Rick Girardi

Business Intelligence Analyst

Enterprise Risk Management

Wilbur-Ellis Company

345 California Street, 27th Floor

San Francisco, CA 94104

Office: 415-772-4029

Fax: 415-772-4011

rgirardi@wilburellis.com

From: GregDavis11009 [mailto:bounce-GregDavis11009@toadworld.com]

Sent: Thursday, November 06, 2014 8:03 AM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] Weekly Automation Script

RE: Weekly Automation Script

Reply by GregDavis11009

How does your file name change? Is it the same name with todays date or timestamp appended? If the file name is something like StandardName20141106_0830.xls you can use pattern matching. Like
if exsist StandardName%1*.xls. %1 would be the 1st variable that you put on the argument line in the Toad RunProgram step. In a prior step you would create and populate the value of that variable. You would probably also need a generic named file say CurrentFile.xls
to use as the import file and a step to copy StandardName%1*.xls to CurrentFile.xls each day before importing (the import template needs the same name each time).

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

Rick,

You will need RunProgram to call the batch file (.bat file). Try to avoid having spaces in file names that you want to pass as a variable to a batch file as it will interpret a space as a delimiter for the next passed variable and split your variables value into two separate variables. To get the list of files in a folder use Dir > filename.txt in your batch file (you can include the path in front of the file name. Then you can use an import step to load the text file rows into a database table. That will allow you to run sql against that table to substring out the file name of rows where the string contains .xls. You can then put that subsring file name into a variable. That will let you copy the random file name to a genenric file name that your import wizzard is looking for. Putting this inside of a Toad loop function will allow you to do this for each .xls file that it finds. If memory serves me correct the file name should start at possition 40 of the text string form the Dir > filename.txt

I am much lazier than you guys. I would just use the "Add Folder" feature of the Import Wizard. Here you can point to a directory and build a filter. For every file fitting that filter it will import it. This filter can match on name, date range, or file type.

If you deliver multiple files or completely non standard file names there is a path to get any file names in a folder to a text file.

Debbie,

I can’t believe I never looked at that button before. That could have saved me a lot of time.

Rick,

You can use that to get any file and then use the copy command to archive the files in that folder when you are done. You just need to make sure the user does not change the format of the file.

Thanks Everyone

I will give it a try.

Rick

Rick Girardi

Business Intelligence Analyst

Enterprise Risk Management

Wilbur-Ellis Company

345 California Street, 27th Floor

San Francisco, CA 94104

Office: 415-772-4029

Fax: 415-772-4011

rgirardi@wilburellis.com

From: GregDavis11009 [mailto:bounce-GregDavis11009@toadworld.com]

Sent: Friday, November 07, 2014 7:29 AM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] Weekly Automation Script

RE: Weekly Automation Script

Reply by GregDavis11009

Debbie,

I can’t believe I never looked at that button before. That could have saved me a lot of time.

Rick,

You can use that to get any file and then use the copy command to archive the files in that folder when you are done. You just need to make sure the user does not change the format of the file.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

Hi,

While going thru the Import Wizard after I select “Add Folder”.

I’m on the Select Target screen.

I’m only able to see one of my Database names in the Database drop down.

Why would that be?

Thanks,

Rick

Rick Girardi

Business Intelligence Analyst

Enterprise Risk Management

Wilbur-Ellis Company

345 California Street, 27th Floor

San Francisco, CA 94104

Office: 415-772-4029

Fax: 415-772-4011

rgirardi@wilburellis.com

From: GregDavis11009 [mailto:bounce-GregDavis11009@toadworld.com]

Sent: Friday, November 07, 2014 7:29 AM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] Weekly Automation Script

RE: Weekly Automation Script

Reply by GregDavis11009

Debbie,

I can’t believe I never looked at that button before. That could have saved me a lot of time.

Rick,

You can use that to get any file and then use the copy command to archive the files in that folder when you are done. You just need to make sure the user does not change the format of the file.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

What type of database are you connecting to? and how are you connected? IE: If Oracle are you using OCI client, direct connect or ODBC?

Hi Debbie,

SQL Server

Looks like I fixed it … I went to the main TOAD Data Point window and of the left hand side where the database icon and name is.

I selected the right drop down and select .

Thanks,

Rick

Rick Girardi

Business Intelligence Analyst

Enterprise Risk Management

Wilbur-Ellis Company

345 California Street, 27th Floor

San Francisco, CA 94104

Office: 415-772-4029

Fax: 415-772-4011

rgirardi@wilburellis.com

From: Debbie Peabody [mailto:bounce-Debbie_Peabody@toadworld.com]

Sent: Friday, November 07, 2014 8:01 AM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] Weekly Automation Script

RE: Weekly Automation Script

Reply by Debbie Peabody

What type of database are you connecting to? and how are you connected? IE: If Oracle are you using OCI client, direct connect or ODBC?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

It sounds like you had a filter defined on the Object Explorer. The import wizard uses that filter. However, there is a filter icon in the import wizard on the page you mention where you can change the filter for that import without having to set the Object Explorer to