2

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
}
Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
Christian Cornell
  • 21
  • 1
  • 2

2 Answers2

2

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.
Mark Wragg
  • 22,105
  • 7
  • 39
  • 68
  • Hi Ansgar, thanks for your help. FYI, i have absolutely no knowledge about Powershell. Those code was merely copied from others site. Albeit there was error, it actually split into 2 files from the same owner, but it split based on 1 owner only. (My original files has two owner, 1 owner has two file each. If correct, it should have 4 files). (see link: prntscr.com/fbwahc But when i tried again, it didn't work. See link: prntscr.com/fbwapt . Can you help to check? – Christian Cornell May 25 '17 at 10:25
  • The error suggests that there are characters in the "comment" field which aren't valid for use in a path, e.g: https://stackoverflow.com/q/1976007/2796058. To fix you'll need to go through the source data and make sure there are no illegal characters. – Mark Wragg May 25 '17 at 10:48
0

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}
Iggy Zofrin
  • 515
  • 3
  • 10