CODEWITHSUNDEEP
arrayspowershell
scatterbits
scatterbits

Reputation: 331

PowerShell: trouble importing tab delimited file as array

I am trying to build a PowerShell script that finds a tape barcode of previous month and stores it in a variable. The data is read from a tab delimited text file generated by the backup software. For some reason, PowerShell is able to import the file as an array, however I am unable to filter based on the data.

I will explain:

The first problem I have, is that the first 5 lines of the exported file prevent PowerShell from recognizing it as an array:

#List of Media
#Cell Manager: hpdp.domain.local
#Creation Date: 11/08/2018 9:36:09 AM
# Headers
# Medium ID Label   Location    Status  Protection  Used [MB]   Total [MB]  Last Used   Last Used_t Pool    Type

The way I am able to 'fix' this is by using notepad to delete the first four lines and remove the # from the header line. The file then looks like this:

Medium ID   Label   Location    Status  Protection  Used [MB]   Total [MB]  Last Used   Last Used_t Pool    Type
id1:id2:id3:id5 [hostname] labelname_31 [Library:     5]    Good    13/08/2018 10:01:41 PM  762699.50   762699.50   14/07/2018 10:00:36 PM  1531828836  Tape Drive Pool LTO-Ultrium

I then run the following command to import the file as an array:

$Table = Import-Csv $file # "-Delimiter `t" yields the same result

Consider the following results:

$Table | FT produces a table looking like the source file

$Table | FL produces a list with all objects appearing as they should

$Table | select "Label" lists the Label column with all data under it. Doing this with any column produces the column and data as it should.

$Table | Where-Object {"Last Used" -like '*'} produces same output as $Table | FL

Now we're getting to what I'm trying to achieve and the part that isn't working:

I want to locate and store a Label object who's "Last Used" date is of previous month. However when I run something this:

$Table | Where-Object {"Last Used" -like '*/07*'}

I get no result.

When I run this:

$Table | Where-Object {"Last Used" -is '2/07/2018 7:36:28 PM'}

I get lots of this:

Cannot convert the "2/07/2018 7:36:28 PM" value of type "System.String" to type "System.Type".
At line:1 char:24
+ $Table | Where-Object {"Last Used" -is '2/07/2018 7:36:28 PM'}
+                        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], RuntimeException
    + FullyQualifiedErrorId : RuntimeException

Cannot convert the "2/07/2018 7:36:28 PM" value of type "System.String" to type "System.Type".
At line:1 char:24
+ $Table | Where-Object {"Last Used" -is '2/07/2018 7:36:28 PM'}
+                        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], RuntimeException
    + FullyQualifiedErrorId : RuntimeException

Can anyone share their wisdom in what I'm doing wrong? Thanks in advance!

Upvotes: 1

Views: 898

Answers (1)

Robert Cotterman
Robert Cotterman

Reputation: 2268

 $Table | Where-Object {"Last Used" -like '*/07*'}

Is looking for */07* as the single quotes imply. Double quotes would be better. A back slash will escape any weird characters like the forward slash incase it has meaning

$Table | Where-Object {"Last Used" -like "*\/07*"}

I'd also do an indication of where "last used" belongs. A $_ means the piped in object

$Table | Where-Object {$_."Last Used" -like "*\/07*"}

And finally, replace -is with -eq that's where the system type error is coming from. (read this for more info on -is vs -eq) -contains and -ccontains would also help

$Table | Where-Object {$_."Last Used" -eq '2/07/2018 7:36:28 PM'}

Also another tip. Avoid pipes they add a ton of processing. This is the same as above but many many times faster. Especially if opening a file. The pipe waits for the previous command to be finished to do its job instead of a method. A method does it in line with it. Skipping unnecessary processing. In other words the pipe will force where to read every line the method will only activate on compatible lines. If you wanna test it you could use measure-command {command to measure here}

$Table.where({$_."Last Used" -eq '2/07/2018 7:36:28 PM'})

Upvotes: 1

Related Questions