1

I have 2 CSV Files that contain different information. There is one column which is similar in both files. I want to merge the two files by checking the ID Column to get a new file with all information from both files. So it should be like this:

File A 

Column 1 = ID
Column 2 = Text
Column 3 = other Text

File B

Column 1 = ID
Column 2 = some other text

I want to merge them now using powershell and "import-excel" module to get one new csv:

File C

Column 1 = ID
Column 2 = Text
Column 3 = other text
Column 4 = some other text
Morpheus
  • 1,616
  • 1
  • 21
  • 31
ThoMo
  • 21
  • 3
  • 1
    Hi, welcome, what have you tried and what was the problem? – maestromusica Mar 12 '20 at 16:34
  • Well actually i tried to google what or how i can merge them but i didn't find anything helpful for that. Whenever i try to google for "Import-Excel" i get a lot of links relative to excel but i have two simple textfiles that i want to merge. – ThoMo Mar 12 '20 at 17:58
  • Try [Merge-Csv](https://www.powershelladmin.com/wiki/Merge_CSV_files_or_PSObjects_in_PowerShell). – JosefZ Mar 12 '20 at 20:05
  • Or using [Join-Object](https://www.powershellgallery.com/packages/Join): `Import-Csv .\FileA.csv | Join-Object (Import-Csv .\FileB.csv) -On Id | Export-Excel .\FileC.xlsx`, see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026) – iRon Mar 13 '20 at 09:08
  • Thanks that worked very well and was easy to use. – ThoMo Mar 19 '20 at 17:59

1 Answers1

0

There are a number of ways to relate 2 (or more) data sets, but probably the most common in PowerShell is to find a common & unique property and use hash tables to relate one to the other.

Assuming you have the ImportExcel module; Here's a simple example based on the somewhat limited information you gave:

I created:

FileA:
ID   Text1          Tex2
1    something1.1   something2.1
2    something2.1   something2.2

FileB:
ID   Text3
1    SoemthingElse
2    SomethingElse2

$FileA      = 'c:\temp\fileA.csv'
$FileB      = 'c:\temp\fileB.csv'
$MergeFile  = 'c:\temp\FileA_BMerged.xlsx'
$FileB_Hash = @{}

# Create a hash table from the data in FileB indexed on the ID column.
Import-Csv -Path $FileB |
ForEach-Object{ $FileB_Hash.Add( $_.ID, $_) }

# Now Import FileA via Import-Csv and correlate the data using the ID to tie the 
# 2 sets together.
#
# Notice the use of Select-Object with a calculated property.
Import-Csv -Path C:\temp\FileA.csv |
Select-Object *,@{Name = 'Text3'; Expression = { $FileB_Hash.($_.ID).Text3 } } |
Export-Excel -Path $MergeFile

Export-Excel is the last and least obscure part it's simply taking the objects created earlier in the pipeline steps and outputting them into an Excel sheet.

You do have to be sensitive to the field names. Make sure your keys are unique otherwise you'll have to handle differently. The basic principal is when you encounter the ID in the first collection you can use it to easily reference the object in the second set then use that data to extend the data in the first.

We can mix this in different ways including not use either set as a basis and simply creating a collection of PSCustomObjects. However the main principal should be the similar.

halfer
  • 19,824
  • 17
  • 99
  • 186
Steven
  • 6,817
  • 1
  • 14
  • 14
  • Thank you very much for your answer. I will have a try later with that. Just to be clear. The column ID in my files are matching in both files. So i want to check the ID in both files to get the Data together. I hope this will work so far. Thank you! – ThoMo Mar 13 '20 at 12:59
  • OK let me know. I'd also point out the other 2 solutions mentioned in the comments are very good. – Steven Mar 13 '20 at 15:25
  • I tried merge-csv in poowershell and this tool works very easy and powerfull in powershell. Thank you! – ThoMo Mar 19 '20 at 18:00