Converting CSV Column to Rows

powershell csv transpose columns to rows
how to change column to row in excel 2007
convert to columns
convert columns to rows in excel with formulas
convert column into comma
how to transpose multiple rows in excel
csv to json
csv column to list

I'm trying to create a PowerShell script that transpose a CSV file from column to rows.

I found examples of doing the opposite (converting row based CSV to column) but I found nothing on column to rows. My problem being that I don't know exactly how many column I'll have. I tried adapting the row to column to column to rows but unsuccessfully.

$a = Import-Csv "input.csv"
$a | FT -AutoSize

$b = @()
foreach ($Property in $a.Property | Select -Unique) {
    $Props = [ordered]@{ Property = $Property }
    foreach ($Server in $a.Server | Select -Unique){ 
        $Value = ($a.where({ $_.Server -eq $Server -and 
                    $_.Property -eq $Property })).Value
        $Props += @{ $Server = $Value }
    }
    $b += New-Object -TypeName PSObject -Property $Props
}

$b | FT -AutoSize
$b | Out-GridView
$b | Export-Csv "output.csv" -NoTypeInformation 

For example my CSV can look like this:

"ID","DATA1"
"12345","11111"
"54321","11111"
"23456","44444"

or this (number of column can vary):

"ID","DATA1","DATA2","DATA3"
"12345","11111","22222","33333"
"54321","11111",,
"23456","44444","55555",

and I would like the script to convert it like this:

"ID","DATA"
"12345","11111"
"12345","22222"
"12345","33333"
"54321","11111"
"23456","44444"
"23456","55555"

The trick is to query the members of the table to get the column names. Once you do that then the rest is straightforward:

function Flip-Table ($Table) {

    Process {

        $Row = $_

        # Get all the columns names, excluding the ID field.
        $Columns = ($Row | Get-Member -Type NoteProperty | Where-Object Name -ne ID).Name

        foreach ($Column in $Columns) {

            if ($Row.$Column) {

                $Properties = [Ordered] @{
                    "ID"   = $Row.ID
                    "DATA" = $Row.$Column
                }

                New-Object PSObject -Property $Properties
            }
        }

        # Garbage collection won't kick in until the end of the script, so
        # invoke it every 100 input rows.

        $Count++;

        if (($Count % 100) -eq 0) {
            [System.GC]::GetTotalMemory('forceFullCollection') | out-null
        }
    }
}

Import-Csv input.csv | Flip-Table | Export-Csv -NoTypeInformation output.csv

Convert CSV Columns to Rows, Step 3: Choose output options (optional) Output Options. Free online CSV columns to rows converter. Just load your CSV and its columns will automatically get converted to rows. Load CSV, convert columns. There are no ads, popups or nonsense, just an awesome CSV columns and rows exchanger. Created by programmers from team Browserling.

Well, here is mine. I'm not as fancy as the rest:

$in = Get-Content input.csv | Select -Skip 1
$out = New-Object System.Collections.ArrayList
foreach($row in $in){
    $parts = $row.Split(',')
    $id = $parts[0]
    foreach($data in $parts[1..$parts.Count]){
        if($data -ne '' -AND $data -ne $null){
            $temp = New-Object PSCustomObject -Property @{'ID' = $id;
                                                        'Data' = $data}
            $out.Add($temp) | Out-Null
        }
    }

}
$out | Export-CSV output.csv -NoTypeInformation

Convert CSV Rows to Columns, Simple, free and easy to use online tool that converts CSV columns to rows. No ads, popups or nonsense, just a CSV column converter. Load CSV, convert  you are now stacking the "rows", same as your original question. You have an answer in my last edit. Transpose only makes sense in this context if you have a matrix, i.e. you have to have all original rows in memory in one variable and then transpose that variable before writing to the new csv.

You can do something like this

# Convert csv to object
$csv = ConvertFrom-Csv @"
"ID","DATA1","DATA2","DATA3"
"12345","11111","22222","33333"
"54321","11111",,
"23456","44444","55555"
"@

# Ignore common members and the ID property
$excludedMembers = @(
    'GetHashCode',
    'GetType',
    'ToString',
    'Equals',
    'ID'
)

$results = @()

# Iterate around each csv row
foreach ($row in $csv) {
    $members =  $row | Get-Member
    # Iterate around each member from the 'row object' apart from our 
    # exclusions and empty values
    foreach ($member in $members | 
        Where { $excludedMembers -notcontains $_.Name -and $row.($_.Name)}) {
        # add to array of objects
        $results += @{ ID=$row.ID; DATA=$row.($member.Name)}
    }
}

# Write the csv string
$outstring = "ID,DATA"
$results | foreach { $outstring += "`n$($_.ID),$($_.DATA)" }

# New csv object
$csv = $outstring | ConvertFrom-Csv

Probably not the most elegant solution, but should do what you need

I left some comments explaining what it does

Converting CSV Column to Rows, Simple, free and easy to use online tool that converts CSV rows to columns. No ads, popups or nonsense, just a CSV row converter. Load CSV, convert rows. How to quickly convert csv file to columns in Excel? To convert CSV file to Excel columns, actually, you can handle it through three different methods. In this article, I will talk about these three methods for you. Open CSV file in Excel . Insert CSV file in Excel

If you only want to accept a limited number DATA columns (e.g. 5), you could do:

ForEach ($i in 1..5) {$CSV | ? {$_."Data$i"} | Select ID, @{N='Data'; E={$_."Data$i"}}}

And if you have a potential unlimited number of DATA columns:

ForEach ($Data in ($CSV | Select "Data*" -First 1).PSObject.Properties.Name) {
    $CSV | ? {$_.$Data} | Select ID, @{N='Data'; E={$_.$Data}}
}

How to Convert a Row to a Column in Excel the Easy Way, The trick is to query the members of the table to get the column names. Once you do that then the rest is straightforward: function Flip-Table  After you open this file all of your data should be in column A, select this column and Data, Text to Columns, Delimited, by semicolon should give you the result you seek. Originally Posted by plato. Can someone tell me (using Excel 2330) how I make a csv file which has semi. colon separation points into columns? Register To Reply.

Transpose data from rows to columns (or vice versa) in Excel for , You've set up a worksheet, when you realize it would look better if the rows and columns were reversed. There's no need to re-enter all that  Use this tool to convert a column into a Comma Separated List. Copy your column of text in Excel; Paste the column here (into the leftmost textbox) Copy your comma separated list from the rightmost textbox; Paste your comma separated list wherever you wish

Convert CSV columns to Rows - Build, It lets you rotate the data from columns to rows, or vice versa. You can convert the table to a range first by using the Convert to Range button on the Table tab,  If you have a worksheet with data in columns that you need to rotate to rearrange it in rows, use the Transpose feature. With it, you can quickly switch data from columns to rows, or vice versa. For example, if your data looks like this, with Sales Regions in the column headings and and Quarters along the left side:

Convert a column into a comma separated list, I am trying to convert the above columns to rows in CSV file. Can someone help me how to convert this? The process of converting Column to Rows or Vice-Versa, both methods also work when you want to convert a single column to a row or vice-versa. This option is very handy and saves a lot of time while working. Recommended Articles. This has been a guide to Convert Columns to Rows in Excel.

Comments
  • Hmmm...so what you are asking for is not exactly what you say you want. Converting the columns to rows would not give the output that you show. What you want is to convert one row per ID to multiple rows per ID. There are a few ways to go about this. Will the ID always be the first coulmn?
  • Yes, it will always be the first column
  • Also, Is there any chance at all that the data will ever have a comma in a field?
  • No, there won't be any comma in a field.
  • Thanks again, this works perfectly. But I do have one of the files I process with this that is very big, resulting in a huge RAM usage by the script (actually had to kill some other process to avoid the script crashing). Is it possible to adapt this to write some of the lines in the output file then free some memory before resuming. I guess this would slow down the process but avoid needing high amount of memory when working with huge files and risking a crash.
  • @FrancoisA I've changed the function so that it streams the data. That should, as far as I know, fix the RAM issue. Note that it's called in a different way in the last line now.
  • Unfortunately even with that new version the high memory usage issue persisted Exception of type 'System.OutOfMemoryException' was thrown. At C:\Script\transposeRowsGroups.ps1:19 char:17 + if ($Row.$Column) { + + CategoryInfo : OperationStopped: (:) [], OutOfMemoryException + FullyQualifiedErrorID : System.OutOfMemoryException
  • OK - I've just read up on garbage collection in PowerShell and added a manual garbage collection step. I'm hoping that should sort out the memory allocation problem.
  • Thanks a lot. Ok, it's doesn't work. But it is because there is an issue with the garbage collector when using a pipe call, I had to change [System.GC]::Collect() to [System.GC]::GetTotalMemory(‘forcefullcollection’) | out-null so now it works. Thanks for your help ! GC Issue Work-Around