≡ Menu

PowerShell: Converting text file contents into CSV

Recently a friend asked me a question. He has two text files with names in it. He want to have names from first text file copied to first column in CSV and contents of second text file copied to second column in CSV. This post talks about how to achieve this using PowerShell.

Below screenshot shows how the text file contents looks like and how the output CSV file will be.

sampleoutput

First read the contents of text files into variables.

$firstnames = Get-Content C:\temp\firstnames.txt            
$Lastnames = Get-Content C:\temp\lastnames.txt            

Find out which text file has more no. of lines/entries in it. This number tells us how many no. of times we need to loop through to process all items in the text files.

$maxcount = 0            
if($firstnames.count -gt $Lastnames.Count) {            
    $maxcount = $firstnames.count            
} else {            
    $maxcount = $lastnames.Count            
}

Now loop through the contents and save the details to CSV file.

$outputfilename = "c:\temp\namesoutput.csv"            
Add-Content -Path $outputfilename -Value "FirstName,LastName"            
            
for($i=0;$i -lt $maxcount; $i++) {            
    $value = "{0},{1}" -f $firstnames[$i],$lastnames[$i]            
    Add-Content -Path $outputfilename -Value $value            
}

The output file, c:\temp\namesoutput.csv contains the desired output.

Below is a small function that combines all above code pieces and makes the usage generic. You can change the name of output file as you need and names of the CSV columns. I kept them as Column1Name & Column2Name.

function Merge-TextFilesToCSV {            
[CmdletBinding()]            
Param(            
    [parameter(mandatory=$true)]            
    [string]$File1,            
    [parameter(mandatory=$true)]            
    [string]$File2            
)            
    $outputfilename = "c:\temp\megedoutput.csv"            
    $File1Contents = Get-Content $File1            
    $File2Contents = Get-Content $File2            
            
    $maxcount = 0            
    if($File1Contents.count -gt $File2Contents.Count) {            
        $maxcount = $File1Contents.count            
    } else {            
        $maxcount = $File2Contents.Count            
    }            
            
                
    Add-Content -Path $outputfilename -Value "Column1Name,Column2Name"            
            
    for($i=0;$i -lt $maxcount; $i++) {            
        $value = "{0},{1}" -f $File1Contents[$i],$File2Contents[$i]            
        Add-Content -Path $outputfilename -Value $value            
    }            
            
    Write-Host "Output available at $outputfilename"            
}

Copy past the above function into PowerShell window and run it as shown below.

mergecsv

Output shows the name of the output file.

{ 4 comments… add one }
  • Rahul July 7, 2015, 10:00 am

    Thanks dude, you arnt called Scriping champ for no reason…. đŸ™‚

  • Emilien November 14, 2016, 6:10 pm

    Thank you for these explanations , I already understood everything but i would like to know if it was possible to add a new column with values ​​coming from a new text file ?
    For exemple : I creates an other text file entitled “nickname” , I have already created my csv file and I would like to add these new data in my csv file, into a newest column
    I tried something but I did not get good results.

    Thank you in advance

    • Wintel Rocks December 15, 2016, 8:29 pm

      Please post what you have tried. We will try helping you with that.

  • George Crossley December 30, 2016, 6:27 am

    This is awesome! So how would I do it with 3 text files needing to be merged into 3 columns?

Leave a Comment