How to make a script that merges all .txt files into one .csv file into multiple columns in Powershell

Question

I don't know how to merge multiple .txt files with datas into one .csv file each of the .txt file seperated into columns.

This is my code so far,

$location = (Get-Location).Path
$files = Get-ChildItem $location -Filter "*.asd.txt"
$data = @()

foreach ($file in $files) {
    $fileData = Get-Content $file.FullName

    foreach ($line in $fileData) {
        $lineData = $line -split "\t"
        $data = $lineData[1]
        Add-Content -Path "$location\output.csv" -Value  $data
    } 

}

Each of the file looks like this

I want to keep the first column "WaveLength" and put the second columns next to each other from all the files in the folder. The header will start with the exac name "stovikmladyDoupno2 2020080500001.asd" or "stovikmladyDoupno2 2020080500002.asd" and so on ....

so it should look like this

I have tried to look for information over two days and still don't know. I have tried to put "," on the end of the file, I though excel will handle with that, but nothing helped.

Here I provide few files as test data https://mega.nz/folder/zNhTzR4Z#rpc-BQdRfm3wxl87r9XUkw

few lines of data

Wavelength  stovikmladyDoupno2 2020080500000.asd
350  6.38961399706465E-02 
351  6.14107911262903E-02 
352  6.04866108251357E-02 
353  5.83485359067184E-02 
354  0.054978792413247 
355  5.27014859356317E-02 
356  5.34849237528764E-02 
357  5.32841277775603E-02 
358  5.23466655229364E-02 
359  5.47595002186027E-02 
360  5.22061034631109E-02 
361  4.90149806042666E-02 
362  4.81633530421385E-02 
363  4.83974076557941E-02 
364  4.65219929658367E-02 
365  0.044800930294557 
366  4.47830287392802E-02 
367  4.46947539436297E-02 
368  0.043756926558447 
369  4.31725380363072E-02 
370  4.36867609723618E-02 
371  4.33227601805265E-02 
372  4.29978664449687E-02 
373  4.23860463187361E-02 
374  4.12183604375401E-02 
375  4.14306521081773E-02 
376  4.11760903772502E-02 
377  4.06421127128478E-02 
378  4.09771489689262E-02 
379  4.10083126746385E-02 
380  4.05161601354181E-02 
381  3.97904564387456E-02 

Answer 1

I assumed a location since I'm not fond of declaring file paths without a literal path. Please adjust path as needed.

$Files = Get-ChildItem J:\Test\*.txt -Recurse 

$Filecount = 0

$ObjectCollectionArray = @()

#Fist parse and collect each row in an array.. While keeping the datetime information from filename. 

foreach($File in $Files){

$Filecount++
Write-Host $Filecount 

$DateTime = $File.fullname.split(" ").split(".")[1]

$Content = Get-Content $File.FullName

foreach($Row in $Content){

    $Split = $Row.Split("`t")

    if($Split[0] -ne 'Wavelength'){

        $Object = [PSCustomObject]@{
            'Datetime' = $DateTime
            'Number' = $Split[0]
            'Wavelength' = $Split[1]
        }

        $ObjectCollectionArray += $Object
    }
}    
}


#Match by number and create a new object with relation to the number and different datetime. 

$GroupedCollection = @()
$Grouped =  $ObjectCollectionArray | Group-Object number

foreach($GroupedNumber in $Grouped){
    $NumberObject = [PSCustomObject]@{
            'Number' = $GroupedNumber.Name
    }


foreach($Occurance in $GroupedNumber.Group){
        $NumberObject | Add-Member -NotePropertyName $Occurance.Datetime -NotePropertyValue $Occurance.wavelength
}

$GroupedCollection += $NumberObject

}

$GroupedCollection | Export-Csv -Path J:\Test\result.csv -NoClobber -NoTypeInformation