Compare two different csv files using PowerShell

Compare two different csv files using PowerShell

  • Comments 5
  • Likes

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Unless im doing something wrong, this doesnt work correctly with more than two columns, also there is no way to extricate teh data..

  • Hmm a bit silly as in excel you can scroll to the end and see the amount of lines ... it would be handy if the script outputted the differences.

  • Hi Halo,

    If you are only interested in the differences check the options for the Compare-Object Cmdlet. Run Get-Help Compare-Object -Full

    /Stefan

  • I get a feed from my supplier of their product on a daily basis. I need to compare it to the CSV file from my website and change the quanities and upload the CSV to my website to keep quanities accurate. Will this program do that?

  • www.csvcompare.com