Splitting a CSV with Powershell as a Post command in Informatica Cloud

[tcfreer]

Hi All

I've got a query that I'd really like to get working and I'd appreciate any help as I am banging my head on the wall

I have 2 possible Powershell scripts that should work - in theroy

However there not.

Back Ground - I need to get a flat CSV file, to split out so I can re join it to make a SAGE200 compatible load flat file

I have half the data for the load, however I need to split a set into separate sheets

Variables - there can be up to 6 rows in a Group based on the GroupID - I need to get the 1st instance into a sheet 1, the 2nd instance into a sheet2
and so on

I'm trying the following, which from what I have read should work - but aren't

1

Import-Csv C:\InformaticaConfig\Connections\Chilled_Pubs\WorkingFiles\Chilled_Customer_Temp_map.csv | Group-Object -Property GroupID | Foreach-Object {$path = $_.Name + ".csv"; $_.Group | Export-Csv - Path C:\InformaticaConfig\Connections\Chilled_Pubs\Splits\$path -NoTypeInformation}

2

$GroupField = "GroupID"$Delimiter = ";"
$csv = 'C:\InformaticaConfig\Connections\Chilled_Pubs\WorkingFiles\Chilled_Customer_Temp_map.csv'
$outfolder = 'C:\InformaticaConfig\Connections\Chilled_Pubs\Splits\'


$all = Import-Csv $csv -Delimiter $Delimiter
$groupedCollection = $all | Group-Object $GroupField


foreach($group in $groupedCollection)
{
   Write-Host ("Group '" + $group.Name + "' // " + $group.Count.ToString() + " Members")
   $group.Group | ConvertTo-Csv -NoTypeInformation -Delimiter "," | Out-File ($outfolder + $group.Name + ".csv")
}

If anyone has any ideas it'd be great

 

[ralliartur]

Hi, For the first one:

1. missing delimiter in Import-Csv
2. Missing quotes in Import-Csv (this is optional - i had spaces in my path)
3. Space between "- Path" in Export-Csv
4. Missing quotes in Export-Csv (this is optional as previously)

Import-Csv  "C:\InformaticaConfig\Connections\Chilled_Pubs\WorkingFiles\Chilled_Customer_Temp_map.csv"   -Delimiter ";" | Group-Object -Property GroupID | Foreach-Object {$path = $_.Name +  ".csv"; $_.Group | Export-Csv -Path  "C:\InformaticaConfig\Connections\Chilled_Pubs\Splits\$path" -NoTypeInformation}

For the second one:

Two variables in first line, they should be in separate lines

$GroupField = "GroupID"
$Delimiter = ";"
$csv = 'C:\InformaticaConfig\Connections\Chilled_Pubs\WorkingFiles\Chilled_Customer_Temp_map.csv'
$outfolder = 'C:\InformaticaConfig\Connections\Chilled_Pubs\Splits\'
$all = Import-Csv $csv -Delimiter $Delimiter
$groupedCollection = $all | Group-Object $GroupField
foreach($group in $groupedCollection)
{
   Write-Host ("Group '" + $group.Name + "' // " + $group.Count.ToString() + " Members")
   $group.Group | ConvertTo-Csv -NoTypeInformation -Delimiter "," | Out-File ($outfolder + $group.Name + ".csv")
}