Converting at least two text files with different rows into one csv - powershell

Question

I am trying to convert two TXT files into one CSV file using powershell script. When files have same structure, and same number of rows then case looks be easy. But in my case txt files have diffrent structure.

Pipe sign in both txt files is not a delimiter should be treat as normal character and it is a string.

File URL.txt

L5020|http://linktosite.de|URL
L100|http://sitelink.de|URL
L50|http://abcde.de|URL
L511|http://bbcccddeee.de|URL
L300|http://link123456.de|URL
L5450|http://randomlink.de|URL_DE
L5460|http://randomwebsitelink.de|URL_DE

File URL1.txt

L5020|http://linktosite.de|URL|P555
L100|http://sitelink.de|URL|P523
L50|http://abcde.de|URL|P53
L511|http://bbcccddeee.de|URL|P540

CSV which I expect should look like as below and delimiter is ";"

HEADER1;HEADER2
L5020|http://linktosite.de|URL;L5020|http://linktosite.de|URL|P555
L100|http://sitelink.de|URL;L100|http://sitelink.de|URL|P523
L50|http://abcde.de|URL;L50|http://abcde.de|URL|P53
L511|http://bbcccddeee.de|URL;L511|http://bbcccddeee.de|URL|P540
L300|http://link123456.de|URL;  
L5450|http://randomlink.de|URL_DE;  
L5460|http://randomwebsitelink.de|URL_DE;   

I tried something like that

$URL = "C:\Users\XXX\Desktop\URL.txt"
$URLcontent = Get-Content $URL
$URL1 = "C:\Users\XXX\Desktop\URL1.txt"
$URLcontent1 = Get-Content $URL1

$results = @() # Empty array to store new created rows in
$csv = Import-CSV "C:\Users\XXX\Desktop\map.csv" -Delimiter ';'
foreach ($row in $csv) {
    $properties = [ordered]@{
        HEADER1   =  $URLcontent
        HEADER2    = $URLcontent1

          }
    # insert the new row as an object into the results-array
    $results += New-Object psobject -Property $properties
}
# foreach-loop filled the results-array - export it as a CSV-file
$results | Export-Csv "C:\Users\XXXX\Desktop\map_final.csv" -NoTypeInformation

And something like that:

import-csv URL.txt -Header 'HEADER1' |  Export-CSV "C:\Users\xxx\Desktop\URL.csv"  -Delimiter ';' -NoTypeInformation 
import-csv URL1.txt  -Header 'HEADER2' | Export-CSV "C:\Users\xxx\Desktop\URL1.csv" -Delimiter ';' -NoTypeInformation  
Get-ChildItem "C:\Users\xx\Desktop" -Filter "URL*.csv" | Select-Object -ExpandProperty FullName | Import-Csv | Export-Csv .\combinedcsvs.csv -NoTypeInformation -Append

Without any succes...

BR

Answer 1

Based on the updates in your question, if you want to build something yourself, you probably want to do something like this:

$Url1 = @(Get-Content .\URL1.txt)
$i = 0
Get-Content .\URL.txt | Foreach-Object {
    [pscustomobject]@{
        HEADER1 = $_
        HEADER2 = If ($i -lt $URL1.Count) { $URL1[$i++] }
    }
} | Export-Csv .\combinedcsvs.csv -Delimiter ';' -NoTypeInformation -Append

In case you do not want to go through the hassle of reinventing the wheel (with all pitfalls including performance tuning). Using the Join-Object I mentioned in the comment:

Import-Csv .\URL.txt -Header HEADER1 |
LeftJoin (Import-Csv .\URL1.txt -Header HEADER2) |
Export-Csv .\combinedcsvs.csv -Delimiter ';' -NoTypeInformation -Append

• Note1: I am not sure why you trying to import anything like map.csv, I think that is required.
• Note2: If you still want to go your own way, try to avoid using the increase assignment operator (+=) to create a collection it is a very expensive operator.
• Note3: it is generally not a good idea to join lines on their line index as the list might not be sorted or have duplicates, therefore it is better to join lists on a specific property, like the the Url:

 

Import-Csv .\URL.txt -Delimiter '|' -Header Lid,Url,Type |
LeftJoin (Import-Csv .\URL1.txt -Delimiter '|' -Header Lid2,Url,Type2,Pid) -On Url |
Format-Table # or: Export-Csv .\combinedcsvs.csv -Delimiter ';' -NoTypeInformation

Lid   Url                         Type   Lid2  Type2 Pid
---   ---                         ----   ----  ----- ---
L5020 http://linktosite.de        URL    L5020 URL   P555
L100  http://sitelink.de          URL    L100  URL   P523
L50   http://abcde.de             URL    L50   URL   P53
L511  http://bbcccddeee.de        URL    L511  URL   P540
L300  http://link123456.de        URL
L5450 http://randomlink.de        URL_DE
L5460 http://randomwebsitelink.de URL_DE

Or on all three (Lid, Url and Type) properties:

Import-Csv .\URL.txt -Delimiter '|' -Header Lid,Url,Type |
LeftJoin (Import-Csv .\URL1.txt -Delimiter '|' -Header Lid,Url,Type,Pid) -On Lid,Url,Type |
Format-Table # or: Export-Csv .\combinedcsvs.csv -Delimiter ';' -NoTypeInformation

Lid   Url                         Type   Pid
---   ---                         ----   ---
L5020 http://linktosite.de        URL    P555
L100  http://sitelink.de          URL    P523
L50   http://abcde.de             URL    P53
L511  http://bbcccddeee.de        URL    P540
L300  http://link123456.de        URL
L5450 http://randomlink.de        URL_DE
L5460 http://randomwebsitelink.de URL_DE

Answer 2

If you only want to combine lines where both files contain data, you can do the following:

$f1 = Get-Content file1.txt
$f2 = Get-Content file2.txt
$output = for ($i = 0; $i -lt [math]::Min($f1.count,$f2.count); $i++) {
   $f2[$i],$f1[$i] -join '|'
}
$output | Set-Content newfile.txt

If you want to combine all coinciding lines plus add extra lines from one of the files, you can do the following:

$output = for ($i = 0; $i -lt [math]::Max($f1.count,$f2.count); $i++) {
    if ($f1[$i] -and $f2[$i]) {
        $f2[$i],$f1[$i] -join '|'
    } 
    else {
        $f2[$i],$f1[$i] | Where {$_}
    }
}
$output | Set-Content newfile.txt