Powershell - use CSV to create multiple text files

Question

I am trying to create a PS script that autogenerates txt files using a CSV file as an input.

The CSV file contains data such as this:

Category	Product	Priced
Bike	Red bike	Yes
Bike	Blue bike	Yes
Bike	Black bike	No
Car	Red car	No
Car	Blue car	Yes

The script should get a CSV as an input and generate 2 txt files (because we have 2 categories):

Example:

txt file 1: Bike.txt

Red bike - Yes
Blue bike - Yes
Black bike - No

I tried using this code, but it does not act the way I want it to:

$csv = Import-CSV -Path path.csv -Delimiter ";"

foreach($record in $csv){

    $cat_array += $record.Category

    foreach($Product in $cat_array){
        .....
    }
}

I think I am handling the logic wrong.

zett42 · Accepted Answer · 2022-08-23 16:13:35Z

2

Here is a possible solution:

# Group CSV data by column Category and loop over groups
Import-CSV -Path path.csv -Delimiter ";" | Group-Object Category | ForEach-Object {

    # Create a text file from the current group
    $fileName = "$($_.Name).txt"
    Set-Content $fileName -Value "$($_.Name) file contains:"

    # Loop over the rows of the CSV and add the data to the txt file
    $_.Group | ForEach-Object {
        # Format one line of the current text file
        ($_.Product, $_.Priced) -join ' - '
    } | Add-Content $fileName
}

Output:

Bike.txt

Red bike - Yes
Blue bike - Yes
Black bike - No

Car.txt

Red car - No
Blue car - Yes

Detailed explanation:

The Group-Object command groups data by specified criteria, in this case the value of property (column) Category. To better understand how it works, it helps to remove the ForEach-Object command:

Import-CSV -Path path.csv -Delimiter ";" | Group-Object Category

This outputs:

Count Name Group
----- ---- -----
    3 Bike {@{Category=Bike; Product=Red bike; Priced=Yes}, @{Category=Bike; Product=Blue bike; Priced=Yes}, @{Category=Bike;…
    2 Car  {@{Category=Car; Product=Red car; Priced=No}, @{Category=Car; Product=Blue car; Priced=Yes}}

So for each group (Bike, Car), it outputs one object, which has the properties Count (number of grouped objects), Name (value of property by which it groups) and Group (an array of grouped objects).

So when iterating over the output of Group-Object, the ForEach-Object script block gets called one time per group. At this level the file will be created.

We then iterate over the elements of the Group array, which contains the rows of the CSV for the current category, to create the lines of the file, using the -join operator to format each line. This ends up in the file because it is implicit output, piped to the next command.

Finally output the file by piping to Set-Content. If necessary, use the -Encoding parameter to specify a non-default encoding (PS 5.x defaults to ANSI, while PS 7.x defaults to UTF-8).

edited Aug 23, 2022 at 16:13

answered Aug 23, 2022 at 14:47

zett42

28.3k3 gold badges44 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

uba2012 Over a year ago

What if I want to add a some text to the txt, such as: Bike fike contains: Red bike - Yes Blue bike - Yes Black bike - No. How would this be done in the code?

zett42 Over a year ago

@uba2012 I've edited the code to show how you can insert a line at the top of the file using Set-Content and then use Add-Content to append the data from the CSV.

uba2012 Over a year ago

how do i output the group into a string variable for further manipulation? I have tried Out-String instead of add-content, but i cannot seem to manipulate it.

zett42 Over a year ago

@uba2012 $groupStr = $_.Group | ForEach-Object {($_.Product, $_.Priced) -join ' - '} | Out-String

Collectives™ on Stack Overflow

Powershell - use CSV to create multiple text files

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related