CODEWITHSUNDEEP
powershellcsv
Christian Cornell
Christian Cornell

Reputation: 21

Split CSV file with semicolon in PowerShell

This the the template from my system so I can't modify anything here. Basically I want to split into multiple files based on the value on column 3 "Comment".

DeliveryType;ShippingDate;Comment;SellerSku;Requested
send;05/29/16 03:10 PM;HCM - VN101YK- MK1001;EX110OTBCE8QV;3
send;05/29/16 03:1PM;HN - VN101YK;EX110OTBCE8XHY;1

I've tried this code & changed the file name/folder but it doesn't respond to anything.

Import-Csv test.csv | Group-Object -Property "Comment" | ForEach-Object {
    $path = $_.Name + ".csv";
    $_.Group | Export-Csv -Path $path -NoTypeInformation
}

Upvotes: 2

Views: 3065

Answers (2)

Mark Wragg
Mark Wragg

Reputation: 23355

I actually don't think you need to use Group at all to do this:

$CSV = Import-csv test.csv -Delimiter ';'

$CSV | ForEach-Object {
    $_ | Export-CSV "$($_.comment).csv" -Append -NoTypeInformation
}

Explanation:

  • Imports the CSV using Import-CSV, specifying that the file is delimited by semi-colons (expects comma by default).
  • Pipes the result of that Import to a ForEach-Object loop
  • Within the loop $_ represents the current item in the pipeline (e.g the current line in the CSV) and we can access it's properties with . notation.
  • Take the current line in the CSV and append it to a CSV file with it's name. We use the sub-expression operator $( ) to access the .comment property of the current pipeline object from within a double-quoted string.
  • Note also that I think the -Append switch requires you to be using PowerShell version 3 or above.
  • This command also includes the -NoTypeInformation switch which stops PowerShell from putting an unnecessary object description comment line at the top of the output, which can be annoying if you then open the file in Excel as it is visible.

Upvotes: 2

Iggy Zofrin
Iggy Zofrin

Reputation: 525

Your code only does not work because of two reasons: 1. It is trying to parse csv file delimiting by commas by default, not semi-colons. You can fix this by adding -Delimiter ';' to Import-Csv cmd-let, example: Import-Csv test.csv -Delimiter ';'

  1. $_ is the object that was piped to your next command by |, not $

Below is the code that will work:

Import-Csv test.csv -Delimiter ';' | Group-Object -Property "Comment" | Foreach-Object { $path=("C:\pathto-output-files\" + $_.Name + ".csv"); Export-Csv -InputObject $_ -Path $path -NoTypeInformation}

Upvotes: 0

Related Questions