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-29 10:33:38Z

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, 2013 at 10:33

answered May 29, 2013 at 8:54

Shay Levy

128k34 gold badges193 silver badges210 bronze badges

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

3 Comments

Micha Wiedenmann Over a year ago

Can you please explain the comma in foreach { ,(gc $_.fullname) }?

Shay Levy Over a year ago

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.

Micha Wiedenmann Over a year ago

Thanks to your comment I found stackoverflow.com/questions/11138288/… which I want to share.

Richard · Accepted Answer · 2013-05-29 08:59:03Z

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, 2013 at 8:59

answered May 29, 2013 at 8:53

Richard

7,0685 gold badges48 silver badges61 bronze badges

1 Comment

Micha Wiedenmann Over a year ago

Thank you. I have updated my question to clarify that the script needs to process a varying number of files, not a fixed number.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related