PowerShell: Import-CSV with no headers and remove partial duplicate lines

Question

I have a log file that is formatted as a CSV with no headers. The first column is basically the unique identifier for the issues being recorded. There may be multiple lines with different details for the same issue identifier. I would like to remove lines where the first column is duplicated because I don't need the other data at this time.

I have fairly basic knowledge of PowerShell at this point, so I'm sure there's something simple I'm missing.

I'm sorry if this is a duplicate, but I could find questions to answer some parts of the question, but not the question as a whole.

So far, my best guess is:

Import-Csv $outFile | % { Select-Object -Index 1 -Unique } | Out-File $outFile -Append

But this gives me the error:

Import-Csv : The member "LB" is already present. At C:\Users\jnurczyk\Desktop\Scratch\POImport\getPOImport.ps1:6 char:1 + Import-Csv $outFile | % { Select-Object -InputObject $_ -Index 1 -Unique } | Out ... + ~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Import-Csv], ExtendedTypeSystemException + FullyQualifiedErrorId : AlreadyPresentPSMemberInfoInternalCollectionAdd,Microsoft.PowerShell.Commands.ImportCsvCommand

Using the foreach loop means you are selecting each row out of one row. Try removing the %{ }. — Benjamin Hubbard
– Benjamin Hubbard, Commented Dec 11, 2013 at 19:08

Dave F · Accepted Answer · 2018-12-11 18:31:17Z

32

Because your data has no headers, you need to specify the headers in your Import-Csv cmdlet. And then to select only unique records using the first column, you need to specify that in the Select-Object cmdlet. See code below:

Import-Csv $outFile -Header A,B,C | Select-Object -Unique A

To clarify, the headers in my example are A, B, and C. This works if you know how many columns there are. If you have too few headers, then columns are dropped. If you have too many headers, then they become empty fields.

edited Dec 11, 2018 at 18:31

Dave F

2,04619 silver badges20 bronze badges

answered Dec 11, 2013 at 19:21

Benjamin Hubbard

2,92524 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Joshua Nurczyk Over a year ago

I tried something similar, but couldn't figure out where to put the header name (A in your example). The only issue I have is that it appends a bunch of spaces to the end of each line when I output to a file. While annoying, I don't mind this much.

Benjamin Hubbard Over a year ago

Try using trim() to remove spaces.

Joshua Nurczyk Over a year ago

trim() doesn't work on non-string objects. Might figure out how to make it interpret the output of Select-Object as a string.

Benjamin Hubbard Over a year ago

Not sure where your whitespace is coming from, but check this out: stackoverflow.com/questions/17180955/…

user3818571 · Accepted Answer · 2016-11-10 23:22:59Z

3

Every time I look for a solution to this issue I run across this thread. However the solution accepted here is more generic that I would like. The function below Increments each time it sees the same header name: A, B, C, A1 D, A2, C1 etc.

Function Import-CSVCustom ($csvTemp) {
    $StreamReader = New-Object System.IO.StreamReader -Arg $csvTemp
    [array]$Headers = $StreamReader.ReadLine() -Split "," | % { "$_".Trim() } | ? { $_ }
    $StreamReader.Close()

    $a=@{}; $Headers = $headers|%{
        if($a.$_.count) {"$_$($a.$_.count)"} else {$_}
        $a.$_ += @($_)
    }

    Import-Csv $csvTemp -Header $Headers
}

answered Nov 10, 2016 at 23:22

user3818571

312 bronze badges

2 Comments

plaes Over a year ago

Just mentioning that System.IO.StreamReader requires .NET.

Joe the Coder Over a year ago

Just a heads up, this solution isn't compliant with RFC-4180. It will break if the headers are escaped and contain commas. tools.ietf.org/html/rfc4180

Mark Kram · Accepted Answer · 2017-09-11 16:24:02Z

0

To expand upon Benjamin Hubbard's post here is a little Sql Script (assuming that you will be inserting this data into a table in a database of course!) I use to create the header property in my script:

SELECT
        '-Header '
            + STUFF((SELECT
                    ',' + QUOTENAME(COLUMN_NAME, '"')
                    + CASE WHEN C.ORDINAL_POSITION % 5 = 0 THEN ' `' + CHAR(13) + CHAR(10) ELSE '' END
                FROM 
                    INFORMATION_SCHEMA.COLUMNS C
                WHERE
                    TABLE_NAME = '<Staging Table Name>'
            FOR XML PATH (''), type).value('.', 'nvarchar(max)'), 1, 1, '')

answered Sep 11, 2017 at 16:24

Mark Kram

5,8527 gold badges56 silver badges73 bronze badges

Collectives™ on Stack Overflow

PowerShell: Import-CSV with no headers and remove partial duplicate lines

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related