locked
Script to extract certain rows from csv RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I'm trying to extract rows from a csv file that meet a certain pattern (three characters and 8 digits) but cannot get it to work. see below code and an example of the csv file

    #Import the CSV file with all the data.
    $csv = Import-CSV J:\CanvasData\users_utf8nobom.csv 
    #Process the CSV file one row at a time 
    foreach ($row in $csv) { 
    #Each column is accessed using the column header. 
    #We can use the -not operator to specify 'doesn't match'.      
    if ($row.user_id -eq "\b[\D[3}\d{8}]\b")
    { 
    #... Export that row to a new CSV file.        
    $row | Export-CSV J:\Temp\teacherlist.csv  -Append -NoTypeInformation   
     } 
    }

    user_id

    status

    MCN18158364

    active

    901658

    active

    W00054

    active

    MCN17125917

    active

    Wednesday, August 1, 2018 1:14 PM

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote

    Try this:

    $csv = Import-CSV J:\CanvasData\users_utf8nobom.csv 
$csv | ? { $_.user_id -match '\D{3}\d{8}' } | export-csv j:\Temp\teacherlist.csv -NoTypeInformation

    or one liner

    import-csv J:\CanvasData\users_utf8nobom.csv | ? { $_.user_id -match '\D{3}\d{8}' } | export-csv J:\CanvasData\users_utf8nobom.csv -NoTypeInformation


    Wednesday, August 1, 2018 1:25 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    -eq doesn't use regular expressions.  Here's what regex101.com says your regex amounts to:

    /
    \b[\D[3}\d{8}]\b
    /
    gm
    \b assert position at a word boundary (^\w|\w$|\W\w|\w\W)
    Match a single character present in the list below 
    [\D[3}\d{8}]
    \D matches any character that\'s not a digit (equal to [^0-9])
    [3} matches a single character in the list [3} (case sensitive)
    \d matches a digit (equal to [0-9])
    {8} matches a single character in the list {8} (case sensitive)
    \b assert position at a word boundary (^\w|\w$|\W\w|\w\W)



    • Edited by JS2010 Wednesday, August 1, 2018 4:34 PM
    Wednesday, August 1, 2018 4:34 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Thanks for your question.

    Some suggestions for you.

    1. Because you match the regular expression, please replace -eq with -match.

    2. For the use of regular expressions in PowerShell, please refer to the link below.

    https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_regular_expressions?view=powershell-6

    Best Regards,

    Lee

    Just do it.

    Thursday, August 2, 2018 6:44 AM