0

I created a CSV File that looks like this

sequence,reportsource,reportname,reportid,fullpath

It contains several lines of configuration data for the scheduled tasks. I already figured out to use import-csv to get the content of the file. Now I want to loop through every line of the file and store all the values of the line to variables - collection ($sequence,$reportsource,$reportname,$reportid,$fullpath), so I can create scheduled tasks with this values, but I don't know how to access the object, that import-csv provides. I need those values from the csv to hand over arguments to schtasks.exe to create my tasks.

$path = "C:\Workspace\macros\FullReportPathMR.csv"
$PSVersionTable.PSVersion

$csv = Import-CSV -Path $path -Delimiter ";" | %{

##SCHTASKS /Create /TN $($_.reportname) /sc monthly /tr "C:\Program Files (x86)\Microsoft Office###\Office14\EXCEL.EXE"
#New-ScheduledTaskAction -Execute "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" -Argument #"$($_.fullpath)"
#}

# The name of the scheduled task
[string]$TaskName = "$($_.reportsource) - $($_.reportname) - $($_.reportid)"
# The description of the task
[string]$TaskDescr = "Hello, it's you again! I am $($_.reportname) and I will get started, when Report ID $($_.reportid) is fired. Have a nice day!"
# The Task Action command
$TaskCommand0 = "kill.cmd"
$TaskCommand1 = "`"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE`""

# The Task Action command argument
$TaskArg = "hallelujah"
$TaskArg1 = "$($_.fullpath)"
# attach the Task Scheduler com object
$service = new-object -ComObject("Schedule.Service")
# connect to the local machine. 
# http://msdn.microsoft.com/en-us/library/windows/desktop/aa381833(v=vs.85).aspx
$service.Connect()
$rootFolder = $service.GetFolder("\")

$TaskDefinition = $service.NewTask(0) 
$TaskDefinition.RegistrationInfo.Description = "$TaskDescr"
$TaskDefinition.Settings.Enabled = $true
$TaskDefinition.Settings.AllowDemandStart = $true

$triggers = $TaskDefinition.Triggers
#http://msdn.microsoft.com/en-us/library/windows/desktop/aa383915(v=vs.85).aspx
$trigger = $triggers.Create(0) # Creates an "On an event" trigger
$trigger.Subscription = "<QueryList><Query Id='0'><Select Path='Application'>*[System[Provider[@Name='$($_.reportsource)'] and EventID='$($_.reportid)']]</Select></Query></QueryList>"

# http://msdn.microsoft.com/en-us/library/windows/desktop/aa381841(v=vs.85).aspx
$Action = $TaskDefinition.Actions.Create(0)
$action.Path = "$TaskCommand0"
$action.Arguments = "$TaskArg"
$Action = $TaskDefinition.Actions.Create(0)
$action.Path = "$TaskCommand1"
$action.Arguments = "$TaskArg1"

#http://msdn.microsoft.com/en-us/library/windows/desktop/aa381365(v=vs.85).aspx
$rootFolder.RegisterTaskDefinition("$TaskName",$TaskDefinition,6,"System",$null,5)

Write-Host $($_.sequence) $($_.reportsource) $($_.reportname) $($_.reportid) $($_.fullpath) "task created successfully."

}

CSV File Contains

sequence;reportsource;reportname;reportid;fullpath
1;Monthly Report;MR1;3;C:\Workspace\macros\1.txt
2;Monthly Report;MR2;6;C:\Workspace\macros\2.txt
3;Monthly Report;MR3;9;C:\Workspace\macros\3.txt
4;Monthly Report;MR4;12;C:\Workspace\macros\4.txt

https://www.verboon.info/2013/12/powershell-creating-scheduled-tasks-with-powershell-version-3/

https://community.spiceworks.com/topic/1028906-trigger-schedule-task-on-an-eventid

https://powershell.org/forums/topic/scheduled-task-with-multiple-actions/

The whole thing ran through on a Windows 7 with PS 2.0.-1.-1 administration machine and created 4 tasks with the desired parameters and arguments. The subscription - thing is a bit tricky, since I can't manipulate the event trigger using the GUI, just editing the XML. Every task provides 2 actions.

Improve this question
6
  • 1
    Please show some code. We can help you but only if we have something to go on. Commented Oct 11, 2018 at 16:20
  • I have no access to my code by now but the issue kept spinning in my head. I will provide asap :) Commented Oct 11, 2018 at 16:40
  • 2
    Please post edits into your post not in comments. This is for ease to read and other to help. Also please ether reply to my comment or the title post not to this comment as it belongs to Ortolar Commented Oct 11, 2018 at 19:17
  • Updated my post with working code for you Commented Oct 11, 2018 at 19:28
  • very nice, thank you Commented Oct 11, 2018 at 19:39

2 Answers 2

Reset to default
2

OK the question looks like it could just be simply

"How do I store the values of a CSV in a variable and loop through them?"

Lets go over some basic Powershell concepts to understand what you should be looking for.

The solution is.

Import-Csv -Path "C:\Test.csv" | %{
    Put Code Here
}

Ok lets explain this.

Lets image we have a csv called test.csv and the inside looks like

"FirstName","LastName","Age" "Bill","Boxer","52" "Steve","Nix","21"

When I call Import-CSV it creates a PSObject with properties that are equal to the header (First line of the CSV) :

(Import-csv "C:\Test.csv").FirstName

will return

Bill Steve

In powershell we love piping | What this does is takes the information for the previous command and moves it to the next command using $_ as the variable. We also love shorthand known as Alias. Like %{ Code Here } is short hand for foreach-object

so 

Import-csv "C:\Test.csv" | %{
    "My Name Is $($_.FirstName) $($_.LastName) and I am $($_.Age) years old"
}

Will get the data from the CSV and convert it to a PSObject. It will then Pipe | to a Foreach-Object aka %{} and then display the string :

My Name Is Bill Boxer and I am 52 years old My Name Is Steve Nix and I am 21 years old

On a side note the $() inside the quotes is called a expression "$(Code here)" Allows you to output the code inside of a string

"Hello $_.FirstName"

Would show everything in the variable $_ then add a .FirstName to the end of it like :

@{FirstName=Bill; LastName=Boxer; Age=52}.FirstName

But when you add the $() expression you are saying run this part as code.

"Hello $($_.FirstName)"

Shows

Hello Bill

So lets take a look at the code you posted in the comments (Please keep in mind to edit your post and post it there for other people to read.

Based on what I have posted above the issue is that your CSV isnt a CSV. CSV stands for Comma Separated Value. You have your values separated by Semi Colons.

You will need to define that in the Param for import-Csv -Delimiter ";"

A working script would be this :

$path = "C:\Workspace\macros\FullReportPathMR.csv"
Import-CSV -Path $path -Delimiter ";" | %{
    "$($_.sequence) $($_.reportsource) $($_.reportname) $($_.reportid) $($_.fullpath)"
}

output

1 Monthly Report MR1 3 C:\Workspace\macros\1.txt 2 Monthly Report MR2 6 C:\Workspace\macros\2.txt 3 Monthly Report MR3 9 C:\Workspace\macros\3.txt 4 Monthly Report MR4 12 C:\Workspace\macros\4.txt

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

hell yeah, the first time ever I get an output!! Nice! I needed to provide the delimiter, OK. So now I can hand over the "variables" to schtasks. But this I will do on Monday
please mark my answer as correct, Or upvote it. Thank you
Thank you for your very detailed answer!! I already upvoted. If this thing does, what I am expecting, I will mark as answer. Thank you!!
OK, lets consider this: there is a loop, and with each iteration, another task is created using schtasks, where $($_.sequence) and the other variables contain the value I need. How can I make sure that the correct value is handed over to it, since there are 2 loops? I need to ensure that the index of the first loop (which holds the counting variable $i) references to the corresponding line of the csv?
I think you might need to open another question for that. This was about how to loop a single CSV. I would be glad to help you more but i need a new question with a example code. But just so you know this is powershell which if you read my whole post would points to the use of Piping and passing objects in pipes. the idea of [i] does not have a place in a foreach-object
0

The code below will load a CSV where the code is located.
Than it will loop inside of the file and create a schedule task.
This script requires admin rights to create a new scheduled task and it will create a task as the current user.

# get scripth path location
$scriptpath = Split-Path -Parent -Path $MyInvocation.MyCommand.Definition

# import CSV
$csvdata = Import-Csv "$scriptpath\t.csv"

# this will clycle throught the data inside the object $csvdata
foreach ($result in $csvdata) {
    # to access the data just refer to object $result.[columns names inside CSV]

    # Build task info
    $action  = New-ScheduledTaskAction -Execute "$($result.fullpath)" -Argument 'case there is any argument'
    $trigger = New-ScheduledTaskTrigger -Daily -At 9am 
    Register-ScheduledTask -Action $action -Trigger $trigger -TaskName $($result.reportname) -Description $($result.reportname)
    # create the task scheduler
    New-ScheduledTaskAction -Execute 
}

The task creation part was base on: https://blogs.technet.microsoft.com/heyscriptingguy/2015/01/13/use-powershell-to-create-scheduled-tasks/

Improve this answer

4 Comments

Its awesome you are getting into powershell. But keep in mind that the solution here is vague and it seems the user cant do simple things like parse data in a CSV. Also looking at this script you should look more into piping |. Alot of these commands could be chained togather to make more effective and easier to read scripts.
But how does the powershell for $result know that it is a result? Where is it declared?
RWaternelon. It sounds like you are very new to scripting. You should take a read at the response i provided and then ask question from there. As for $Result thats a foreach statement which is iterateing over a psobject to get results one at a time.
@ortolar: thanks for your input, but it doesn't work using powershell commands. I will try to make it with schtasks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.