How to merge colums of multiple text files into one csv file using PowerShell?

Question

I have got multiple measurement files with one column of numeric data each. (Update: The script should work for variable numbers of measurement files.)

data1.dat

1.0
2.0
3.0

data2.dat

10.0
20.0
30.0

...

dataN.dat

1
1
1

How can I merge these data files into a comma separated values file using Powershell?

"data1","data2.dat",...,"dataN.dat"
1.0,10.0,...,1
2.0,20.0,...,1
3.0,30.0,...,1

Related

Merging Two Text Files into One CSV File does not help my case, but might help other people who search for column merging.
Powershell / Perl : Merging multiple CSV files into one? is similar but there the lines have an id.

Shay Levy · Accepted Answer · 2013-05-29T10:33:38.787

2

Here's one way:

$files = Get-ChildItem D:\temp *.dat
$header = $files|foreach {$_.basename}
$content= $files | foreach { ,(gc $_.fullname) }

$lines = for($i=0; $i -lt $content[0].Count; $i++)
{
    $line = for($x=0; $x -lt $files.count; $x++)
    {
        $content[$x][$i]        
    }

    $line -join ','
}

$lines | ConvertFrom-Csv -Header $header | Export-Csv data.csv

edited May 29 '13 at 10:33

answered May 29 '13 at 08:54

Shay Levy

121,444
32
184
206

Can you please explain the comma in `foreach { ,(gc $_.fullname) }`? – Micha Wiedenmann May 29 '13 at 14:15
sure, the comma operator is the array construction operator in PowerShell. It creates a one element array and puts the result in the first element. Without it, all lines of all files would assign to $content. Remove it and print $content, you'll see what I mean. – Shay Levy May 29 '13 at 18:49
Thanks to your comment I found http://stackoverflow.com/questions/11138288/how-to-create-array-of-arrays-in-powershell which I want to share. – Micha Wiedenmann May 29 '13 at 20:24

Richard · Answer 2 · 2013-05-29T08:59:03.190

1

You could try something like this:

$file1 = "C:\firstTxtFile.txt"
$file2 = "C:\SecondTxtFile.txt"
$outputFile = "C:\Output.txt"

$file1String = Get-content $file1 
$file2String = Get-content $file2

'"data1.dat","data2.dat"' > $outputFile

$counter = 0
while(($file1String[$counter] -ne $null) -and ($file2String[$counter] -ne $null)){
    $Line = $file1String[$counter] + "," + $file2String[$counter]
    $line >> $outputFile
    $counter++
}

This will take two text file and combine them into one output file.

Note: The output file will be overwritten each time this is run.

edited May 29 '13 at 08:59

answered May 29 '13 at 08:53

Richard

6,812
5
45
60

1

Thank you. I have updated my question to clarify that the script needs to process a varying number of files, not a fixed number. – Micha Wiedenmann May 29 '13 at 13:38

How to merge colums of multiple text files into one csv file using PowerShell?

2 Answers2

Linked

Related