Compare two different csv files using PowerShell

Today I needed to compare two csv files with results from SQL queries from the OperationsManager database and the OperationsManagerDW database. I run the SQL queries on both OperationsManager databases and saved the result to a csv file. Now I needed to quickly compare the results from both csv files, and what better way than to use PowerShell.

Here is an example how you can use PowerShell to quickly compare two csv files:

First some example CSV files:

Example CSVFile1.csv

ManagementPackId,MPFriendlyName,MPName,mp.MPVersionDependentId,MPLastModified,MPKeyToken,ContentReadable 3A7609F3-A5AB-F205-5001-010EE387DD28,Customer - Exchange 2007 MP overrides,Customer.Exchange.MP.overrides,5D49AADA-DFEE-40DC-9A32-2758FC71B426,2011-01-06 16:39:57.517,NULL,1 49C911AC-337C-CD94-DD13-021E2CFDDAB0,Customer - SCOM MS,Customer.SCOM.MS,9FA54A57-B123-44AE-A9AB-ED1A1C4CDB35,2011-01-27 09:16:28.100,NULL,1 C0A4183F-4318-CB0C-EF5A-054B32AE33B4,Windows Server 2000 Operating System Overrides,Windows.Server.2000.Operating.System.Overrides,6293AED6-DE8F-462E-AD54-1F83F2E33F82,2011-02-04 14:38:19.330,NULL,1 3DFAC27F-8551-B71B-7DD2-30156A31CD92,Customer - Citrix Beheer - WIS Servers Events,Customer.Citrix.Beheer.WIS.Servers.Events,4496E575-C63C-4B45-BEF7-6216668264F7,2010-12-06 01:54:15.963,NULL,1 3AFE86A8-827C-CDB6-9A84-32C67483C3D1,Windows cluster management monitoring - overrides,Windows.cluster.management.monitoring.overrides,243D4B09-9455-4737-9880-399CF2700CF4,2011-02-04 16:07:11.377,NULL,1 6569E210-F188-EEF7-05CA-331859F4D8C6,Customer - Citrix Beheer,Customer.Citrix.Beheer,8109BB67-C923-4814-A1DB-F2A386B2389B,2011-01-17 13:58:08.690,NULL,1 8BDC857A-7F32-40A7-E5C2-4583E263B290,Customer - ISA server 2006,Customer.ISA.server,68F24CBA-B6C7-474B-A09B-03B0D730609B,2011-01-07 10:56:51.290,NULL,1 C39E8EF5-5E04-9CAE-F467-61D87ACD5E9E,Customer - Windows Server - SCCM,Customer.Windows.Server.SCCM,7BB1925D-007A-4621-BAD3-56F87B49C4C8,2011-01-17 13:02:28.880,NULL,1 A2B5CA86-96F7-F4CA-2152-63274CEF3336,Customer - Sharepoint Server,Customer.Sharepoint.Server,26E07753-0740-450F-972C-6A16C2099C12,2011-01-06 07:26:36.533,NULL,1 5BC992B2-96F6-E192-6E92-65FB5EC0CE5E,Customer - SQL Server (Monitoring),Customer.SQL.Server.Monitoring,2618C5C0-D89F-4E86-8259-FD7809D75E7C,2011-02-07 14:12:14.613,NULL,1 E4056BA9-CFC7-2D04-FF9B-67084CC15E83,Customer - Print Servers,Customer.Print.Servers,C336C3FD-CF15-4319-A33E-00F4BF2A390D,2010-11-24 10:14:22.660,NULL,1 3EECB872-CC4A-62BC-EA39-855EF594852D,Customer - Print Beheer Menu MP,Customer.Print.Beheer.Menu.MP,6F12D09D-0A55-40BA-A320-B571E9554ADE,2010-12-21 12:49:53.603,NULL,1 A2D688EC-8AE1-C4AF-A485-8564C214D292,Customer - Arcserve MP,Customer.Arcserve.MP,7AECFE66-51F6-4174-B84C-E918140F37F2,2010-12-03 11:51:39.080,NULL,1 A3542697-E0E7-E382-D40B-86ADEC512D79,Windows Server 2008 Operating System (Monitoring) Overrides,Windows.Server.2008.Operating.System.Monitoring.Overrides,20BCC6DC-F928-4108-B70F-873639A0448A,2010-12-16 12:16:40.800,NULL,1 070A0205-56D6-FF84-688A-8A216A47A949,Customer - Fileservers Dienst,Customer.Fileservers.Dienst,7DDF2381-3B13-4971-9E91-6656075C2E60,2010-12-22 09:26:50.683,NULL,1 98701ABB-AF93-7E5A-D121-9A18E0037CE8,Customer - DSA & PKI Gateways,Customer.DSA.PKI.Gateways,95B49F67-9514-4E80-991D-B3CF4EA2632F,2010-12-20 11:38:44.697,NULL,1 FA4FDEAF-E107-8AA3-BD2C-9E29C58C0C53,Customer - PKI Management,Customer.PKI.Management,6B677888-07F1-4725-BA3F-E742B20FA9C4,2011-01-27 08:45:23.153,NULL,1 51DA566A-1AC1-5DC9-3F66-A11FC439357C,nworks VMware Virtual Enterprise Monitoring MP,nworks.VMware.VEM,599A587E-3357-A127-50EF-198F04365ADA,2010-11-23 11:10:29.243,65c40f14a98ce59b,1 512DB9E6-D21A-2E4F-E987-A28BDE8F940B,Customer - Exchange availability,Customer.Exchange.availability,1D0DDA6B-9ED2-48C0-BFEE-1538DA9AE16F,2010-12-27 10:10:30.707,NULL,1 DD81FA7E-73BF-332B-C4B6-A767BB514BE8,Customer - Connect Direct,Customer.Connect.Direct,9C6BB3FD-6582-4F18-B18C-7BBE4DB9FCA6,2011-01-31 13:47:15.613,NULL,1 155A8BBB-C6D2-C706-DD1C-B7EFCCCC0E81,Customer - AD Beheer,Customer.AD.Beheer,CF036BAA-47C0-4D09-A2F4-25B3DC1F78B9,2011-01-05 13:17:15.520,NULL,1 E2296ED5-DDBC-EBC7-3881-C5288ADAA7CF,Microsoft Exchange Server 2010 Management Pack,Microsoft.Exchange.2010,AB06EB14-EAF1-0F0B-04B8-F1CDD33F4ACC,2011-02-04 09:02:29.607,31bf3856ad364e35,1 9E138797-AE1C-AF6E-E61B-D110253B417C,Engyro Connector TEC,Engyro.Connector.TEC.MP,76306617-5F13-42EB-9F3B-B948F18DC4D8,2010-11-23 11:29:55.980,NULL,1 A3C3E706-795F-BF80-DFC2-DBB4BDDFA919,Customer - ACS Management Pack,Customer.ACS.Management.Pack,D7C4F7A2-B18D-454F-8615-22CEC4EFDFCA,2011-01-04 12:30:01.593,NULL,1 7C2A6181-B7B9-89C2-6FC3-EDB760FCEFC1,Microsoft Exchange Server 2007 CAS Monitoring - Override,Microsoft.Exchange.Server.CAS.Monitoring.Override,BA79A086-E992-4AB7-B4B6-933FB8AD1211,2011-01-07 12:39:36.870,NULL,1

 

Example CSVFile2.csv

ManagementPackId,MPFriendlyName,MPName,mp.MPVersionDependentId,MPLastModified,MPKeyToken,ContentReadable 3A7609F3-A5AB-F205-5001-010EE387DD28,Customer - Exchange 2007 MP overrides,Customer.Exchange.MP.overrides,5D49AADA-DFEE-40DC-9A32-2758FC71B426,2011-01-06 16:39:57.517,NULL,1 49C911AC-337C-CD94-DD13-021E2CFDDAB0,Customer - SCOM ,Customer.SCOM,9FA54A57-B123-44AE-A9AB-ED1A1C4CDB35,2011-01-27 09:16:28.100,NULL,1 C0A4183F-4318-CB0C-EF5A-054B32AE33B4,Windows Server 2000 Operating System Overrides,Windows.Server.2000.Operating.System.Overrides,6293AED6-DE8F-462E-AD54-1F83F2E33F82,2011-02-04 14:38:19.330,NULL,1 F37D0C95-F313-4EC9-5385-0F41BFCFE55D,Customer Custom Monitoring,Customer.Custom.Monitoring,44597F97-6FD4-4D2E-B01E-5CDB9B218E50,2011-01-27 08:52:20.377,NULL,1 75E3375E-B1CD-4641-E86B-2664A7E7E7C2,Customer - Microsoft Exchange 2010 Override MP,Customer.Microsoft.Exchange.Override.MP,6DC9439A-BD4C-46C0-8D2F-080029042A6D,2011-02-04 11:12:40.857,NULL,1 3DFAC27F-8551-B71B-7DD2-30156A31CD92,Customer - Citrix Beheer - WIS Servers Events,Customer.Citrix.Beheer.WIS.Servers.Events,4496E575-C63C-4B45-BEF7-6216668264F7,2010-12-06 01:54:15.963,NULL,1 3AFE86A8-827C-CDB6-9A84-32C67483C3D1,Windows cluster management monitoring - overrides,Windows.cluster.management.monitoring.overrides,243D4B09-9455-4737-9880-399CF2700CF4,2011-02-04 16:07:11.377,NULL,1 6569E210-F188-EEF7-05CA-331859F4D8C6,Customer - Citrix Beheer,Customer.Citrix.Beheer,8109BB67-C923-4814-A1DB-F2A386B2389B,2011-01-17 13:58:08.690,NULL,1 B5FE879F-CD35-DD9A-7464-36401ABB9205,Microsoft Exchange 2010 Report Library,Microsoft.Exchange.2010.Reports,112EA102-A45A-9AA9-C84D-1ED9A56E8C61,2011-02-04 10:31:14.137,31bf3856ad364e35,1 DA187E72-B9D7-9E16-D098-3B0A624DC38C,My Default Management Pack,Microsoft.SystemCenter.OperationsManager.DefaultUser,497CE20F-02AF-417E-97B1-363F311CF739,2011-02-03 16:37:16.093,NULL,1 8BDC857A-7F32-40A7-E5C2-4583E263B290,Customer - ISA server 2006,Customer.ISA.server,68F24CBA-B6C7-474B-A09B-03B0D730609B,2011-01-07 10:56:51.290,NULL,1 C39E8EF5-5E04-9CAE-F467-61D87ACD5E9E,Customer - Windows Server - SCCM,Customer.Windows.Server.SCCM,7BB1925D-007A-4621-BAD3-56F87B49C4C8,2011-01-17 13:02:28.880,NULL,1 A2B5CA86-96F7-F4CA-2152-63274CEF3336,Customer - Sharepoint Server,Customer.Sharepoint.Server,26E07753-0740-450F-972C-6A16C2099C12,2011-01-06 07:26:36.533,NULL,1 5BC992B2-96F6-E192-6E92-65FB5EC0CE5E,Customer - SQL Server (Monitoring),Customer.SQL.Server.Monitoring,2618C5C0-D89F-4E86-8259-FD7809D75E7C,2011-02-07 14:12:14.613,NULL,1 E4056BA9-CFC7-2D04-FF9B-67084CC15E83,Customer - Print Servers,Customer.Print.Servers,C336C3FD-CF15-4319-A33E-00F4BF2A390D,2010-11-24 10:14:22.660,NULL,1 336987CD-60EF-5014-F384-7D37DE784858,Nworks Overrides,Nworks.Overrides,5EF45858-0DC1-4E67-9AFD-F70A2446CCA9,2011-01-25 10:20:27.143,NULL,1 3EECB872-CC4A-62BC-EA39-855EF594852D,Customer - Print Beheer Menu MP,Customer.Print.Beheer.Menu.MP,6F12D09D-0A55-40BA-A320-B571E9554ADE,2010-12-21 12:49:53.603,NULL,1 A2D688EC-8AE1-C4AF-A485-8564C214D292,Customer - Arcserve MP,Customer.Arcserve.MP,7AECFE66-51F6-4174-B84C-E918140F37F2,2010-12-03 11:51:39.080,NULL,1 A3542697-E0E7-E382-D40B-86ADEC512D79,Windows Server 2008 Operating System (Monitoring) Overrides,Windows.Server.2008.Operating.System.Monitoring.Overrides,20BCC6DC-F928-4108-B70F-873639A0448A,2010-12-16 12:16:40.800,NULL,1 070A0205-56D6-FF84-688A-8A216A47A949,Customer - Fileservers Dienst,Customer.Fileservers.Dienst,7DDF2381-3B13-4971-9E91-6656075C2E60,2010-12-22 09:26:50.683,NULL,1 98701ABB-AF93-7E5A-D121-9A18E0037CE8,Customer - DSA & PKI Gateways,Customer.DSA.PKI.Gateways,95B49F67-9514-4E80-991D-B3CF4EA2632F,2010-12-20 11:38:44.697,NULL,1 FA4FDEAF-E107-8AA3-BD2C-9E29C58C0C53,Customer - PKI Management,Customer.PKI.Management,6B677888-07F1-4725-BA3F-E742B20FA9C4,2011-01-27 08:45:23.153,NULL,1 51DA566A-1AC1-5DC9-3F66-A11FC439357C,nworks VMware Virtual Enterprise Monitoring MP,nworks.VMware.VEM,599A587E-3357-A127-50EF-198F04365ADA,2010-11-23 11:10:29.243,65c40f14a98ce59b,1 512DB9E6-D21A-2E4F-E987-A28BDE8F940B,Customer - Exchange availability,Customer.Exchange.availability,1D0DDA6B-9ED2-48C0-BFEE-1538DA9AE16F,2010-12-27 10:10:30.707,NULL,1 DD81FA7E-73BF-332B-C4B6-A767BB514BE8,Customer - Connect Direct,Customer.Connect.Direct,9C6BB3FD-6582-4F18-B18C-7BBE4DB9FCA6,2011-01-31 13:47:15.613,NULL,1 155A8BBB-C6D2-C706-DD1C-B7EFCCCC0E81,Customer - AD Beheer,Customer.AD.Beheer,CF036BAA-47C0-4D09-A2F4-25B3DC1F78B9,2011-01-05 13:17:15.520,NULL,1 E2296ED5-DDBC-EBC7-3881-C5288ADAA7CF,Microsoft Exchange Server 2010 Management Pack,Microsoft.Exchange.2010,AB06EB14-EAF1-0F0B-04B8-F1CDD33F4ACC,2011-02-04 09:02:29.607,31bf3856ad364e35,1 9E138797-AE1C-AF6E-E61B-D110253B417C,Engyro Connector TEC,Engyro.Connector.TEC.MP,76306617-5F13-42EB-9F3B-B948F18DC4D8,2010-11-23 11:29:55.980,NULL,1 A3C3E706-795F-BF80-DFC2-DBB4BDDFA919,Customer - ACS Management Pack,Customer.ACS.Management.Pack,D7C4F7A2-B18D-454F-8615-22CEC4EFDFCA,2011-01-04 12:30:01.593,NULL,1 7C2A6181-B7B9-89C2-6FC3-EDB760FCEFC1,Microsoft Exchange Server 2007 CAS Monitoring - Override,Microsoft.Exchange.Server.CAS.Monitoring.Override,BA79A086-E992-4AB7-B4B6-933FB8AD1211,2011-01-07 12:39:36.870,NULL,1 94DDD8EA-2A12-9E54-9545-F4A926DBACF0,System Center Core Monitoring Overrides,System.Center.Core.Monitoring.Overrides,BA527C6E-0E2C-440C-9358-6D073BBD4722,2010-12-07 07:25:25.517,NULL,1

 

Now we can compare both files with the use of the compare-object cmdlet in PowerShell.

$file1 = import-csv -Path "C:\temp\Test1.csv" $file2 = import-csv -Path "C:\temp\Test2.csv" Compare-Object $file1 $file2 -property MPFriendlyName -IncludeEqual

 

image

Have fun with PowerShell and the compare-object cmdlet.

 

(stefan.stranger).gettype()

IsPublic IsSerial Name BaseType

-------- -------- ---- --------

True False Object[] Crappy.Planner

Tweet