locked
need help on a power shell script to filter with keywords and paste the data in new sheet. RRS feed

  • Question

  • A bit new to power shell but learning it fast, I have downloaded a content from a web url containing servers data like server name, env details, Site address, Country, I want to filter a column headed as "Country" and paste its filtered data in new sheet. So far i have the below script, please assist:-

    Invoke-WebRequest -Uri "URL Address" -OutFile "C:\Users\tarwaniv\Desktop\file1.csv"
    $import = get-content "C:\Users\tarwaniv\Desktop\file1.csv"
    $import | select-Object -Skip 1 | Set-Content "C:\Users\tarwaniv\Desktop\file2.csv"
    Import-csv -Path "C:\Users\tarwaniv\Desktop\file2.csv" -Header "#Active Servers", "Street Address" , "City", "Country"
    @filter = @("#Active Servers", "City", "Country")


    Monday, June 29, 2020 11:31 AM

All replies

  • A bit new to power shell but learning it fast, I have downloaded a content from a web url containing servers data like server name, env details, Site address, Country, I want to filter a column headed as "Country" and paste its filtered data in new sheet. So far i have the below script, please assist:-

    Invoke-WebRequest -Uri "URL Address" -OutFile "C:\Users\tarwaniv\Desktop\file1.csv"
    $import = get-content "C:\Users\tarwaniv\Desktop\file1.csv"
    $import | select-Object -Skip 1 | Set-Content "C:\Users\tarwaniv\Desktop\file2.csv"
    Import-csv -Path "C:\Users\tarwaniv\Desktop\file2.csv" -Header "#Active Servers", "Street Address" , "City", "Country"
    @filter = @("#Active Servers", "City", "Country")

    1 for working with csv you do not need to use get-content but import-csv cmdlet or if you need modify csv somehow before import you do not need todo set-conten. Instead of that you can use ConvertFrom-CSV

    2 not really get what do you mean by 'filter'. If you mean select 1 column from many you need to use select-object with providing column name you want to select. Otherwise if you need get date for some specyfic country from many you need to use Where-Object cmdlet

    Example #1 :

    Invoke-WebRequest -Uri "URL Address" -OutFile "C:\Users\tarwaniv\Desktop\file1.csv"
    get-content "C:\Users\tarwaniv\Desktop\file1.csv" | select-Object -Skip 1 | ConvertFrom-CSV -Header "#Active Servers", "Street Address" , "City", "Country" | Select-Object Country | Export-Csv C:\Tmp\NewCSV.csv

    Example #2 :

    Invoke-WebRequest -Uri "URL Address" -OutFile "C:\Users\tarwaniv\Desktop\file1.csv"
    get-content "C:\Users\tarwaniv\Desktop\file1.csv" | select-Object -Skip 1 | ConvertFrom-CSV -Header "#Active Servers", "Street Address" , "City", "Country" | Where-Object {$_.Country -in @("USA","China","UK")} | Export-Csv C:\Tmp\NewCSV.csv


    The opinion expressed by me is not an official position of Microsoft

    Monday, June 29, 2020 12:24 PM
  • Ty for your response, But i tried both ways and it did not work, I need to filter the country columns with column value names and bring the corresponding server names with their street address and City name with Country in a new csv file. But new csv file comes empty all the time and first example it bring only column of country with errors.

    In last i broke the code to use like below:-

    Invoke-WebRequest -Uri "url address" -OutFile "C:\Users\tarwaniv\Desktop\file1.csv"
    get-content "C:\Users\tarwaniv\Desktop\file1.csv" | select-Object -Skip 1 | set-content "C:\Users\tarwaniv\Desktop\file2.csv"
    get-content "C:\Users\tarwaniv\Desktop\file2.csv" | ConvertFrom-CSV -Header "#Active Servers", "Street Address" , "City", "Country" | Where-Object {$_.Country -in @("ARGENTINA","AUSTRALIA","BELARUS","BELGIUM")}
     | Export-Csv "C:\Users\tarwaniv\Desktop\file4.csv"

    But it brought the empty file4.csv


    Monday, June 29, 2020 1:52 PM
  • show your csv sample

    hard to say what is incorrect without input data

    + please post your code in a code blocks becasue in a text format it hard to read


    The opinion expressed by me is not an official position of Microsoft


    • Edited by Vector BCO Monday, June 29, 2020 1:57 PM
    Monday, June 29, 2020 1:56 PM
  • Used import-csv as well to deal with CSV's but this also brought the empty file4.csv (dont know why):-

    Invoke-WebRequest -Uri "URL Address" -OutFile "C:\Users\tarwaniv\Desktop\file1.csv"
    import-csv "C:\Users\tarwaniv\Desktop\file1.csv" | Select-Object -Skip 1 | Set-Content "C:\Users\tarwaniv\Desktop\file2.csv"
    import-csv "C:\Users\tarwaniv\Desktop\file2.csv" | ConvertFrom-Csv -Header "#Active Servers", "Street Address" , "City", "Country" | Where-Object {$_.Country -in @("ARGENTINA","AUSTRALIA","BELARUS","BELGIUM")}
     | Export-Csv "C:\Users\tarwaniv\Desktop\file4.csv"


    Monday, June 29, 2020 2:00 PM
  • Input as required
    Monday, June 29, 2020 2:20 PM
  • from excel screenshot its a little bit complicated copy test data :)

    + not sure how about you, but i did not see delimiter char

    would be great if you will copy first 4 lines from notepad to codeclock form


    The opinion expressed by me is not an official position of Microsoft

    Monday, June 29, 2020 2:26 PM
  • Code coded form for few lines:-

    ##### GENERATED: 2020-06-29 09:13:00 #####																		
    # Active Servers	Scope	Scope Changed	Discovered Dt	Asset Status	Serial Number	Tool Agent	Tool	HB Age	Location	Street Address	City	Country	Associations	Op. Sys.	Support Assign. Grp	Notes	Tool date	MM_Date
    IP Add	IN_SCOPE	6/26/2020	6/26/2020	NEW	UNDEF	INSTALL			UNDEF	UNDEF	UNDEF	UNDEF		SLES 10 X	UNDEF	BIGFIX:INSTALL/CONFIGURE		N/A
    IP Add	IN_SCOPE	6/26/2020	6/26/2020	NEW	UNDEF	UNSUPPORTED_OS			UNDEF	UNDEF	UNDEF	UNDEF		UNKNOWN	UNDEF			N/A
    IP Add	IN_SCOPE	6/26/2020	6/26/2020	NEW	UNDEF	UNSUPPORTED_OS			UNDEF	UNDEF	UNDEF	UNDEF		UNKNOWN	UNDEF			N/A
    

    Monday, June 29, 2020 2:37 PM
  • Try this:

    get-content c:\junk\t.csv | 
        Select-Object -skip 2 |
            ConvertFrom-Csv -Delimiter "`t" -Header "Active Servers","Scope","Scope Changed","Discovered Dt","Asset Status","Serial Number","Tool Agent","Tool","HB Age","Location","Street Address","City","Country","Associations","Op. Sys.","Support Assign. Grp","Notes","Tool date","MM_Date" |
                Select-Object "Active Servers",City,Country |
                    Export-CSV c:\junk\t1.csv -NoTypeInformation


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)


    Monday, June 29, 2020 3:28 PM
  • Code coded form for few lines:-

    ##### GENERATED: 2020-06-29 09:13:00 #####																		
    # Active Servers	Scope	Scope Changed	Discovered Dt	Asset Status	Serial Number	Tool Agent	Tool	HB Age	Location	Street Address	City	Country	Associations	Op. Sys.	Support Assign. Grp	Notes	Tool date	MM_Date
    IP Add	IN_SCOPE	6/26/2020	6/26/2020	NEW	UNDEF	INSTALL			UNDEF	UNDEF	UNDEF	UNDEF		SLES 10 X	UNDEF	BIGFIX:INSTALL/CONFIGURE		N/A
    IP Add	IN_SCOPE	6/26/2020	6/26/2020	NEW	UNDEF	UNSUPPORTED_OS			UNDEF	UNDEF	UNDEF	UNDEF		UNKNOWN	UNDEF			N/A
    IP Add	IN_SCOPE	6/26/2020	6/26/2020	NEW	UNDEF	UNSUPPORTED_OS			UNDEF	UNDEF	UNDEF	UNDEF		UNKNOWN	UNDEF			N/A

    ок, so now we know that this is not the comma separated value format, and your first steps did sonething different that you expected :)

    The opinion expressed by me is not an official position of Microsoft

    Monday, June 29, 2020 3:43 PM
  • Nope, it did not work.
    Monday, June 29, 2020 3:45 PM
  • I need to have Active server, Location, Street Address and country to be populated in the output sheet.
    Monday, June 29, 2020 3:53 PM
  • If you want someone to understand your question you need to read teh following very carefully and ask your question corretly and with all of the required information.


    \_(ツ)_/

    Monday, June 29, 2020 4:09 PM
  • I am not over expecting from the forum, all i ask was how i can filter a CSV through a column with distinct values and bring the corresponding server values in an output file. I did also research a lot on the same concern and trying. Did i ask something which is not understandable?
    Monday, June 29, 2020 4:32 PM
  • I am not over expecting from the forum, all i ask was how i can filter a CSV through a column with distinct values and bring the corresponding server values in an output file. I did also research a lot on the same concern and trying. Did i ask something which is not understandable?

    Mostly yes.  Your question is a moving target.  It is hard to understand what the exact question is.

    Now you are asking about a CSV but your initial question was about Excel.

    Much of the confusion is caused by your not posting code as required by teh fiorum and descibed in the links I posted.  Your knowledge of PowerShell is limited which is causing you to misuse terminology whiich adds  to the confusion.

    The simple answer to your latest version of the question is to use "Where-Object" to filter a CSV.  To learn how to do this you can reqad PowerShell book I posted a link to or you can search for articles that explain how to use "Where-Object" to filter collections.

    Simple example:

    Import-Csv myfile.csv |
        Where-Object{
            $_.ColumnB -q 'somevalue'
        }
    Without a clear understand of the basics of PowerShell you will find it hard to ask questions and understand answers.



    \_(ツ)_/

    Monday, June 29, 2020 4:41 PM
  • Since my first post i have been talking CSV's 

    Tried Already 

    import-csv "C:\Users\tarwaniv\Desktop\file2.csv" | ConvertFrom-Csv -Header "#Active Servers", "Street Address" , "City", "Country" | Where-Object {$_.Country -in @("ARGENTINA","AUSTRALIA","BELARUS","BELGIUM")}
     | Export-Csv "C:\Users\tarwaniv\Desktop\file4.csv"
    Check all the posts as what has been done so far on this, if you need to really want to answer, else wait for some meaning full answers.

    Monday, June 29, 2020 5:30 PM
  • ok, now i have some time to answer with details

    your data not a comma separated so you need convert it somehow to csv supported format

    in my preparation i copied your sample data into the file Example.csv (no difference that would be the name and extansion) and make some preparation steps

    1 i replaced tab (and multi tabs) with semicolon char (;)

    2 i removed first hash from every line in this case second line can be interpretted as header

    3 after all i convert csv data (with semicolon delimiter) to the posh objects

    (Get-Content .\Example.csv ) -replace '\t+',';' -replace '^#' | ConvertFrom-Csv -Delimiter ';'

    After executing previous command you should get something like this


    After that you can use filters (where-object) and select some needed properties 

    (Get-Content .\Example.csv ) -replace '\t+',';' -replace '^#' |
    ConvertFrom-Csv -Delimiter ';' |
    Where-Object {$_.Country -in @('Undef', 'USA', 'Belarus')} |
    Select-Object 'Active servers', Location, 'Street Address', Country

    *Properties with spaces inside the name should be placed in quotes

    So if you have correct output in the console you can now export this into the csv

    (Get-Content .\Example.csv ) -replace '\t+',';' -replace '^#' |
    ConvertFrom-Csv -Delimiter ';' |
    Where-Object {$_.Country -in @('Undef', 'USA', 'Belarus')} |
    Select-Object 'Active servers', Location, 'Street Address', Country |
    Export-CSV .\output.csv -Delimiter ';'

    If you will have any problems, please provide outputs from all 3 steps


    The opinion expressed by me is not an official position of Microsoft

    • Edited by Vector BCO Monday, June 29, 2020 9:10 PM
    • Proposed as answer by Vector BCO Monday, June 29, 2020 9:12 PM
    Monday, June 29, 2020 8:32 PM
  • Given the code you originally posted, the assumption was that you were having a problem importing the file using Import-CSV. I suspect that was true, but not for the reason you wrote the code to solve. Your problem is that the file you're importing is not a "Comma-Separated Values" but a "Tab-Separated Values" file.

    The code I originally supplied does work, given the data you supplied. However, since you can simply import the TSV file, this code greatly simplifies the solution:

    Import-CSV c:\junk\t.csv -delimiter "`t" |
        Select-Object "# Active Servers","Street Addrress",City |
            Export-CSV c:\junk\t1.csv -Delimiter "`t" -NoTypeInformation


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Monday, June 29, 2020 9:31 PM
  • I am not over expecting from the forum, all i ask was how i can filter a CSV through a column with distinct values and bring the corresponding server values in an output file. I did also research a lot on the same concern and trying. Did i ask something which is not understandable?

    I agree with Rich.  You need to think about what you ar5e asking,  Asking about a "sheet" then a "CSV" then a "TSV" is not a good way to start.

    We need accurate information to help.  You are constantly changing the question and the description of the data and the requirement.

    Please read the links I posted until you are able to ask a clear question.


    \_(ツ)_/

    Monday, June 29, 2020 9:51 PM
  • Given the code you originally posted, the assumption was that you were having a problem importing the file using Import-CSV. I suspect that was true, but not for the reason you wrote the code to solve. Your problem is that the file you're importing is not a "Comma-Separated Values" but a "Tab-Separated Values" file.

    The code I originally supplied does work, given the data you supplied. However, since you can simply import the TSV file, this code greatly simplifies the solution:

    Import-CSV c:\junk\t.csv -delimiter "`t" |
        Select-Object "# Active Servers","Street Addrress",City |
            Export-CSV c:\junk\t1.csv -Delimiter "`t" -NoTypeInformation


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Vishal provided data that contain more then one tabs between some values, so import-csv -delimiter "`t" may not work correctly

    The opinion expressed by me is not an official position of Microsoft

    Monday, June 29, 2020 10:00 PM

  • Vishal provided data that contain more then one tabs between some values, so import-csv -delimiter "`t" may not work correctly

    The opinion expressed by me is not an official position of Microsoft

    The data and explanation are all over the place.  It is impossible to know without a copy of the raw file.  Multiple tabs are ok if the fields are blank. 

    Only the OP can sort this out by trying to understand the technical issues and why they are issues.  That will require learning more of the technology unless you guys are willing to guess until you hit the jackpot.  I would play poker with you as it is a sure win for me.  

    Don't gamble with technology.  It seldom ends well.


    \_(ツ)_/


    • Edited by jrv Monday, June 29, 2020 10:09 PM
    Monday, June 29, 2020 10:09 PM
  • Sure, Affirmative.
    Wednesday, July 1, 2020 9:01 AM
  • Thank a lot for your response on this, let me know which csv format did you choose to save the file?
    Wednesday, July 1, 2020 2:59 PM
  • Thank a lot for your response on this, let me know which csv format did you choose to save the file?

    what do you mean?

    I get your code block, copy it from web page, open notepad++.exe, paste there your sample and save that file with name sample.csv (it also could be 'ABCD.txt', 'test.ABCD', 'NameWithOutExtansion' or any other name)

    I see one more possible problem that you may faced - linux lines ending also may have some impact

    If you still have a problem, please show your file in notepad++ (with special char displaying)


    The opinion expressed by me is not an official position of Microsoft

    • Edited by Vector BCO Wednesday, July 1, 2020 3:22 PM
    Wednesday, July 1, 2020 3:18 PM
  • The file as posted above is not a TSV and not a CSV.  It is just a formatted text file.  This is likely due to the user copying th file incorrectly.  It  appears to be a log file that has likely been printed to a text file which will always break a structured log.

    The task must be performed on the original log file.  No other method is likely to work correctly.


    \_(ツ)_/

    Wednesday, July 1, 2020 5:13 PM
  • Hi,
    Was your issue resolved? 
    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.
    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.
    If no, please reply and tell us the current situation in order to provide further help.
    Best Regards,
    Yang Yang

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, July 3, 2020 5:48 AM