Matt Bloomfield

[Matt Bloomfield Participant Topics: 4 Replies: 429 Points: 770Rank: Major Contributor]

OK, you won’t be able to refresh using the Excel Module so the COM object is your only option. The following is tested with Excel 2013 and should meet your requirements:

PowerShell

#Get all the spreadsheets. $files = Get-ChildItem *.xlsx -Recurse #Create Excel COM object. $xl = New-Object -ComObject Excel.Application foreach ($file in $files) { #Make the filename. $newFile = "$($file.DirectoryName)\$($file.BaseName).csv" #Open the file and refresh it. $wb = $xl.Workbooks.Open($file) $wb.RefreshAll() #Save the file as a CSVfile (xlCSV = 6). $wb.SaveAs("$newFile",6) #Close the workbook, $true will save any changes without a prompt. $wb.Close($true) } #Close the COM object. [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl) | Out-Null

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

#Get all the spreadsheets.   $files = Get-ChildItem *.xlsx -Recurse   #Create Excel COM object.   $xl = New-Object -ComObject Excel.Application   foreach ($file in $files) {       #Make the filename.     $newFile = "$($file.DirectoryName)\$($file.BaseName).csv"          #Open the file and refresh it.     $wb = $xl.Workbooks.Open($file)     $wb.RefreshAll()       #Save the file as a CSVfile (xlCSV = 6).     $wb.SaveAs("$newFile",6)     #Close the workbook, $true will save any changes without a prompt.     $wb.Close($true)       }   #Close the COM object. [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl) | Out-Null

Bear in mind that CSV only supports a single sheet. If you have multiple sheets in a workbook then I’d be looking at using a mixture of the COM object and the Excel module.

[Matt Bloomfield Participant Topics: 4 Replies: 429 Points: 770Rank: Major Contributor]

I agree with Wilfredo, use the Excel module. Something like this is what you’re after although I’m not sure I’ve fully understood your requirement.

PowerShell

#Get all the Excel files in the folder. $excelFiles = Get-ChildItem *.xlsx foreach ($file in $excelFiles) { #Get the subfolder path from the current directory and the name of the file. $subfolder = "$($file.DirectoryName)\$($file.BaseName)" #Create the subfolder if it does not exist. if (-not (Test-Path $subfolder)) { New-Item -Path "$subfolder" -ItemType Directory } #Process each sheet in the file, converting to a CSV file, and save it in the subfolder. ConvertFrom-ExcelSheet -Path $file.FullName -OutputPath $subfolder }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

#Get all the Excel files in the folder. $excelFiles = Get-ChildItem *.xlsx   foreach ($file in $excelFiles) {       #Get the subfolder path from the current directory and the name of the file.     $subfolder = "$($file.DirectoryName)\$($file.BaseName)"       #Create the subfolder if it does not exist.     if (-not (Test-Path $subfolder)) {           New-Item -Path "$subfolder" -ItemType Directory       }          #Process each sheet in the file, converting to a CSV file, and save it in the subfolder.     ConvertFrom-ExcelSheet -Path $file.FullName -OutputPath $subfolder   }

[Matt Bloomfield Participant Topics: 4 Replies: 429 Points: 770Rank: Major Contributor]

You could store the password as an encrypted secure string. This would enable you to store the password as ciphertext rather than plaintext. If you stored it in a text file or registry key you could further secure the file by allowing only the account that’s running the script to read the file/key. Look at the help for ConvertFrom-SecureString for guidance. There are also several step-by-step guides online.

[Matt Bloomfield Participant Topics: 4 Replies: 429 Points: 770Rank: Major Contributor]

.ALIASES isn’t listed in the help file Get-Help about_comment_based_help as a valid keyword.

From testing, this looks like a bug. It appears that any invalid keyword in the help inserts an ALIASES section and remove the examples:

In the ISE add the Cmdlet (advanced function) snippet.
Add .TESTKEYWORD to the help section.
Add Get-Help Verb-Noun -Full to the end of the script.
Run the script.

[Matt Bloomfield Participant Topics: 4 Replies: 429 Points: 770Rank: Major Contributor]

I don’t have anything using CSV to test this with but reading the docs it seems to just pass the command off to defrag.exe, is that correct? If so, do you just get back the output from defrag.exe? Something like this should work:

PowerShell

[int]$fragmentation = (((defrag f: /A | Select-String 'Total fragmented space') -split '=') -replace '%','').trim()[1]

1	[int]$fragmentation = (((defrag f: /A | Select-String 'Total fragmented space') -split '=') -replace '%','').trim()[1]

Select-String gets the relevant line from the output. We split that on the equals sign which gives us a collection containing two strings ‘Total fragmented space’ and ‘n%’. We remove the % sign by replacing it with nothing and then trim the leading space. We then choose the second string from the collection (which has the index 1).

[Matt Bloomfield Participant Topics: 4 Replies: 429 Points: 770Rank: Major Contributor]

In LDAP syntax the comparison operator goes at the start of the clause so, in your, example. The filter translates to “give me all the objects where the operatingSystem attribute is ‘windows 10’ AND the object is a computer object”.

Lots of examples and how to use the syntax here: Active Directory LDAP Syntax Filters

[Matt Bloomfield Participant Topics: 4 Replies: 429 Points: 770Rank: Major Contributor]

I don’t think it does borders yet but have a look at Doug Finke’s Excel module. It makes getting data into and out of Excel very easy.

Introducing the PowerShell Excel Module

[Matt Bloomfield Participant Topics: 4 Replies: 429 Points: 770Rank: Major Contributor]

Looking at the highlighting in the ISE I think it’s the double quote marks that are causing the problem. Try escaping the ” with a backtick `

PowerShell

$query = "INSERT INTO table('host_name','value1','value2','value3','date_entered','host_id') VALUE ('$host','$value1','$value2','$value3', now(),Null) ON DUPLICATE KEY UPDATE (host_name=`"$host`",value1=`"$value1`",value2=`"$value2`",value3=`"$value3`",date_entered=now(),host_id=Null);"

1

$query = "INSERT INTO table('host_name','value1','value2','value3','date_entered','host_id') VALUE ('$host','$value1','$value2','$value3', now(),Null) ON DUPLICATE KEY UPDATE (host_name=`"$host`",value1=`"$value1`",value2=`"$value2`",value3=`"$value3`",date_entered=now(),host_id=Null);"

[Matt Bloomfield Participant Topics: 4 Replies: 429 Points: 770Rank: Major Contributor]

The first example does use SQL server but only because it’s demonstrating how to quickly import a CSV file into SQL. You should find the second script, for finding duplicates, works without any additional configuration which at least proves the concept could work for you.

This method is using the ODBC drivers which are (probably) already installed. It’s effectively treating your CSV file as a SQL database table.

You will need to understand how to use SQL queries and how to call the driver’s properties and methods though – that’s the hard bit.

[Matt Bloomfield Participant Topics: 4 Replies: 429 Points: 770Rank: Major Contributor]

Have a look at Chrissy Le Maire’s blog. She demonstrates several techniques for working with large CSV files:

High-Performance Techniques for Importing CSV to SQL Server using PowerShell

Quickly Find Duplicates in Large CSV Files using PowerShell

[Matt Bloomfield Participant Topics: 4 Replies: 429 Points: 770Rank: Major Contributor]

This was much harder to do than I expected. I read several examples online where people have tried to achieve similar results. Using some of them as inspiration, I have come up with the following which pretty much achieves the objective.

The loop runs until a key is pressed or until the timeout period is reached. If a key is pressed, the key is read and assigned to $response. If no key has been pressed, then $response is assigned a default value. I have just written out $response but it could be passed to a Switch construct to implement menu functionality.

Note: this won’t work in the ISE, only in the PowerShell console.

PowerShell

function Read-KeyOrTimeout { Param( [int]$seconds = 5, [string]$prompt = 'Hit a key', [string]$default = 'A' ) $startTime = Get-Date $timeOut = New-TimeSpan -Seconds $seconds Write-Host $prompt while (-not $host.ui.RawUI.KeyAvailable) { $currentTime = Get-Date if ($currentTime -gt $startTime + $timeOut) { Break } } if ($host.ui.RawUI.KeyAvailable) { [string]$response = ($host.ui.RawUI.ReadKey("IncludeKeyDown,NoEcho")).character } else { $response = $default } Write-Output "You typed $($response.toUpper())" } Read-KeyOrTimeOut

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

function Read-KeyOrTimeout {       Param(            [int]$seconds = 5,         [string]$prompt = 'Hit a key',         [string]$default = 'A'     )       $startTime = Get-Date     $timeOut = New-TimeSpan -Seconds $seconds       Write-Host $prompt       while (-not $host.ui.RawUI.KeyAvailable) {           $currentTime = Get-Date           if ($currentTime -gt $startTime + $timeOut) {               Break              }          }       if ($host.ui.RawUI.KeyAvailable) {           [string]$response = ($host.ui.RawUI.ReadKey("IncludeKeyDown,NoEcho")).character       }       else {              $response = $default       }       Write-Output "You typed $($response.toUpper())"   }   Read-KeyOrTimeOut

[Matt Bloomfield Participant Topics: 4 Replies: 429 Points: 770Rank: Major Contributor]

I was having a think about this and figured that you could probably ‘hit’ F12 with SendKeys. I had a problem changing the focus of the Window but this stackoverflow post helped there. Does this help at all?

PowerShell

Add-Type @" using System; using System.Runtime.InteropServices; public class Tricks { [DllImport("user32.dll")] [return: MarshalAs(UnmanagedType.Bool)] public static extern bool SetForegroundWindow(IntPtr hWnd); } "@ $ie = Start-Process iexplore.exe -PassThru | Get-Process [void][Tricks]::SetForeGroundWindow($ie.MainWindowHandle) Start-Sleep -Seconds 2 [System.Windows.Forms.SendKeys]::SendWait("{F12}")

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Add-Type @"   using System;   using System.Runtime.InteropServices;   public class Tricks {      [DllImport("user32.dll")]      [return: MarshalAs(UnmanagedType.Bool)]      public static extern bool SetForegroundWindow(IntPtr hWnd);   } "@   $ie = Start-Process iexplore.exe -PassThru | Get-Process   [void][Tricks]::SetForeGroundWindow($ie.MainWindowHandle)          Start-Sleep -Seconds 2   [System.Windows.Forms.SendKeys]::SendWait("{F12}")

[Matt Bloomfield Participant Topics: 4 Replies: 429 Points: 770Rank: Major Contributor]

Are you sure the script you’re running is the script you’re editing and that you’re not editing one version and running a different version? The error implies that you have CLS (PowerShell alias for Clear-Host) erroneously appended to the end of line 3.

If you Copy & Paste the script you posted straight from this website into the ISE and hit Run Script, do you still get the error?

[Matt Bloomfield Participant Topics: 4 Replies: 429 Points: 770Rank: Major Contributor]

OK, this took some fiddling to get working as I’m not overly familiar with using charting. However, the docs on MSDN are really good and by looking at the various properties I think I’ve found the solution.
What’s happening in your code is that you’re settting a value (it’s treated as a double) for both the X and Y axis but in your case the value for X isn’t really a value, it’s just a label. What you should be doing, I think, is setting the value for X to 0 and then setting the label on the X-Axis for that datapoint.

Try this as your foreach for the points. I think it will still need some tweaking, for example, you probably don’t want the X-Axis to start at 0 but it’s getting there.

PowerShell

foreach ($point in $points) { $datapoint = New-Object System.Windows.Forms.DataVisualization.Charting.DataPoint(0,$point.UsedSize) $datapoint.AxisLabel = $point.Date $chart.Series[$Aggregate].Points.Add($datapoint) }

1 2 3 4 5 6

foreach ($point in $points)  {     $datapoint = New-Object System.Windows.Forms.DataVisualization.Charting.DataPoint(0,$point.UsedSize)     $datapoint.AxisLabel = $point.Date     $chart.Series[$Aggregate].Points.Add($datapoint) }

[Matt Bloomfield Participant Topics: 4 Replies: 429 Points: 770Rank: Major Contributor]

You’re currently getting all the aggregates and then processing all the aggregates for all the servers. What you want to be doing is getting the aggregates on a per server basis. i.e. within the foreach server loop.

Remove this line from the top of the script:

PowerShell

$Aggregates = $data | select -ExpandProperty Aggregate -Unique | sort

1	$Aggregates = $data | select -ExpandProperty Aggregate -Unique | sort

And add this just before foreach ($aggregate in $aggregates):

PowerShell

$aggregates = $data | Where {$_.Name -eq $server} | Select -ExpandProperty Aggregate -Unique

1	$aggregates = $data | Where {$_.Name -eq $server} | Select -ExpandProperty Aggregate -Unique

This works OK with some dummy data that I setup although I’m not getting any points plotted. Some sample data would be useful if further troubleshooting is required.

[Author]

Posts