Weekly Automation Script

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

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

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

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

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

GregDavis11009 · November 6, 2014, 8:02am

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

GregDavis11009 · November 6, 2014, 8:08am

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.

rgirardi_4519 · November 6, 2014, 8:29am

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

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

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

Subject: RE: [Toad Data Point - Discussion Forum] 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.

GregDavis11009 · November 6, 2014, 9:46am

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

Debbie_Peabody · November 6, 2014, 7:02pm

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.

clippingpath · November 7, 2014, 2:09am

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.

GregDavis11009 · November 7, 2014, 7:29am

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.

rgirardi_4519 · November 7, 2014, 7:32am

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

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

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

Subject: RE: [Toad Data Point - Discussion Forum] 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.

rgirardi_4519 · November 7, 2014, 7:54am

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

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

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

Subject: RE: [Toad Data Point - Discussion Forum] 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.

Debbie_Peabody · November 7, 2014, 8:00am

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?

rgirardi_4519 · November 7, 2014, 8:05am

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

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

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

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