Search code examples
powershellcsvxlsxtxt

PowerShell: How to upload data from multiple txt files into a single xlsx or csv file

PowerShell: How to upload data from multiple txt files into a single xlsx or csv file using Windows PowerShell

I would like to be able to use a loop that will iterate through a folder of many txt files and upload all of the data from them into a single xlsx or csv file.

The txt files are tab delimited with a few columns and include column headers. I only want the headers to upload to the new file once so it shows up at the top but never again.

I found the following code on another site that allows me to upload multiple files into a single one and only uploads the header once. The issue is that the data does not format correctly as the rows are put into a single cell when I need each point to be split up.

$getFirstLine = $true

get-childItem "YOUR_DIRECTORY\*.txt" | foreach {
$filePath = $_

$lines =  $lines = Get-Content $filePath  
$linesToWrite = switch($getFirstLine) {
       $true  {$lines}
       $false {$lines | Select -Skip 1}

}

$getFirstLine = $false
Add-Content "YOUR_DESTINATION_FILE" $linesToWrite
}

This image is an example of the txt data with the first row being the column headers: txt file data that I am trying to upload into a csv or xlsx

Solution

  • If you're certain the text files all have the same format, you can treat them as tab-delimited csv files, import them and save out merged like below:

    (Get-ChildItem -Path 'X:\Somewhere' -Filter '*.txt' -File).FullName | 
Import-Csv -Delimiter "`t" | 
Export-Csv 'X:\SomewhereElse\merged.csv' -UseCulture -NoTypeInformation

    Using switch -UseCulture means the merged csv is written out using the delimiter your local Excel expects, so when done just double-click the file to open in Excel.