How to remove CSV headers and keep columns seperate using Powershell

Question

I'm trying to split a CSV in to 2 based on the values in the first column. The newly created CSVs cannot have a column name and this is where the problem arises. When I remove the headers, all the values go into the first column.

In order to split the CSV based on the first column value I've had to assign headers. The 2 new files are created successfully with the correct values, however my final step is to remove the headers as the application used to process the CSV will not accept column names.

Once I've removed the headers, all the values are no longer separated by the comma and as a result are all in the first column. How can I get around this?

if (!(Test-Path X:)) {
    $net = New-Object -comobject Wscript.Network
    $net.MapNetworkDrive("X:","**Path**")
}

$file = Get-ChildItem -Path "**Path**"  | Where-Object {
    $_.LastWriteTime -gt (Get-Date).AddHours(-10) -and
    $_.Name -like '*.CSV*'
}

if ($file) {
    $filename = $file.FullName
    $date = (Get-Date).ToString("yyyyMMdd")
    $ExportPathJUO = "**Path**$date"
    $ExportPathWDA = "**Path**$date"

    ##Create Seperate Files in Staging Folder
    $csv = Import-Csv $filename -Header A,B,C,D,E,F,G
    $IDS = $csv | select -ExpandProperty A -Unique
    foreach ($ID in $IDS) {
        $csv | where {
            $_.A -like '*0000009*'
        } | Export-Csv $ExportPathJUO -NoTypeInformation
    }
    foreach ($ID in $IDS) {
        $csv | where {
            $_.A -like '*0000007*'
        } | Export-Csv $ExportPathWDA -NoTypeInformation
    }

    ##Remove Headers
    (Get-content $ExportPathJUO) |
        select -Skip 1 |
        Out-File -FilePath $ExportPathJUO -Force
    (Get-content $ExportPathWDA) |
        select -Skip 1 |
        Out-File -FilePath $ExportPathWDA -Force 
}

You treat the outcome of your Get-ChildItem $file as a single item instead of a possible array better iterated with a foreach. — user6811411
– user6811411, Commented Aug 27, 2019 at 9:26

Ansgar Wiechers · Accepted Answer · 2019-08-27 09:12:22Z

1

Don't use the *-Csv cmdlets if you don't have/want headers in your CSV. What you want can be done like this:

foreach (line in Get-Content $filename) {
    $a = $line.Split(',')[0]
    switch -wildcard ($a) {
        '*0000007*' { $line | Add-Content $ExportPathWDA }
        '*0000009*' { $line | Add-Content $ExportPathJUO }
    }
}

answered Aug 27, 2019 at 9:12

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

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

3 Comments

Martin Brown Over a year ago

Though this will work this case, it won't work where one of the input values has an escaped comma in it.

Ansgar Wiechers Over a year ago

@MartinBrown True. But I usually try to avoid over-engineering a solution.

Martin Brown Over a year ago

@AnsgarWiechers Just thought I'd mention it because I've spent way too many hours fixing that bug in multiple systems over my career. As long as people are aware of the issue that's fine.

Collectives™ on Stack Overflow

How to remove CSV headers and keep columns seperate using Powershell

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related