Page 1 of 1

Compare multiple CSV files

Posted: Thu May 19, 2016 4:54 pm
by diogogmaio
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.

Re: Compare multiple CSV files

Posted: Fri May 20, 2016 11:48 am
by RobinHood42
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

Re: Compare multiple CSV files

Posted: Fri May 20, 2016 11:59 am
by odklizec
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.

Re: Compare multiple CSV files

Posted: Fri May 20, 2016 3:54 pm
by diogogmaio
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

Re: Compare multiple CSV files

Posted: Fri May 20, 2016 6:47 pm
by krstcs
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.

Re: Compare multiple CSV files

Posted: Mon May 23, 2016 11:57 am
by diogogmaio
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

Re: Compare multiple CSV files

Posted: Mon May 23, 2016 12:06 pm
by diogogmaio
After pasting the code in a new code module and clicking RUN.

TestFileExists error.

Re: Compare multiple CSV files

Posted: Mon May 23, 2016 12:13 pm
by odklizec
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.");
		}

Re: Compare multiple CSV files

Posted: Tue Jun 14, 2016 4:59 pm
by diogogmaio
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?

Re: Compare multiple CSV files

Posted: Wed Jun 15, 2016 7:30 am
by odklizec
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?