Results 1 to 2 of 2

Thread: Splitting a CSV with Powershell as a Post command in Informatica Cloud
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2017
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Code:
    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
    Code:
    $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

  2. #2
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Splitting a CSV with Powershell as a Post command in Informatica Cloud

    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)

    Code:
    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

    Code:
    $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")
    }

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •