none
Marge 2 CSV files with common user ID RRS feed

  • Question

  • Hello Scripting Guys

    I am trying to merge two CSV files with data pulled from active directory for accounts that need to be disabled and why. CSV file 1 list uses that have expired accounts, the headers are UserID (SAMAccountName), Reason, and Account expiration date.  CSV file 2 is a list of users that have not logged on in 90 days, the headers are UserID (SAMAccountName, reason and last log on.

    One of the issues I am running into are that some of the User ID's show up in both CSV files. When I use the sort cmdlet and the property -unique the still list the User ID twice.

    What I need to do it combine both files and if the name shows up in both files add the header and notes for that UserID under one entry.

    I have tried it with text files and had not luck now I am trying it with CSV files. my last attempt used the following.

    $CSV1= import-CSV "path"

    $CSV2= Import-CSV "path"

    $CSV0 = $CSV1 + $CSV2

    $CSV0

    I also found online that I need to use the "Merge-CSV" cmdlet but I am getting and error saying that it in not in fact a cmdlet.

    My last attempt will combine both files, however there are still duplicated UserID's and it will annotate the reason but will leave off the last logon date for the second CSV file. Is there and easier way to combine both files, including adding all columns from each file and if a User ID is present in both files, it will append the data from both files to the one UserID?

    Thank

    Wednesday, June 10, 2015 4:39 PM

Answers

  • First of all, thank you for introducing me to Merge-Csv (http://www.powershelladmin.com/wiki/An_Advanced_Cmdlet_to_Merge_Csv_Files_in_PowerShell). Let me explain what I did, and perhaps that will help you solve your own problem.

    I started by creating two Csv files, 1.csv and 2.csv. I've included their information below. Notice that the only share header is UserID. Using the header Reason in both didn't work and so I renamed Reason to Resaon2 in 2.csv. Watch for that.

    1.csv
    UserID,Reason,AccountExpDate
    123,Terminated,12/12/2015
    223,Resigned,12/13/2015
    333,Deceased,12/14/2015

    2.csv
    UserID,Reason2,LastLogOn
    222,Reason1,11/14/2014
    223,Reason2,03/12/2015
    224,Reason3,06/01/2015

    I saved Merge-Csv.ps1 to my desktop and then in my PowerShell console, navigated to the new .ps1 file: cd C:\users\tommymaynard\desktop. I then dot sourced the .ps1 file: . .\Merge-Csv.ps1. Please enter that just like you see it (there's two dots and a space in between). I then ran the function and supplied the necessary information: Merge-Csv -Path .\1.csv,.\2.csv -Id UserID

    Here's the results:

    PS C:\Users\tommymaynard\Desktop> Merge-Csv -Path .\1.csv,.\2.csv -id userid | ft
    WARNING: Identifying column entry '224' was not found in all CSV data objects/files. Found in object/file no.: 2
    WARNING: Identifying column entry '222' was not found in all CSV data objects/files. Found in object/file no.: 2
    WARNING: Identifying column entry '333' was not found in all CSV data objects/files. Found in object/file no.: 1
    WARNING: Identifying column entry '123' was not found in all CSV data objects/files. Found in object/file no.: 1
    
    userid                  AccountExpDate          Reason                  LastLogOn               Reason2
    ------                  --------------          ------                  ---------               -------
    223                     12/13/2015              Resigned                03/12/2015              Reason2
    224                                                                     06/01/2015              Reason3
    222                                                                     11/14/2014              Reason1
    333                     12/14/2015              Deceased
    123                     12/12/2015              Terminated
    Notice, that I used ft (Format-Table) to better display my results. You could run without that and the final pipe character, or just pipe it to Export-Csv .\3.csv -NoTypeInformation and create a new file based on the two. Good luck. Hopefully this will help, but it may not depending on your Csv files; I had to do some guessing...

    Edit: Sorry about results looking wonky. It'll look fine in your console/Csv.


    • Edited by tommymaynard Wednesday, June 10, 2015 6:06 PM
    • Marked as answer by Richardlaw Wednesday, June 17, 2015 4:32 PM
    Wednesday, June 10, 2015 6:04 PM

All replies

  • Yes Merge-Csv is a CmdLet that you have to supply.

    You cannot add the CSVs as it will create all duplicates.  You have to write a script that actually joins them field by field.

    You also need to post the exact headers and not a gues as to what they are.  If what you posted is an actual header then you likely do nothave a CSV file.


    \_(ツ)_/

    Wednesday, June 10, 2015 5:46 PM
  • First of all, thank you for introducing me to Merge-Csv (http://www.powershelladmin.com/wiki/An_Advanced_Cmdlet_to_Merge_Csv_Files_in_PowerShell). Let me explain what I did, and perhaps that will help you solve your own problem.

    I started by creating two Csv files, 1.csv and 2.csv. I've included their information below. Notice that the only share header is UserID. Using the header Reason in both didn't work and so I renamed Reason to Resaon2 in 2.csv. Watch for that.

    1.csv
    UserID,Reason,AccountExpDate
    123,Terminated,12/12/2015
    223,Resigned,12/13/2015
    333,Deceased,12/14/2015

    2.csv
    UserID,Reason2,LastLogOn
    222,Reason1,11/14/2014
    223,Reason2,03/12/2015
    224,Reason3,06/01/2015

    I saved Merge-Csv.ps1 to my desktop and then in my PowerShell console, navigated to the new .ps1 file: cd C:\users\tommymaynard\desktop. I then dot sourced the .ps1 file: . .\Merge-Csv.ps1. Please enter that just like you see it (there's two dots and a space in between). I then ran the function and supplied the necessary information: Merge-Csv -Path .\1.csv,.\2.csv -Id UserID

    Here's the results:

    PS C:\Users\tommymaynard\Desktop> Merge-Csv -Path .\1.csv,.\2.csv -id userid | ft
    WARNING: Identifying column entry '224' was not found in all CSV data objects/files. Found in object/file no.: 2
    WARNING: Identifying column entry '222' was not found in all CSV data objects/files. Found in object/file no.: 2
    WARNING: Identifying column entry '333' was not found in all CSV data objects/files. Found in object/file no.: 1
    WARNING: Identifying column entry '123' was not found in all CSV data objects/files. Found in object/file no.: 1
    
    userid                  AccountExpDate          Reason                  LastLogOn               Reason2
    ------                  --------------          ------                  ---------               -------
    223                     12/13/2015              Resigned                03/12/2015              Reason2
    224                                                                     06/01/2015              Reason3
    222                                                                     11/14/2014              Reason1
    333                     12/14/2015              Deceased
    123                     12/12/2015              Terminated
    Notice, that I used ft (Format-Table) to better display my results. You could run without that and the final pipe character, or just pipe it to Export-Csv .\3.csv -NoTypeInformation and create a new file based on the two. Good luck. Hopefully this will help, but it may not depending on your Csv files; I had to do some guessing...

    Edit: Sorry about results looking wonky. It'll look fine in your console/Csv.


    • Edited by tommymaynard Wednesday, June 10, 2015 6:06 PM
    • Marked as answer by Richardlaw Wednesday, June 17, 2015 4:32 PM
    Wednesday, June 10, 2015 6:04 PM
  • Thank you all for your responses, however when I type merge-csv in PowerShell ISE V.3 it is still showing as not being a valid cmdlet. What versions are you using to utilize Merge-csv?
    Wednesday, June 10, 2015 6:26 PM
  • Yu have to install it on your system.

    http://www.powershelladmin.com/w/images/e/e5/Merge-Csv.ps1.txt


    \_(ツ)_/

    Wednesday, June 10, 2015 6:28 PM
  • Thank you all for your responses, however when I type merge-csv in PowerShell ISE V.3 it is still showing as not being a valid cmdlet. What versions are you using to utilize Merge-csv?

    I wrote you complete instructions on how to make use of Merge-Csv, to include how to get it working inside your PowerShell console. Merge-Csv is something someone else wrote; it's not there by default. If it's any easier, then use jrv's link. Select the entire text (Ctrl+A), right-click and choose Copy, and then go to your PowerShell session and right-click once. After it pastes it in, press Enter a couple times and then you'll have function available in that session.

    Edit: Added additional info.

    Wednesday, June 10, 2015 6:44 PM
  • I have taken all of your advice and tried it all but it is still not working properly,

    Depending on how I run it or which way I type the string I either get a file that contains only (I>>?) with the question mark upside down, a blank file or an error in the last line off code saying there is a null value for |select property ($sharedHeaders + $HeaderProperties)

    Any thought?  Thanks again for all of your help.

    

    Thursday, June 11, 2015 2:45 PM
  • Your issues seem to be in translation.  You are not copying or typing the code and not showing us accurate CSV contents.  The code supplied is a template that you need to adapt to your files and needs.

    It also appears that the headers of your CSV files are not good or you do no thave real CSV files.  Post a few lines of each to be sure.


    \_(ツ)_/

    Thursday, June 11, 2015 2:53 PM
  • Here is exactly what is in my CSV file

    CSV File 1

    UserID     Reason1              Expirationdate

    THjones     Expired Account   10/10/2014

    CSV File 2

    UserID     Reason2                  LastLogOn

    THjones     Account Inactive     1/25/2015

    I have copied the function to the letter an pasted it in PowerShell ISE. It is saved on my desktop as Merge.csv.

    I open ISE and run the function, then I run my other script to try and combine the files.

    I have tried the following

    

    merge-csv -path @(ipcsv C:\Users\law\Desktop\Lastlogon.csv),@(ipcsv C:\Users\law\Desktop\password.csv) -Id UserID | Export-Csv -notype -enc UTF8 merged.csv

    

    The result

    
    Merge-Csv : A positional parameter cannot be found that accepts argument 'System.Object[]'.
    At line:2 char:10
    + merge-csv <<<<   @(ipcsv C:\Users\lawhornr\Desktop\Lastlogon.csv),@(ipcsv C:\Users\lawhornr\Desktop\password.csv) -Id UserID | Export-Csv -notype -enc UTF8 merged.csv
        + CategoryInfo          : InvalidArgument: (:) [Merge-Csv], ParameterBindingException
        + FullyQualifiedErrorId : PositionalParameterNotFound,Merge-Csv
     


    Then I tried

    merge-csv -path  C:\Users\law\Desktop\Lastlogon.csv,C:\Users\law\Desktop\password.csv -Id UserID | Export-Csv -notype -enc UTF8 merged.csv

    Result

    Merge-Csv : Some headers are shared. Are you just looking for '@(ipcsv csv1) + @(ipcsv.csv2) | Export-Csv ...'?
    To remove duplicate (between the files to merge) headers from a CSV file, Import-Csv it, pass it to Select-Object, and omit the duplicate header(s)/column(s).
    Exiting.
    At line:2 char:10
    + merge-csv <<<<  -path  C:\Users\lawhornr\Desktop\Lastlogon.csv,C:\Users\lawhornr\Desktop\password.csv -Id UserID | Export-Csv -notype -enc UTF8 merged.csv
        + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
        + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Merge-Csv
     

    
    I hope this helps in determining what i am doing wrong. Thanks again for all of your assistance.

    

     

    Thursday, June 11, 2015 5:19 PM
  • As I suspected.  This si not a CSV file.

    Here is what a CSV file looks like:

    UserID,Reason1,Expirationdate

    THjones,Expired Account,10/10/2014

    It may also have quotes around some or all fields.

    Please go back and carefully read the instuctions for the function.  You are not calling it correctly.

    merge-csv -path C:\Users\law\Desktop\Lastlogon.csv,C:\Users\law\Desktop\password.csv -Id UserID

    As shown below.


    \_(ツ)_/

    Thursday, June 11, 2015 5:37 PM
  • Ok so I have made some progress, I can now get the files to merge but with one small issue. The usernames merge fine but it is not pulling down the headers. My account is not verified yet or I would put an image in but I am using 6 test accounts, Test 1, Test 2, Test 3, Test 4 and Test 5 in the first CSV file and Test 2 through Test 6 in the second. When the merge is completed I have all 6 test accounts in the same file, the issue is that it does not pull any of the other header information, It shows the labels such as reason 1 and reason 2 but does not pull any data for them. any thoughts?

    Thanks again for all of your help.

    Tuesday, June 16, 2015 7:13 PM
  • So I am finally able to attached an image to the forum. Here is my  attempt.

    I am unable to show you exactly what I am doing be cause I am having to hand type the Merge-Csv Script form one system to another. but in this example that I am doing just like the others, this is what I get

    On the other system, I can get the User ID to merge but the Reson1,Reson2,ExperationDate and Lastlogin headers display but there is not data in the fields.

    Wednesday, June 17, 2015 2:44 PM
  • You do not have real CSV files.  They have issues that cause this to fail.

    They may load as CSV files but youwil need to figure out why this is happening.

    Post your issues about the code in the Q&A for the author of the code and let them see if they can add some messages int he script to teszt the files.

    You could also just write your own merge routine.

    In the end, without your failing files, it is unliky that anyone can reproduce your issues.


    \_(ツ)_/


    • Edited by jrv Wednesday, June 17, 2015 2:53 PM
    Wednesday, June 17, 2015 2:52 PM
  • Thank you. I got it figured out, because I was having to type it from one system to another I missed part of the variable that cut out when printing. The function works. Thank you all for your help.
    Wednesday, June 17, 2015 4:31 PM