Transpose part of CSV

Question

I have an Excel file with 10000 rows which looks like this:

softwarename1, a, b, c, d, e, f, g, h, i
softwarename2, f, e, r, g, u, o, d
softwarename3,
softwarename4, x

I would like to become the following:

softwarename1 a
softwarename1 b
softwarename1 c
softwarename1 d
softwarename1 e
softwarename1 f
softwarename1 g
softwarename1 h 
softwarename1 i
softwarename2 f
softwarename2 e
softwarename2 r
softwarename2 g
softwarename2 u
softwarename2 o
softwarename2 d
softwarename3 
softwarename4 x
softwarename4 ac
softwarename4 gd
softwarename4 d

For the moment I only know how to import the CSV file:

$source = Import-Csv -Path C:\tmp\ib20161016.csv -Delimiter ","

Any help would be appreciated, as I have no clue how to start.

This question is quite broad. You need to learn a bit more about programming before asking that kind of question. It's also not so clear how you got the last 3 lines of your example. — Loïc Faure-Lacroix, Oct 17 '16 at 16:53
[Starting](http://stackoverflow.com/a/11885405/1630171) [points](https://technet.microsoft.com/en-us/library/ff730948.aspx). — Ansgar Wiechers, Oct 17 '16 at 17:12

davidhigh · Answer 1 · 2016-10-17T18:20:05.077

There's no need for an import-csv here (-- I even think that csv is the wrong storage format here, as afaik each column should be specified). Here is the alternative one-liner:

Get-Content C:\tmp\ib20161016.csv |
           ForEach {$s = $_.split(","); $s[1..($s.length-1)] | ForEach{$s[0] + " " + $_}}

EDIT: that does not give the corner case where there's only one entry in the line. See the other answer for that.

score 0 · Answer 2 · answered Oct 17 '16 at 18:17

$source = get-content C:\tmp\ib20161016.csv 
$Output = foreach ($row in $source) {
    $rowarray = $row.split(',').trim()
    $softwarename = $rowarray[0]
    $values = $rowarray[1..($rowarray.length - 1)]
    if ($null -ne $values) {
        foreach ($value in $values) {
            "$softwarename $value"
        }
    } else {
        $softwarename
    }
}
$Output | Out-File C:\tmp\ib20161016.txt

Notes: I am unsure where the last 3 values came from, I have assumed that softwarename4, x should have been softwarename4, x, ac, gd, d

You won't want to use Import-CSV as you dont have column headers. And if you did it will turn your spreadsheet into an object where the properties will be unordered. It's not really a CSV since the different columns don't match up, it's really a just a txt file.

Iris Classon · Accepted Answer · 2016-10-18T07:17:01.603

This was a different one, but I enjoy a good Powershell challenge. Based on your data the below would yield the desired results based on your details:

$data = gc  -Path "C:\Users\Iris Classon\Documents\data.csv" 

$csv = ""

$nl = [Environment]::NewLine

$data | % {

    $columns = $_.ToString().Split(',')

    For ($i=1; $i -lt $columns.Length; $i++) {

        $csv  += "{0} {1}{2}" -f $columns[0],$columns[$i],$nl
    }
}

Write-Host $csv

Result:

softwarename1  a
softwarename1  b
softwarename1  c
softwarename1  d
softwarename1  e
softwarename1  f
softwarename1  g
softwarename1  h
softwarename1  i
softwarename2  f
softwarename2  e
softwarename2  r
softwarename2  g
softwarename2  u
softwarename2  o
softwarename2  d
softwarename3 
softwarename4  x

Here is a function that does the same, but less error prone due to some validation checks.

<#
    .SYNOPSIS 
    Splits CSV in an unusual way..

    .DESCRIPTION

    .INPUTS
    Source file, target file and delimiter

    .OUTPUTS

    .EXAMPLE
    Split-CSV -SourceFile "C:\data.csv" -TargetFile "C:\data3.csv" -Delimiter " "

    Split-CSV -SourceFile "C:\data.csv" -TargetFile "C:\data3.csv" -Delimiter ","

#>

function Split-CSV {
    [CmdletBinding(SupportsShouldProcess,ConfirmImpact = "high")]
    param(
    [Parameter(Mandatory = $true,HelpMessage = "`t Please provide path")]
    [ValidateNotNullOrEmpty()]
    [string]$SourceFile,
    [Parameter(Mandatory = $true,HelpMessage = "`t Please provide path")]
    [ValidateNotNullOrEmpty()]
    [string]$TargetFile,
    [Parameter(Mandatory = $true,HelpMessage = "`t Please provide delimiter for new csv")]
    [ValidateNotNullOrEmpty()]
    [string]$Delimiter
  )

    $data = gc -Path $SourceFile

    $csv = ""

    $nl = [Environment]::NewLine

    $data | % {


    if ($_){

        $columns = $_.ToString().Split(',')

            if ($columns.Length -gt 1){

                For ($i=1; $i -lt $columns.Length; $i++) {
                    $csv  += "{0}{1}{2}{3}" -f $columns[0],$Delimiter,$columns[$i],$nl
                }
            }
        }
    }

    $csv | Out-File  $TargetFile
}

Split-CSV -SourceFile "C:\data.csv" -TargetFile "C:\data3.csv" -Delimiter ","

yields same result as earlier but with comma as a delimiter

Transpose part of CSV

3 Answers3