Compare multiple CSV files

Ask general questions here.
diogogmaio
Posts: 34
Joined: Thu May 19, 2016 4:52 pm

Compare multiple CSV files

Post by diogogmaio » Thu May 19, 2016 4:54 pm

I need to compare the strucuture of two CSV files.
So the "project" would be...

Donwload first CSV - donwload second CSV and then compare the two outputs.

What is the best approach?

Thanks in advance.

User avatar
RobinHood42
Posts: 324
Joined: Fri Jan 09, 2015 3:24 pm

Re: Compare multiple CSV files

Post by RobinHood42 » Fri May 20, 2016 11:48 am

Hi,

You have to do that in code. The most common way is to compare the files byte by byte.

Here are two examples:
http://www.dotnetperls.com/file-equals
http://stackoverflow.com/questions/7931 ... in-c-sharp

Regards,
Robin

User avatar
odklizec
Ranorex Guru
Ranorex Guru
Posts: 7470
Joined: Mon Aug 13, 2012 9:54 am
Location: Zilina, Slovakia

Re: Compare multiple CSV files

Post by odklizec » Fri May 20, 2016 11:59 am

Hi,

Probably the simplest way to compare two text files is described here:
http://www.ranorex.com/support/user-gui ... html#c7783

However, in case you need more detailed comparison output, you will have to write a bit more code.

I'm personally using Ranorex CSV data connector and some custom code, which basically loads both files into separate CSV data connectors, compare both connectors and returns differences.

Code: Select all

		/// <summary>
		/// method to compare two csv files
		/// </summary>
		/// <param name="refFile"></param>
		/// <param name="cmpFile"></param>
        public static void CompareCSVFiles(string refFile, string cmpFile)
        {        	
        	//validate path to configuration file
			TestFileExists(refFile);
			//create CSV data connector
			string refConnector = "CSVConnector";

			TestFileExists(cmpFile);
			//create CSV data connector
			string cmpConnector = "CSVConnector";

			//get data from ref. CSV
			Ranorex.Core.Data.CsvDataConnector refCSVConnector = new Ranorex.Core.Data.CsvDataConnector(refConnector,@refFile,true);
			refCSVConnector.SeparatorChar = ',';
			Ranorex.Core.Data.ColumnCollection refCSVColumns = new Ranorex.Core.Data.ColumnCollection();
			Ranorex.Core.Data.RowCollection refCSVRows = new Ranorex.Core.Data.RowCollection(refCSVColumns);
			//load CSV connector
			refCSVConnector.LoadData(out refCSVColumns, out refCSVRows);

			//get data from cmp. CSV
			Ranorex.Core.Data.CsvDataConnector cmpCSVConnector = new Ranorex.Core.Data.CsvDataConnector(cmpConnector,@cmpFile,true);
			cmpCSVConnector.SeparatorChar = ',';
			Ranorex.Core.Data.ColumnCollection cmpCSVColumns = new Ranorex.Core.Data.ColumnCollection();
			Ranorex.Core.Data.RowCollection cmpCSVRows = new Ranorex.Core.Data.RowCollection(cmpCSVColumns);
			//load CSV connector
			cmpCSVConnector.LoadData(out cmpCSVColumns, out cmpCSVRows);
			
			Ranorex.Core.Data.Row refRowCSV;
			Ranorex.Core.Data.Row cmpRowCSV;
			if (refCSVRows.Count == cmpCSVRows.Count)
			{
				//go through ref/cmp CSV files and compare individual elements
				string refCSVValue = "";
				string cmpCSVValue = "";
				bool differenceFound = false; 
				for (int i=0; i<=refCSVRows.Count-1; i++) 
				{
					refRowCSV = refCSVRows[i];
					cmpRowCSV = cmpCSVRows[i];
					for (int j=0; j<=refCSVColumns.Count-1; j++)
					{
						refCSVValue = refRowCSV[j].ToString();
						cmpCSVValue = cmpRowCSV[j].ToString();
						if (refCSVValue != cmpCSVValue)
						{
							Report.Log(ReportLevel.Failure, "Comparison value different than reference value...", "Reference value: " + refCSVValue + "\n" + "Comparison value: " + cmpCSVValue);							
							differenceFound = true;
						}								
					}
				}
				if (!differenceFound)
				{
					Report.Log(ReportLevel.Success, "Validation", "Validation OK! Reference and compare CSV files the same!");
				}
			}
			else
			{
				// skip the iteration in case the number of ref and cmp rows differ
				throw new RanorexException("Number of rows in cmp. CSV file is not equal to number of rows in ref. CSV file!"); 
			}
		}	
BTW, as you can see, the detailed comparison is done only in case both files contain the same number of rows. I'm always comparing files, which are supposing to have the same number of rows. So if the number of rows differs, I consider the result as failed and I don't need to compare individual rows.
Pavel Kudrys
Ranorex explorer at Descartes Systems

Please add these details to your questions:
  • Ranorex Snapshot. Learn how to create one >here<
  • Ranorex xPath of problematic element(s)
  • Ranorex version
  • OS version
  • HW configuration

diogogmaio
Posts: 34
Joined: Thu May 19, 2016 4:52 pm

Re: Compare multiple CSV files

Post by diogogmaio » Fri May 20, 2016 3:54 pm

odklizec wrote:Hi,

Probably the simplest way to compare two text files is described here:
http://www.ranorex.com/support/user-gui ... html#c7783

However, in case you need more detailed comparison output, you will have to write a bit more code.

I'm personally using Ranorex CSV data connector and some custom code, which basically loads both files into separate CSV data connectors, compare both connectors and returns differences.

Code: Select all

		/// <summary>
		/// method to compare two csv files
		/// </summary>
		/// <param name="refFile"></param>
		/// <param name="cmpFile"></param>
        public static void CompareCSVFiles(string refFile, string cmpFile)
        {        	
        	//validate path to configuration file
			TestFileExists(refFile);
			//create CSV data connector
			string refConnector = "CSVConnector";

			TestFileExists(cmpFile);
			//create CSV data connector
			string cmpConnector = "CSVConnector";

			//get data from ref. CSV
			Ranorex.Core.Data.CsvDataConnector refCSVConnector = new Ranorex.Core.Data.CsvDataConnector(refConnector,@refFile,true);
			refCSVConnector.SeparatorChar = ',';
			Ranorex.Core.Data.ColumnCollection refCSVColumns = new Ranorex.Core.Data.ColumnCollection();
			Ranorex.Core.Data.RowCollection refCSVRows = new Ranorex.Core.Data.RowCollection(refCSVColumns);
			//load CSV connector
			refCSVConnector.LoadData(out refCSVColumns, out refCSVRows);

			//get data from cmp. CSV
			Ranorex.Core.Data.CsvDataConnector cmpCSVConnector = new Ranorex.Core.Data.CsvDataConnector(cmpConnector,@cmpFile,true);
			cmpCSVConnector.SeparatorChar = ',';
			Ranorex.Core.Data.ColumnCollection cmpCSVColumns = new Ranorex.Core.Data.ColumnCollection();
			Ranorex.Core.Data.RowCollection cmpCSVRows = new Ranorex.Core.Data.RowCollection(cmpCSVColumns);
			//load CSV connector
			cmpCSVConnector.LoadData(out cmpCSVColumns, out cmpCSVRows);
			
			Ranorex.Core.Data.Row refRowCSV;
			Ranorex.Core.Data.Row cmpRowCSV;
			if (refCSVRows.Count == cmpCSVRows.Count)
			{
				//go through ref/cmp CSV files and compare individual elements
				string refCSVValue = "";
				string cmpCSVValue = "";
				bool differenceFound = false; 
				for (int i=0; i<=refCSVRows.Count-1; i++) 
				{
					refRowCSV = refCSVRows[i];
					cmpRowCSV = cmpCSVRows[i];
					for (int j=0; j<=refCSVColumns.Count-1; j++)
					{
						refCSVValue = refRowCSV[j].ToString();
						cmpCSVValue = cmpRowCSV[j].ToString();
						if (refCSVValue != cmpCSVValue)
						{
							Report.Log(ReportLevel.Failure, "Comparison value different than reference value...", "Reference value: " + refCSVValue + "\n" + "Comparison value: " + cmpCSVValue);							
							differenceFound = true;
						}								
					}
				}
				if (!differenceFound)
				{
					Report.Log(ReportLevel.Success, "Validation", "Validation OK! Reference and compare CSV files the same!");
				}
			}
			else
			{
				// skip the iteration in case the number of ref and cmp rows differ
				throw new RanorexException("Number of rows in cmp. CSV file is not equal to number of rows in ref. CSV file!"); 
			}
		}	
BTW, as you can see, the detailed comparison is done only in case both files contain the same number of rows. I'm always comparing files, which are supposing to have the same number of rows. So if the number of rows differs, I consider the result as failed and I don't need to compare individual rows.


How can I use this code in my test? Do I have to create a Code Module?
Thanks in advance

krstcs
Posts: 2683
Joined: Tue Feb 07, 2012 4:14 pm
Location: Austin, Texas, USA

Re: Compare multiple CSV files

Post by krstcs » Fri May 20, 2016 6:47 pm

You can do it in either a Recording Module's usercode, or you can create a new code module, but yes, Pavel's solution requires working in code.
Shortcuts usually aren't...

diogogmaio
Posts: 34
Joined: Thu May 19, 2016 4:52 pm

Re: Compare multiple CSV files

Post by diogogmaio » Mon May 23, 2016 11:57 am

I understand.

But what is supposed to do?
Copy this piece of code into a new user code module for example...
Should I paste it after the code automatically generated by ranorex (when u create a new user code module) or should i delete everything and then paste only the code by Pavel?

Thanks

diogogmaio
Posts: 34
Joined: Thu May 19, 2016 4:52 pm

Re: Compare multiple CSV files

Post by diogogmaio » Mon May 23, 2016 12:06 pm

After pasting the code in a new code module and clicking RUN.

TestFileExists error.
You do not have the required permissions to view the files attached to this post.

User avatar
odklizec
Ranorex Guru
Ranorex Guru
Posts: 7470
Joined: Mon Aug 13, 2012 9:54 am
Location: Zilina, Slovakia

Re: Compare multiple CSV files

Post by odklizec » Mon May 23, 2016 12:13 pm

Hi,

The code I posted is just a method, which you can use anywhere you want. Then you need to fill the refFile and cmpFile with path to files you want to compare.

I would suggest to create a new code module (let's call it common.cs), where you can put this method and eventually similar "common" methods, which you can use across your project. There is a very nice blog article, describing the whole process in detail:
http://www.ranorex.com/blog/custom-smar ... readAction

PS: the TestFileExists is another method I'm using, to test the availability of file, before it's usage. You can either remove this line or add this method, along with the CompareCSVFiles method...

Code: Select all

		/// <summary>
		/// This method tests the file availability 
		/// </summary>
		/// <param name="FilePath">path to file </param>
		public static void TestFileExists(string FilePath)
		{
			if (! System.IO.File.Exists(@FilePath))
			{
				
				// skip the iteration in case of missing file
				throw new RanorexException("File Does not exist! File: " + @FilePath); 
				
			}
			else
				Report.Log(ReportLevel.Success, "Validation", "File " + FilePath + " exists.");
		}
Pavel Kudrys
Ranorex explorer at Descartes Systems

Please add these details to your questions:
  • Ranorex Snapshot. Learn how to create one >here<
  • Ranorex xPath of problematic element(s)
  • Ranorex version
  • OS version
  • HW configuration

diogogmaio
Posts: 34
Joined: Thu May 19, 2016 4:52 pm

Re: Compare multiple CSV files

Post by diogogmaio » Tue Jun 14, 2016 4:59 pm

Only one more doubt...

Imagine that instead of two files I want to compare 15 files. So a comparison 15 by 15...one pair comparison but 15 times...

How can I redo your example to check all the files i want and not only two?
I know that basicallly i could rename the module 15 times and run it but i am looking for a better/efficient way to do it.
Is it possible?

User avatar
odklizec
Ranorex Guru
Ranorex Guru
Posts: 7470
Joined: Mon Aug 13, 2012 9:54 am
Location: Zilina, Slovakia

Re: Compare multiple CSV files

Post by odklizec » Wed Jun 15, 2016 7:30 am

Hi, I think the best way to do what you want is to create a new test case with data connector, with list of ref/cmp files. The data connector basically works as a loop, where each line of data connecotr is one iteration. So all you have to do is to add the code module with file comparison method into this test case and connect the ref/cmp data connectors to refFile/cmpFile variables. This should solve your problem?
Pavel Kudrys
Ranorex explorer at Descartes Systems

Please add these details to your questions:
  • Ranorex Snapshot. Learn how to create one >here<
  • Ranorex xPath of problematic element(s)
  • Ranorex version
  • OS version
  • HW configuration