Network Automation Software We are currently migrating this forum
over to our HelpSystems domain. Please
post all new threads in our new
HelpSystems Community Portal.
Post to the HelpSystems Forum
You are not currently logged on. You must be logged on in order to post. Log on
Or Create a new account
AutoMate Discussion
Decrease font size
Increase font size
Topic Title: Dynamic Dataset field names
Topic Summary: Pass Column names dynamically to "write to file" task
Created On: 09/25/2017 09:47 AM
Status: Read Only
Linear : Threading : Single : Branch
Search Topic Search Topic
Topic Tools Topic Tools
View similar topics View similar topics
View topic in raw text format. Print this topic.
Answer This question was answered by BrandonPoddany, on Tuesday, September 26, 2017 1:47 PM

Answer:

Hello, 

I was able to get the desitred result by adding an If statement that evauluated the 3rd column for 'Null" and wrote to the file if the column did not contain "null" and skipped the value in column 3 if it contained "null". 

Thank you for your help. 

 

 09/25/2017 09:47 AM
User is offline View Users Profile Print this message

Author Icon
BrandonPoddany
n00b (>6 posts)

Posts: 4
Joined: 09/25/2017

I am attempting to write data to a csv file from a MS SQL stored procedure. The result set of the ST contains 3 columns (Hour, mm/dd/yyyy, mm/dd/yyyy) where column 2's value is yesterday, and column 3's value is today. And 24 data rows (Hour [1] - [24]). 

All is well with the loop> write data to file task if column 2 and 3 are referenced manual (i.e. write data to file > %csvSet.Hour%,%csvSet.[9/24/2017]%,%csvSet.[09/25/2017]%).

However, my job runs once an hour essentially adding the most recent hour's data into col 3' next row. When the job hits midnight and today becomes yesterday and tomorrow becomes today (Hour, 09/24/2017, 09/25/2017 becomes Hour, 09/25/2017, 09/26/2017) the write step errors with an error stating "[Main](Step 36) Expression evaluation failed: "A variable or function in the expression does not exist or is misspelled " Obviously this error makes sense because the column names have changed. 

I have not had success passing the dataset field names (column names) dynamically to the write data to file step. I would be grateful for any assistance that could be provided. 

 

Stored procedure and srite data to file code attached. Thanks. 

 

 



 Category Survey
AutoMate 9 version: 9.0.0
Windows version: Windows Server 2012 R2



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 09/25/2017 12:12 PM
User is offline View Users Profile Print this message

Author Icon
Devin Cannon
HelpSystems

Posts: 398
Joined: 03/17/2016

Hi Brandon,

You can use the "GetVarValueByVarName" function.  Basically you have to get "csv.Set[09/24/2017]" into a variable. You can then use the "GetVarValueByVarName" function to get the value from the variable "csv.Set[09/24/2017]".  I hope that makes sense.  Please see the steps below.



 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


-------------------------

Devin Cannon | Support Analyst
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700 | F: +1.213.738.7665
 09/25/2017 01:14 PM
User is offline View Users Profile Print this message

Author Icon
BrandonPoddany
n00b (>6 posts)

Posts: 4
Joined: 09/25/2017

Devin! You're a hero! Your suggestion works like a charm! Thank you!

I have a follow-up question: How to pass 'Null' values as blanks?

How might replace a "null" value (for the hours of today that have not happend yet) with a blank? 

Instead of:

Hour,9/24/2017,9/25/2017
1,15587,16727
2,14682,15800
3,14169,15188
4,13934,15223
5,14000,16376
6,14526,18598
7,14823,20118
8,16051,21966
9,18155,23553
10,20157,25654
11,22141,27541
12,24617,29371
13,26460,30656
14,27825,31814
15,28934,Null
16,29645,Null
17,29794,Null
18,29037,Null
19,28371,Null
20,27380,Null
21,25235,Null
22,22861,Null
23,20195,Null
24,18144,Null

write as:

Hour,9/24/2017,9/25/2017
1,15587,16727
2,14682,15800
3,14169,15188
4,13934,15223
5,14000,16376
6,14526,18598
7,14823,20118
8,16051,21966
9,18155,23553
10,20157,25654
11,22141,27541
12,24617,29371
13,26460,30656
14,27825,31814
15,28934,
16,29645,
17,29794,
18,29037,
19,28371,
20,27380,
21,25235,
22,22861,
23,20195,
24,18144,

Thanks again Devin!! 


 Category Survey
AutoMate 9 version: 9.0.0
Windows version: Windows Server 2012 R2
 09/25/2017 02:26 PM
User is offline View Users Profile Print this message

Author Icon
Devin Cannon
HelpSystems

Posts: 398
Joined: 03/17/2016

Hi Brandon,

You can use a "Text: Replace" action and replace "Null" with nothing.



 Category Survey



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


-------------------------

Devin Cannon | Support Analyst
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700 | F: +1.213.738.7665
 09/26/2017 07:43 AM
User is offline View Users Profile Print this message

Author Icon
BrandonPoddany
n00b (>6 posts)

Posts: 4
Joined: 09/25/2017

Thanks again Devin for the reply. 

I'm having some issues executing your suggestion. I've tried to point the Test:replace action to the file the previous steps create but no changes are made to the file. I have also tried to change the advanced settings in the Write the data to file step to replace "null" with null (nothing). This does remove the word Null but the ftp file that is uploaded after this step does not open within the stp site as it should. Instead the file is downloaded as a Microsoft Excel csv. 

Where and how should I implement your suggestion to get this working. I have included the entire code for your reference. 

Thank you. I'm looking forward to hearing from you soon!

- Brandon



 Category Survey
AutoMate 9 version: 9.0.0
Windows version: Windows Server 2012 R2



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 09/26/2017 01:47 PM
User is offline View Users Profile Print this message

Author Icon
BrandonPoddany
n00b (>6 posts)

Posts: 4
Joined: 09/25/2017

Answer Answer

Hello, 

I was able to get the desitred result by adding an If statement that evauluated the 3rd column for 'Null" and wrote to the file if the column did not contain "null" and skipped the value in column 3 if it contained "null". 

Thank you for your help. 

 



 Category Survey
AutoMate 9 version: 9.0.0
Windows version: Windows Server 2012 R2



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
Statistics
18258 users are registered to the AutoMate Discussion forum.
There are currently 0 users logged in.
The most users ever online was 5551 on 01/08/2018 at 11:11 AM.
There are currently 1088 guests browsing this forum, which makes a total of 1088 users using this forum.

FuseTalk Enterprise Edition v4.0 - © 1999-2020 FuseTalk Inc. All rights reserved.

Sitemap Network Automation Software Blog