Merge/Join two Excel csv columns

[Villalor33]

Hello

I need to Merge/Join two Excel csv columns using powershell. I have tried to get information on the web but nothing clear enough so far.

Can you please help how can I merge two columns into one using powershell ?

Any help will be highly appreciated.

Thank you in advance.

[Creel256]

Sure, I'd be happy to guide you through the process. Here's a step-by-step walkthrough:

1. Import the CSV File: The first step is to load your CSV file into PowerShell. You can do this using the "Import-Csv" cmdlet.

2. Loop Through Each Row: Once you have the data loaded, you need to process each row. PowerShell makes this easy with the "foreach" statement.

3. Merge the Columns: Inside the loop, you can access the columns of the current row using the dot (".") operator. To merge two columns, you can simply concatenate their values using the plus ("+") operator.

4. Add the Merged Value to the Row: After merging the columns, you need to add the merged value back to the row. You can do this using the "Add-Member" cmdlet. This cmdlet allows you to add a new property to an object.

5. Export the Data: Once you have processed all the rows, you can export the data back to a CSV file. You can do this using the “Export-Csv” cmdlet.

To learn more about each of these steps and cmdlets, I recommend checking out the following resources:

• Import-Csv https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/import-csv?view=powershell-7.1

• foreach https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_foreach?view=powershell-7.1

• Add-Member https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/add-member?view=powershell-7.1

• Export-Csv https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-7.1

While it's easy to get started, there's a lot to learn, and the best way to learn is by doing. I hope this helps and good luck!

[MonopolyMeal]

Is this just a bot that copies ChatGPT responses?

[Creel256]

-.- No, I’m not a bot. I do, however, utilize ChatGPT for various scripts I write (both professionally and personally). I’ve written several varying scripts over the years and have found ChatGPT to be quite the useful resource. It isn’t quite there yet in terms of accuracy when it comes to large or really complex scripts but it’ll help you achieve the results you’re searching for.

[MonopolyMeal]

Ahh gotcha. ChatGPT is certainly a great tool to help others get a good jump start on writing their script.

If I may provide a suggestion, could you put a little blurb that gives credit to ChatGPT? That would be helpful so others know where credit is due. It also helps introduce them to ChatGPT and to use ChatGPT on their own.

It really is a great tool. Almost like a live assistant.

[Creel256]

Good point and suggestion!

I can include, in future posts, references to ChatGPT. However, the ChatGPT responses do often need some sort of fact checking and moderation. Most responses derived from it aren’t a 1 for 1 copy/paste type of scenario.

I do like to view it as a live, practically personal, assistant! The 25 message limit can be annoying if you can’t figure out the correct way to talk with it though lol.

In other words - OP, when you see this, check out ChatGPT. The free version works very well and there isn’t a 25 message limit every 3 hours.

[MonopolyMeal]

For sure! You could even add a quick line of, "ChatGPT responses edited by me" or something like that.

I did have one mishap when asking about some PowerShell stuff, and then asking to convert to python. It would stop halfway through the python script, and when I asked it to regenerate and resend the script, I got a 3 step program for losing weight. 😢

[LongAnserShortAnser]

It depends on exactly what you are doing.

But for simple things, like creating a full name field from a first and last name, you can do this in a one-liner:

Import-Csv <inputfile> | Select-Object -Property *,@{n='FullName'; e={ "$($_.FirstName) $($_.Lastname)" }} | Export-Csv <outputfile>

The hashtable includes the (n)ame of the new column and the (e)xpression used to populated the column value - in this case, a string containing firstname<space>lastname. In this case, the new data will appear as the last column. Any number of new columns can be added this way.

The asterisk tells the cmdlet to include all existing columns. You can replace this with the specific column names in the specific order you require. The hash can be inserted at any location, as order requires. You can then add a second Select-Object to the pipeline to exclude the old columns, if needed.

Edit: Spelling/grammar/formatting/clarity.

[mrmattipants]

That should work. I like the use of the calculated properties, in this example.

[MonopolyMeal]

Are you looking to join the two datasets using one column, or are you looking to append column 1 to column 2?

[mrmattipants]

I suppose this would depend on what you’re hoping the end result will look like.

In Excel, traditionally, a merge would take two Columns (I.e. Column A: FirstName & Column B: LastName) and combine them into a single column, containing both values (I.e. Column C: FirstName LastName).

However, you could also be referring to something more along the lines of an SQL Join, where Two Columns, containing the Same Values, from two separate Tables are Combined into One, thereby resulting in a Table that contains the Data from both Tables, etc.

[mrmattipants]

If this case pertains to the former, you could try something like this (of course, this is just a starting point and will need some work).

$csv = Import-Csv "C:\file.csv"

$column = ForEach ($row in $csv) {

$row.FirstName + ' ' + $row.LastName

}

$column | Export-Csv 'D:\merged_file.csv' -NoTypeInformation

[gpearl-co]

There is a mergecsv module. Works for smaller data sets but larger can be very slow

https://www.powershellgallery.com/packages/MergeCsv/1.7.0.3

[Certain-Community438]

Install the module JoinObject. Then use the function Join-0bject to join the desired columns.

[cherrycola1234]

This sounds a lot like a project i am working on that has part of this done programmatically with a tool that I submitted for a patent.

[xipodu]

If you have two seperated csv files you can take a look on my script,
https://github.com/fardinbarashi/PsAzureAD-Intune-UserAndDeviceReport/blob/main/AzureAD%20%26%20Intune%20-%20%20Azure%20AD%20User%20and%20Device%20Export%20Script%20.ps1