Logo

Documentation home for Transend tools

  • Build-ArtifactArchive
  • Build-ExceptionReport
  • Build-FolderReport
  • Build-Report
  • Compress-Folders
  • Expand-ArtifactArchive
  • Find-UserListErrors
  • Format-Csv
  • Initialize-ToolsDirectory
  • Mount-ToolsDirectory
  • New-SampleFiles
  • Remove-EmptyFolders
  • Select-Pattern
  • Show-Config

    • Home

    Format-Csv

    SYNOPSIS

    Format a CSV file in one of several modes.

    SYNTAX

    Delimiters (Default)

    Format-Csv -Csv <String> [-InputPrimaryDelimiter <String>] [-InputSecondaryDelimiter <String>]
 [-OutputPrimaryDelimiter <String>] [-OutputSecondaryDelimiter <String>] [-MultiValueFields <String[]>]
 [-Format <String>] [<CommonParameters>]

    Translate

    Format-Csv -Csv <String> [-InputPrimaryDelimiter <String>] [-InputSecondaryDelimiter <String>]
 [-OutputPrimaryDelimiter <String>] [-OutputSecondaryDelimiter <String>] [-MultiValueFields <String[]>]
 -TranslationCsv <String> [-Format <String>] [<CommonParameters>]

    Flatten

    Format-Csv -Csv <String> [-InputPrimaryDelimiter <String>] [-InputSecondaryDelimiter <String>]
 [-OutputPrimaryDelimiter <String>] [-OutputSecondaryDelimiter <String>] [-MultiValueFields <String[]>]
 -FlattenKeyFields <String[]> -FlattenValueField <String> [-Format <String>] [<CommonParameters>]

    DESCRIPTION

    Use the Format-Csv cmdlet to format a CSV file in one of three modes.

    The Delimiters mode is used to change primary and secondary delimiters, generally into the combination supported by TMCTools (semicolons as primary delimiters and commas as secondary delimiters).

    The Translate mode is used to perform string translation, using a separate file of SourceName;TargetName translations.

    The Flatten mode is generally used for group CSVs when nested members need to be resolved, thus “flattening” the CSV.

    EXAMPLES

    Example 1: Convert delimiters

    # users.csv

SourceId,TargetId
user1@source.org,user1@target.org
user2@source.org,user2@target.org
user3@source.org,user3@target.org

    Format-Csv -Csv 'users.csv' -InputDelimiter ',' -OutputDelimiter ';' -Format 'Csv'
"SourceId";"TargetId"
"user1@source.org";"user1@target.org"
"user2@source.org";"user2@target.org"
"user3@source.org";"user3@target.org"

    Converts the CSV record delimiters from commas to semicolons.

    Example 2: Perform record translation

    # groups.csv

Name;EmailAddress;Members
Group1;group1@example.org;CN=User1/O=Example,CN=User2/O=Example
Group2;group2@example.org;CN=User3/O=Example,CN=User4/O=Example
Group3;group1@example.org;CN=User5/O=Example,CN=User6/O=Example

    # translations.csv

SourceName;TargetName
CN=User1/O=Example;user1@example.org
CN=User2/O=Example;user2@example.org
CN=User3/O=Example;user3@example.org
CN=User4/O=Example;user4@example.org
CN=User5/O=Example;user5@example.org
CN=User6/O=Example;user6@example.org

    Format-Csv -Csv 'groups.csv' -TranslationCsv 'translations.csv' -MultiValueFields @('Members') -Format 'Csv'
"Name";"EmailAddress";"Members"
"Group1";"group1@example.org";"user1@example.org,user2@example.org"
"Group2";"group2@example.org";"user3@example.org,user4@example.org"
"Group3";"group1@example.org";"user5@example.org,user6@example.org"

    Translates Domino canonical names into email addresses.

    Example 3: Perform record flattening

    # groups.csv

Name;EmailAddress;Members
Animals;animals@example.org;Mammals,Reptiles
Mammals;mammals@example.org;Dogs,Cats
Reptiles;reptiles@example.org;Snakes,Lizards
Dogs;dogs@example.org;Retriever,Lab
Cats;cats@example.org;Siamese,Persian
Snakes;snakes@example.org;Rattlesnake,Cobra
Lizards;lizards@example.org;Chameleon,Gecko

    Format-Csv -Csv 'groups.csv' -FlattenKeyFields @('Name') -FlattenValueField 'Members' -MultiValueFields @('Members') -Format 'Csv'
"Name";"EmailAddress";"Members"
"Animals";"animals@example.org";"Persian,Siamese,Lab,Retriever,Chameleon,Gecko,Cobra,Rattlesnake"
"Mammals";"mammals@example.org";"Persian,Siamese,Lab,Retriever"
"Reptiles";"reptiles@example.org";"Chameleon,Gecko,Cobra,Rattlesnake"
"Dogs";"dogs@example.org";"Lab,Retriever"
"Cats";"cats@example.org";"Persian,Siamese"
"Snakes";"snakes@example.org";"Cobra,Rattlesnake"
"Lizards";"lizards@example.org";"Chameleon,Gecko"

    Converts nested group members to a flattened (direct membership) structure.

    PARAMETERS

    -Csv

    The path to the CSV to format.

    Type: String
Parameter Sets: (All)
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

    -FlattenKeyFields

    An array of fields to be used as key fields. Identify all fields that may contain values that appear in the -FlattenValueField field.

    Type: String[]
Parameter Sets: Flatten
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

    -FlattenValueField

    The field to be flattened.

    Type: String
Parameter Sets: Flatten
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

    -Format

    Specifies the output format for formatted records.

    Type: String
Parameter Sets: (All)
Aliases:
Accepted values: Csv, Json, Objects

Required: False
Position: Named
Default value: Objects
Accept pipeline input: False
Accept wildcard characters: False

    -InputPrimaryDelimiter

    The primary CSV delimiter used in the input CSV field. Defaults to ;.

    Type: String
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: ;
Accept pipeline input: False
Accept wildcard characters: False

    -InputSecondaryDelimiter

    The secondary CSV delimiter used in the input CSV file. Defaults to ,.

    Type: String
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: ,
Accept pipeline input: False
Accept wildcard characters: False

    -MultiValueFields

    A list of CSV fields that can contain multiple values. When specified, each value in field will be treated separately. If not specified, the entire contents of the field will be considered a single value.

    Type: String[]
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

    -OutputPrimaryDelimiter

    The primary CSV delimiter used in the output. Defaults to ;. Note that this is only meaningful if -Format is Csv.

    Type: String
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: ;
Accept pipeline input: False
Accept wildcard characters: False

    -OutputSecondaryDelimiter

    The secondary CSV delimiter used in the output. Defaults to ,. Note that this is only meaningful if -Format is Csv.

    Type: String
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: ,
Accept pipeline input: False
Accept wildcard characters: False

    -TranslationCsv

    The path to a CSV file containing translation records.

    Type: String
Parameter Sets: Translate
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

    CommonParameters

    This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.

    INPUTS

    None

    OUTPUTS

    System.Object

    NOTES

    TMCTools configuration options