none
Powershell , Get-content, Import Txt File into an array RRS feed

  • Question

  • Hello,

    So i am trying to mimic an array that works, by import a txt file containing info.. but it is not working.

    This works....

    # Filter for an array of values IE: choose muipliple items.... in one column
    $xlFilterValues = 7 # found in MS documentation
    #filter for column
    $FL = @("Item1", "Item2", "item3") 
    $rng=$Worksheet.cells.item(2,38).entirecolumn
    $rng.select | Out-Null
    $excel.Selection.AutoFilter(20,$FL,$xlFilterValues)

    This does not work..

    Txt file contains  Item1, Item2, Item3

    Here is the code that does not work, What am i doing wrong??

    # Filter for an array of values IE: choose muipliple items.... in one column
    $xlFilterValues = 7 # found in MS documentation
    #filter for column
    #$FL = @("item1", "item2", "item3") 
    [array]$FL = Get-Content C:\Test\Row20aa.txt
    $rng=$Worksheet.cells.item(2,38).entirecolumn
    $rng.select | Out-Null
    $excel.Selection.AutoFilter(20,$FL,$xlFilterValues)

    Thank you in advance, Please help


    Jeffrey I Wilson

    Friday, August 29, 2014 1:38 PM

Answers

  • I am trying to learn this as fast as possible, i have been writing VB for a very long time and converted to Powershell in December last year. you have been very helpful, However, it still does not work, importing the data.

    # Filter for an array of values IE: choose muipliple items.... in one column
    $xlFilterValues = 7 # found in MS documentation
    #filter for column
    #$FL = @("Item1", "Item2", "Item3") 
    #$FL = Get-Content C:\Test\filter.txt -raw
    $FL=((Get-Content C:\Test\filter.txt) -join ',') -split ','
    $rng=$Worksheet.cells.item(2,38).entirecolumn
    $rng.select | Out-Null
    $excel.Selection.AutoFilter(20,$FL,$xlFilterValues)

    ok, i changed to your code..

    $fltr=((Get-Content C:\Test\filter.txt) -join ',') -split ','
    $Worksheet.UsedRange.Columns.Item(20).Autofilter(20,$fltr,7)

    and bam, it worked....:)

    ok, so now i can pump data in via txt file...

    Also silly ?, how does one obtain points? I see you have 78,522 - you must have been posting for years....

    Thanks jrv


    Jeffrey I Wilson


    • Edited by blackd1 Friday, August 29, 2014 4:33 PM
    • Marked as answer by blackd1 Tuesday, September 2, 2014 1:22 PM
    Friday, August 29, 2014 4:29 PM

All replies

  • Your text file needs to contain 3 individual lines.


    item1
    item2
    item3
    

    You also don't need [array] at the beginning of that line. If the text file contains multiple lines, get-content will return it as an array automatically.

    -- Bill Stewart [Bill_Stewart]

    Friday, August 29, 2014 2:05 PM
    Moderator
  • OK, i have tried, that as well to no avail, for some reason it won't import correctly, the resuts,simply come up blank.. and i get this error..

    Exception calling "AutoFilter" with "3" argument(s): "Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))" At line:1 char:1 + $excel.Selection.AutoFilter(20,$FL,$xlFilterValues) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : ComMethodTargetInvocation 

    Thanks, Jeff W.


    Jeffrey I Wilson


    • Edited by blackd1 Friday, August 29, 2014 2:27 PM
    Friday, August 29, 2014 2:14 PM
  • This creates an array one element per line.

    $FL = Get-Content C:\Test\Row20aa.txt

    Just type: $fl to see the array contents.


    ¯\_(ツ)_/¯

    Friday, August 29, 2014 2:24 PM
  • I can't see your screen, so I don't know what "simply comes up blank" means. All I can tell you is that a text file that contains a single line isn't going to automatically import into a 3-element array unless you 1) put each element on its own line or 2) parse the single line into an array using the -split operator (or something similar).


    -- Bill Stewart [Bill_Stewart]

    Friday, August 29, 2014 2:25 PM
    Moderator
  • OK, So Here is where i am,

    If i put one item in the text file and use

    $FL = Get-Content C:\Test\Row20aa.txt So the txt file would read only item1

     Then it works, but when i had any other line, like

    Item1

    Item2

    Item3

    it does not work and throws the error provided above..

    So if i put them in succession like

    Item1, Item2, Item3

    it will take it but only filter on the last item3.. Thoughts??? I hope i am explaining well, i am new to blogging for a solution, but i am really stuck. I have another question but will open a new thread for that one... Thanks again...


    Jeffrey I Wilson


    • Edited by blackd1 Friday, August 29, 2014 2:59 PM
    Friday, August 29, 2014 2:57 PM
  • You cannot have blank lines in the file. 

    Here is how to filter by filter as your method is a bit convoluted:

    # hos to filter a worksheet
    Add-Type -AssemblyName Microsoft.Office.Interop.Excel
    $xl=New-Object -ComObject Excel.Application
    $xl.Workbooks.Open('c:\scripts\process.xlsx')
    
    # filter using wild card.
    $fltr=@('Microsoft*','Adobe*')
    # select target column add apply filter
    $xl.ActiveSheet.UsedRange.Columns.Item(9).Autofilter(1,$fltr,[Microsoft.Office.Interop.Excel.XlAutoFilterOperator]::xlFilterValues)
    
    #done
    
    


    ¯\_(ツ)_/¯

    Friday, August 29, 2014 3:11 PM
  • Here is how to load the filter from a file on item per line.

    # use a raw read to force correct array load.
    $fltr=Get-Content c:\scripts\filter.txt -Raw
    $xl.ActiveSheet.UsedRange.Columns.Item(9).Autofilter(1,$fltr,7)

    Notice we need to add "-Raw" to the read.


    ¯\_(ツ)_/¯

    Friday, August 29, 2014 3:29 PM
  • If you are having encoding issues this will work around them:

    # use a raw read to force correct array load.
    $fltr=((Get-Content filter.txt) -join ',') -split ','
    $xl.ActiveSheet.UsedRange.Columns.Item(9).Autofilter(1,$fltr,7)
    


    ¯\_(ツ)_/¯

    Friday, August 29, 2014 3:56 PM
  • So what should the txt file look like?

    Item1, Item2, Item3, Item4

    ?

    

    And can you please explain the

    Autofilter(1,$fltr,7)

    section


    Jeffrey I Wilson


    • Edited by blackd1 Friday, August 29, 2014 4:02 PM
    Friday, August 29, 2014 3:59 PM
  • So what should the txt file look like?

    Item1, Item2, Item3, Item4



    If the file is one line:

    $fltr=(Get-Content file.txt) -split ','


    ¯\_(ツ)_/¯

    Friday, August 29, 2014 4:12 PM
  • And can you please explain the

    Autofilter(1,$fltr,7)


    Autofilter works on a range, a column, or a selection (which is also a range).

    On a column the "1" says use the first column in the range.  $filtr is the array and the 7 says use values.


    ¯\_(ツ)_/¯

    Friday, August 29, 2014 4:14 PM
  • I am trying to learn this as fast as possible, i have been writing VB for a very long time and converted to Powershell in December last year. you have been very helpful, However, it still does not work, importing the data.

    # Filter for an array of values IE: choose muipliple items.... in one column
    $xlFilterValues = 7 # found in MS documentation
    #filter for column
    #$FL = @("Item1", "Item2", "Item3") 
    #$FL = Get-Content C:\Test\filter.txt -raw
    $FL=((Get-Content C:\Test\filter.txt) -join ',') -split ','
    $rng=$Worksheet.cells.item(2,38).entirecolumn
    $rng.select | Out-Null
    $excel.Selection.AutoFilter(20,$FL,$xlFilterValues)

    ok, i changed to your code..

    $fltr=((Get-Content C:\Test\filter.txt) -join ',') -split ','
    $Worksheet.UsedRange.Columns.Item(20).Autofilter(20,$fltr,7)

    and bam, it worked....:)

    ok, so now i can pump data in via txt file...

    Also silly ?, how does one obtain points? I see you have 78,522 - you must have been posting for years....

    Thanks jrv


    Jeffrey I Wilson


    • Edited by blackd1 Friday, August 29, 2014 4:33 PM
    • Marked as answer by blackd1 Tuesday, September 2, 2014 1:22 PM
    Friday, August 29, 2014 4:29 PM
  • On the same subject, how do i "reverse" the choice?

    So lets say that I have 30 items to choose from for the filter in one column, but want to exclude only 1 item??

    ___________________________

     ~~:>JIW~~


    Jeffrey I Wilson


    • Edited by blackd1 Friday, August 29, 2014 4:39 PM
    Friday, August 29, 2014 4:38 PM
  • On the same subject, how do i "reverse" the choice?

    So lets say that I have 30 items to choose from for the filter in one column, but want to exclude only 1 item??

    ___________________________

     ~~:>JIW~~


    Jeffrey I Wilson


    $xl.ActiveSheet.UsedRange.Columns.Item(9).Autofilter(1,@(''),7)

    Just exclude it from the array.



    ¯\_(ツ)_/¯

    Friday, August 29, 2014 5:03 PM
  • $xl.ActiveSheet.UsedRange.Columns.Item(9).Cells| Select Text -Unique

    You will eventually discover that Excel is very slow when running under automation. CYOu would do better to actually learn Excel and use VBA or C# if you have to do many things.

    Excel can also be opened as a database and queried with SQL using the gateway drivers; either Jet or Net.


    ¯\_(ツ)_/¯

    Friday, August 29, 2014 5:16 PM
  • YOur code above, only "clears" the data.

    Sorry if i did not explain correctly , what i want to do is exclude only one item, instead of choose lets say 4 items, i have that working.

    So what i am looking for is this

    Item1 = X

    Item2 = X

    Item3 = X

    Item 4 =

    Item5 = X

    In other words, only to NOT choose Item4.. or -ne, which i have found does not work...

    . I hope this helps...Apoligize for the formating


    Jeffrey I Wilson


    • Edited by blackd1 Friday, August 29, 2014 5:40 PM update info..
    Friday, August 29, 2014 5:18 PM
  • How do I exclude only 1 item out of say 7? instead of choosing 6 of them.. The above "clears" the column..

    Jeffrey I Wilson

    Looking at the Custom AutoFilter, how do I automate,

    does not equal  {Intuit}

    $$__Jiw__@@


    • Edited by blackd1 Friday, August 29, 2014 6:11 PM
    Friday, August 29, 2014 5:43 PM
  • How do I exclude only 1 item out of say 7? instead of choosing 6 of them.. The above "clears" the column..

    Jeffrey I Wilson

    Looking at the Custom AutoFilter, how do I automate,

    does not equal  {Intuit}

    $$__Jiw__@@


    See if this article helps. It shows how to remove element from array

    Sam Boutros, Senior Consultant, Software Logic, KOP, PA http://superwidgets.wordpress.com (Please take a moment to Vote as Helpful and/or Mark as Answer, where applicable)

    Friday, August 29, 2014 10:54 PM