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

I don't understand the combining logic. Could you clarify a bit more? Might be easier to also show an expected output here. — RoadRunner, Apr 10 '20 at 14:07
Correct me if I'm wrong but, you the end product to be a csv with 1 more property taken from txt values and if 1 is greater in size than the other it should fill it with blank lines, its doable but could you please elaborate the answer, add what you've tried and text files with expected output? — Roque Sosa, Apr 10 '20 at 14:16
please show how you want the two files to look after they are merged. — Lee_Dailey, Apr 10 '20 at 14:32
Using this [`Join-Object`](https://stackoverflow.com/a/45483110/1701026): `ConvertFrom-Csv .\File1.txt -Header A | LeftJoin (ConvertFrom-Csv .\File2.txt -Header B,C,D -Delimiter '|')` — iRon, Apr 10 '20 at 15:09

score 1 · Accepted Answer · edited Jul 25 '20 at 19:28

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

The first example results in the exact same output as your expectation using the exact same input files as given in your question. The later two recommended examples, use the `-Delimiter '|'` for your ***input*** files that clearly show a pipe (`|`) sign in your given input examples. — iRon, Apr 11 '20 at 20:13
I have updated the answer and added a small specific script in the top how you might also do it without using the `Join-Object` cmdlet. — iRon, Apr 13 '20 at 09:52

score 0 · Answer 2 · answered Apr 10 '20 at 16:02

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

Hello, sorry but it is not what I expected, and result should be CSV (not txt) contained two txt file. Data from one file in left column and data from second file in right column. — majan, Apr 10 '20 at 19:15

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

2 Answers2