Matt Bloomfield

Forum Replies Created

Viewing 15 posts - 361 through 375 (of 429 total)
  • Author
    Posts
  • in reply to: Excel com objects for refreshing data and saving as CSV #33076
    Participant
    Topics: 4
    Replies: 429
    Points: 770
    Helping Hand
    Rank: 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:

    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.

    in reply to: Excel com objects for refreshing data and saving as CSV #33064
    Participant
    Topics: 4
    Replies: 429
    Points: 770
    Helping Hand
    Rank: 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.

    Participant
    Topics: 4
    Replies: 429
    Points: 770
    Helping Hand
    Rank: 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.

    in reply to: Including .ALIASES within my help without breaking it. #33032
    Participant
    Topics: 4
    Replies: 429
    Points: 770
    Helping Hand
    Rank: 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.

    in reply to: Repair-clustersharedvolume #32978
    Participant
    Topics: 4
    Replies: 429
    Points: 770
    Helping Hand
    Rank: 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:

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

    in reply to: understanding the use of LDAPFilter #32959
    Participant
    Topics: 4
    Replies: 429
    Points: 770
    Helping Hand
    Rank: 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

    in reply to: Powershell version 5's integraion with Excel #32887
    Participant
    Topics: 4
    Replies: 429
    Points: 770
    Helping Hand
    Rank: 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

    in reply to: MySQL update on duplicate key #32867
    Participant
    Topics: 4
    Replies: 429
    Points: 770
    Helping Hand
    Rank: 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 `

    in reply to: parse csv files with posh #32723
    Participant
    Topics: 4
    Replies: 429
    Points: 770
    Helping Hand
    Rank: 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.

    in reply to: parse csv files with posh #32717
    Participant
    Topics: 4
    Replies: 429
    Points: 770
    Helping Hand
    Rank: 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

    in reply to: Wait for input, then timeout to default #32685
    Participant
    Topics: 4
    Replies: 429
    Points: 770
    Helping Hand
    Rank: 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.

    in reply to: Automating IE with PowerShell #32639
    Participant
    Topics: 4
    Replies: 429
    Points: 770
    Helping Hand
    Rank: 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?

    in reply to: Cant get script to run query against specific data base??? #32638
    Participant
    Topics: 4
    Replies: 429
    Points: 770
    Helping Hand
    Rank: 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?

    in reply to: Graph shows too much data in the Legend #32536
    Participant
    Topics: 4
    Replies: 429
    Points: 770
    Helping Hand
    Rank: 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.

    in reply to: Graph shows too much data in the Legend #32511
    Participant
    Topics: 4
    Replies: 429
    Points: 770
    Helping Hand
    Rank: 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:

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

    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.

Viewing 15 posts - 361 through 375 (of 429 total)