locked
Powershell merge csv file by first column RRS feed

  • Question

  • Hello,

    is there a way to merge a CSV file by its first column?

    I exported a List of our AD Members including the properties Office, Department and DisplayName, which now need to be combined by the Office.

    My current Command:

    get-aduser -Filter * -SearchBase "MYSEARCHBASE" -Properties Office, Department, DisplayName | Select Office, Department, DisplayName | sort -Property Office

    The Output:

    Office          Department          Displayname

    Office1        Department1        User1

    Office1        Department1        User2

    Office1        Department1        User3

    Office2        Department2        User4

    Office2        Department2        User5

    The Output I need:

    Office          Department          Displayname1          Displayname2           Displayname3

    Office1        Department1        User1                       User2                        User3

    Office2        Department2        User4                       User5

    Many thanks in advance,

    Finn

    • Moved by Bill_Stewart Tuesday, November 7, 2017 10:53 PM This is not "search the Internet for me" forum
    Wednesday, October 4, 2017 9:18 AM

All replies

  • I do not have a Join-Object command, do I have to import it somehow or do I have to write it as a SELECT statement?

    I am not trying to join two tables, just one List of Objects according to the first column.

    Please be patient with me, I have no experience with Windows or Powershell for that matter.

    Thanks for your help!

    Wednesday, October 4, 2017 11:30 AM
  • Did you try to read the link.  The answer and download link are pretty obvious.


    \_(ツ)_/

    Wednesday, October 4, 2017 11:33 AM
  • Yup.  I just went back and looked.  The script is there in the blog.

    \_(ツ)_/

    Wednesday, October 4, 2017 11:35 AM
  • Now I get it... The code is the "function add".

    Is that persistent and for all users?

    I still cant make sense out of it... and yes... I did read the article multiple times.

    I am not trying to join two tables and nowhere in the Article are they trying to do anything similar.

     
    Wednesday, October 4, 2017 12:28 PM
  • A CSV is an object.  Import both and follow the instructions in the article.

    We will not write your code for you.

    Start here:

    Learn PowerShell: https://mva.microsoft.com/en-us/training-courses/getting-started-with-microsoft-powershell-8276


    \_(ツ)_/

    Wednesday, October 4, 2017 12:42 PM
  • I am not asking for you to write my code...

    I am asking for being pointed in the right direction and maybe some help getting started. I simply do not see how the Join-Object is applicable to my problem. I only have one CSV File and I am not trying to join that in anyway they are describing it in the article.

    Wednesday, October 4, 2017 12:52 PM
  • I am not asking for you to write my code...

    I am asking for being pointed in the right direction and maybe some help getting started. I simply do not see how the Join-Object is applicable to my problem. I only have one CSV File and I am not trying to join that in anyway they are describing it in the article.

    Then why are you asking how to "merge" CSV by one column.  Perhaps you need to ask a clear question.

    What you seem to want can be done by joins.  You can self join a table on a column.  You an write a loop that will merge the columns but that would require some skill in PowerShell.  All I can say is to write a loop that generates a new output.

    The easiest way to do this is to load the CSV into Excel and turn it into a pivot table around "Office".


    \_(ツ)_/

    Wednesday, October 4, 2017 1:20 PM
  • Merge made perfect sense to me. Besides, there was more than a title to my question.

    Did it in bash for now... my goal was to get around the hopping from one server to another for one little repetitive job.

    Wednesday, October 4, 2017 1:50 PM
  • Merge made perfect sense to me. Besides, there was more than a title to my question.

    Did it in bash for now... my goal was to get around the hopping from one server to another for one little repetitive job.

    You won't have to do that if you take the time to learn PowerShell.

    Learn PowerShell: https://mva.microsoft.com/en-us/training-courses/getting-started-with-microsoft-powershell-8276


    \_(ツ)_/

    Wednesday, October 4, 2017 2:17 PM