Usercube-CSV-Transform

Examples

Define a primary key

Consider the file C:/identitymanagerContoso/Sources/hr_example.csv with the following headers line:

Login,Company,Email,FirstName,LastName

To avoid having too much duplicated information on each line in a CSV file, we need to define a primary key for the file which will allow the pooling of common information. We choose to concatenate the values of the columns Login and Company with a - as separator in an Id column, which will be defined as key for our file.

--input-file C:/identitymanagerContoso/Sources/hr_example.csv --columns-concat "Login Company - ID"--columns-key ID```

Handle multi-valued columns in a generated file

Consider the file C:/identitymanagerContoso/Sources/hr_example123.csv with the following headers line separated by a ;:

GroupAzure;Members;GroupSharePoint;Members

This file is automatically generated by a script and the suffix (123here) is incremented on each generation. Thus, we need to use a regex to avoid changing the command line for each new generated file.

--input-file C:/identitymanagerContoso/Sources/hr_example(.*?).csv --regex --separator ;

The file contains two headers with the same name, each related to one kind of group. Thus, we need to rename one of these headers.

--input-file C:/identitymanagerContoso/Sources/hr_example(.*?).csv --regex --separator ; --headers-edit-name "Members MembersAzure"

In this example, we will consider that the two Members columns contain all members for each group separated by a , for the first Members column, and by a * for the second one. We need to transform these columns in Identity Manager's format for multi-valued attributes.

--input-file C:/identitymanagerContoso/Sources/hr_example(.*?).csv --regex --separator ; --headers-edit-name "Members MembersAzure" --columns-multivalued "MembersAzure ," "Members *"

Arguments

Argument Name	Details
--input-path required	Type String Description Specifies the CSV file to modify. Example Define C:/identitymanagerContoso/Sources/hr_example.csv as input file: --input-file C:/identitymanagerContoso/Sources/hr_example.csv.
--output-path optional	Type String Description Specifies the output path, which is the exports' output path by default. Example Define C:/identitymanagerContoso/Test as output folder: --output-path "C:/identitymanagerContoso/Test".
--new-name optional, required if --regex is true	Type String Description Specifies the new name for the output file. Example Define new name hr_transformed.csv: --new-name hr_transformed.csv.
--input-file-encoding default value: UTF-8	Type String Description Encoding of the input file. See the list of available encodings. Example --input-file-encoding UTF-16.
--headers-edit-index optional	Type String List Description Specifies the headers to edit by index, which is particularly useful to rename empty headers. Each member of the list is written like index newHeader. Example Add or replace header at index 1 with ExampleHeader : --headers-edit-index "1 ExampleHeader".
--headers-edit-name optional	Type String List Description Specifies the headers to rename (first found) with the new name. Each member of the list is written like currentHeader newHeader. Example Rename headers CompanyId into Company and int32_1 into int32: --headers-edit-name "CompanyId Company" "int32_1 int32".
--headers-remove-index optional	Type Integer Description Specifies the headers to remove by index. This command can be used to remove the second occurrence of a duplicate header by specifying its index. Example Remove header located at index 5: --headers-remove-index 5.
--headers-remove-name optional	Type String List Description Specifies the headers to remove by name (first found). Example Remove first occurrences of headers date1 and bool1: --headers-remove-name date1 bool1.
--new-headers optional	Type String List Description ONLY for files without headers, specifies the new headers except the ones created by the concatenation of columns. Example Defines header1 and header2 as headers of the file: --new-headers header1 header2.
--columns-concat optional	Type String List Description Specifies the columns to concatenate and how. Each member of the list is written like column1Header column2Header. If you want to specify characters between the column values, you can write column1Header column2Header charactersBetween. This operation creates a new column where it puts the result of the concatenation. This column header is the concatenation of the headers, but you can change it by writing the member like column1Header column2Header charactersBetween newColumnHeader. Example Concatenate columns: - Company and Employee with a - between them. ID will be the new column header. - guid1 and bytes1 with _ between them. - int32_2 and int64_2 with nothing in between. --columns-concat "Company Employee - ID" "guid1 bytes1 _" "int32_2 int64_2" .
--columns-multivalued optional	Type String List Description Specifies the columns with multi-valued values not splittable with breaks. Each member of the list is written like columnHeader separator. Example Handle columns multivalued1, using separator ,, and multivalued2, using separator *: --columns-multivalued "multivalued1 ," "multivalued2 *".
--columns-date optional	Type String List Description Specifies the columns with date values, and their date format, to format them into Identity Manager's format. Each member of the list is written like columnHeader dateFormat. Example Format date columns date1 and date2, using the format yyyyddMMHHmmss: --columns-date "date1 yyyyddMMHHmmss" "date2 yyyyddMMHHmmss".
--columns-bool optional	Type String List Description Specifies the columns with Boolean values to convert them into Identity Manager's format. Example Format Boolean columns bool1 and bool2: --columns-bool bool1 bool2.
--columns-int32 optional	Type String List Description Specifies the columns with Int32 values to convert them into Identity Manager's format. Example Format Int32 columns int32_1 and int32_2 : --columns-int32 int32_1 int32_2.
--columns-int64 optional	Type String List Description Specifies the columns with Int64 values to convert them into Identity Manager's format. Example Format Int64 columns int64_1and int64_2: --columns-int64 int64_1 int64_2.
--columns-guid optional	Type String List Description Specifies the columns with Guid values to convert them into Identity Manager's format. Example Format Guid columns guid1and guid2: --columns-guid guid1 guid2.
--columns-bytes optional	Type String List Description Specifies the columns with Bytes values to convert them into Identity Manager's format. Example Format Bytes columns bytes1 and bytes2: --columns-bytes bytes1 bytes2.
--columns-key optional	Type String List Description Specifies the columns key to delete duplicates (the first line found is the one we keep). A column created by this tool can be specified as a key column through this argument, like the columns created by the --columns-concat for example. Example Define columns RawId and ID as keys: --columns-key RawId ID.
---	---
--regex optional	Type No Value Description The file name is a regex so we find the last generated corresponding file.
--separator optional	Type String Description Defines the separator if different than ,.