CSV Parser compared to Text File

Question

Hi all, I have been working at this for a while and believe I am close to completing this project.  I am trying to load a csv file and compare it to a text file. What I am doing is looking at each line of the text file compared to a row from the csv file.  I am looking to see if any line in the text file has any of the cells in that row in the csv.  So far I am able to get a row at will from the csv file but when comparing to each line in the text file it prints to file duplicates of the same lines.  Is there something I am missing.  Sample code below:

		    foreach (string line in file1)
                    {
                        if (line.Contains(serverName))
                        {
                            for (int lineNo = 0; lineNo < file2.Length; lineNo++)
                            {
                                serverInfo = file1[lineNo];
                                char[] splitString = { ',' };
                                cells = serverInfo.Split(splitString);

                                foreach (string cell in cells)
                                {
                                    if (file2[lineNo].Contains(cell))
                                    {
                                        string files = "Entries Found on Column # " + lineNo + "   " + file2[lineNo];
                                        serverLines.Add(files);
                                    }
                                }
                            }
                        }

Please help! I am at a deadline.

Answer 1

I'd make sure just to break out as soon as you find the answer.

function foo(...)
{
 foreach (string line in file1)
                    {
                        if (line.Contains(serverName))
                        {
                            for (int lineNo = 0; lineNo < file2.Length; lineNo++)
                            {
                                serverInfo = file1[lineNo];
                                char[] splitString = { ',' };
                                cells = serverInfo.Split(splitString);

                                foreach (string cell in cells)
                                {
                                    if (file2[lineNo].Contains(cell))
                                    {
                                        string files = "Entries Found on Column # " + lineNo + "   " + file2[lineNo];
                                        serverLines.Add(files);
                                         return;
                                    }
                                }
                            }
                        }
}

Answer 2

Unfortunately, that return statement will exit my program before any files are saved to disk.

Answer 3

Then you can do breaks with flags, or take the function out of the main call.

Answer 4

I do not want to break the program. I want to check each line of the file to make sure that each line does or does not contain certain text.

Answer 5

I do not want to break the program. I want to check each line of the file to make sure that each line does or does not contain certain text.

Then you should follow the instructions that you've been given which tell you how to do exactly that.

Answer 6

Hi CSharp0101,

Welcome to the MSDN forum!

How is it going with the problem currently?

If you need any help, please feel free to let us know.

Meanwhile, you may try to use Range.Find method to looking for specified text in Worksheet:

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.find(v=office.11).aspx 

Please refer to the following code:

       // using Excel = Microsoft.Office.Interop.Excel;
       // using System.IO;  

        static object missing = Type.Missing;
        static Excel._Application app;
        static Excel.Workbook workBook;
       

        static void Main(string[] args)
        {
            app = new Excel.Application();
            workBook = app.Workbooks.Open(@"E:\TestFindRange.xlsx", ReadOnly: true);
            app.Visible = false;
            string line;

            StreamReader file = new StreamReader("E:\\test.txt");
            while ((line = file.ReadLine()) != null)
            {
                string[] list = line.Trim().Split(' ');

                foreach (string s in list)
                {                    
                    FindText(s);                   
                }
            }
           
            app.Quit();
            workBook = null;
            app = null;
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            
           
        }

        static private void FindText(string text)
        {
            Excel.Range currentFind = null;
            Excel.Range firstFind = null;

            Excel.Range testRange = app.get_Range("A1", "G4");

            currentFind = testRange.Find(text, missing,
                Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
                Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
                missing, missing);
            while (currentFind != null)
            {
                if (firstFind == null)
                {
                    firstFind = currentFind;
                }

              // If you didn't move to a new range, you are done.
                else if (currentFind.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing)
                      == firstFind.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing))
                {
                    break;
                }

               Console.WriteLine("We find " +text+ " at "+ 
                   currentFind.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing));
            }        
        }

Hope it helps.

Have a nice day!

yoyo

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us

Answer 7

Thank you for your reply.  I am still working on the issue.  My goal is to grab a row from a .csv file and see if any of the fields are in a text file then print the file to a log.  So far I am able to get the row from the csv file but the code I posted above prints the whole text file out.  That is not the desired result. What I want is to print out the entire text line that the words of each field was found on.  I know my array contains the desired index of each word from a cell because I am able to print to screen all or a specific index.  I created the code above to compare each line in the text with the rows of the cell/array to see if it contans that word and if so add to my list Array.  I will not be comparing my csv to another csv or excel spreadsheet.  

I also want to thank everyone for your time!

Answer 8

Hi CSharp,

Thanks for your response.

I'd like to use a flag variable to show whether the line is added into the log file already.

Please see:

  bool flag = false;
  foreach (string cell in cells)
  {    
    if (file2[lineNo].Contains(cell))
    {
       string files = "Entries Found on Column # " + lineNo + "   " + file2[lineNo];
    
       if(flag !=true)
       {
        serverLines.Add(files);
        flag = true;
       }
    }
  }

Note: Since I didn't test the code on my side, you may need to modify the code according to the specify situation.

Hope it helps.

yoyo

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us

Answer 9

Hi Yoyo.  I am still printing duplicates but on on every line.  The code works.  I think there is something I am overlooking.  

Answer 10

Hi CSharp,

What do you mean about "duplicates but on every line"? Do you mean that, for example the first line is the same as the second line? If so, I think something must be wrong with reading row from the csv file, if cells are supposed to be the bunch of strings of each line. 

Also, when I returned to the original post, I noticed these line:

for(intlineNo =0;lineNo <file2.Length;lineNo++){

                                serverInfo = file1[lineNo];
                                char[] splitString = { ',' };
                                cells = serverInfo.Split(splitString); 

If lineNo here is the index of the rows in file2, I don't think it is right to use it with file1. If you want to get the line from file1, and detect if there is corresponding value from each line of file2, please try the following code instead:

                                char[] splitString = { ',' };
                                cells = line.Split(splitString); 

for(intlineNo =0;lineNo <file2.Length;lineNo++){

Please debug the program, and use add watch to see if each step turns out as expected. You may narrow down the issue to find the root cause.

Hope it helps.

yoyo

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us

Answer 11

I think I know what the problem is but its my lack of understanding in this area.  I believe the reason why lines keep getting printed is because my evaluation equals true every time.  I changed the code up some but here is what I have.

bool flag = false;
                                foreach(string cell in cells)
                                {
                                    if (Lines[lineNo].Contains(cell))
                                    {
                                        if (!cell.Equals(cells[15]) && !cell.Equals(cells[16]) && !cell.Equals(cells[24]) &&
                                            !cell.Equals(cells[25]) && !cell.Equals(cells[26]) && !cell.Equals(cells[27]) &&
                                            !cell.Equals(cells[15]) && !cell.Equals(cells[28]) && !cell.Equals(cells[29]) &&
                                            !cell.Equals(cells[30]) && !cell.Equals(cells[31]))
                                        {
                                            if (flag != true)
                                            {
                                                string files = Lines[lineNo];
                                                serverLines.Add(files);
                                                flag = true;
                                            }
                                        }
                                    }                                    
                                }

I believe I am not checking if index[15] NOT EQUALS index[15] correctly; therefore, each evaluation is true it does not equal this then adds lines to text file resulting in every line being printed.

Answer 12

Hi CSharp,

Thanks for your response.

You may refer to the following solution:

        static void Main(string[] args)
        {
            string serverName="localhost";

            // Read file1.csv
            StreamReader sr = new StreamReader(@"E:\file1.csv");
            List<string> file1 = new List<string>();
            string temp =null;
            while ((temp = sr.ReadLine()) != null)
            {
                file1.Add(temp);
            }

            // Read file2.csv
            sr = new StreamReader(@"E:\file2.csv");
            List<string> file2 = new List<string>();
            while ((temp = sr.ReadLine()) != null)
            {
                file2.Add(temp);
            }
            

            List<string> serverlines = new List<string>();

            // test file2.
            //foreach (string line in file2)
            //{
            //    Console.WriteLine(line);
            //    if(line.Contains("good"))
            //    {
            //    Console.WriteLine(line);
            //    }
            //}



            foreach (string line in file1)
            {
                //Console.WriteLine(line);
                if (line.Contains(serverName))
                {
                    string[] cells = line.Trim().Split(',');

                    bool flag = false; // flag.

                    foreach (string cell in cells)
                    {
                        if (cell != string.Empty)
                        {
                            Console.WriteLine(cell);
                            for (int lineNo = 0; lineNo < file2.Count; lineNo++)
                            {

                                if (file2[lineNo].Contains(cell))
                                {
                                    string files = "Entries Found on Column # " + lineNo + "   " + file2[lineNo];
                                    if (flag != true)
                                    {
                                        serverlines.Add(files);
                                        flag = true;
                                    }
                                }
                            }
                        }
                    }
                }
            }

            foreach (string str in serverlines)
            {
                Console.WriteLine(str);
            }
           
        }

Hope it helps.

Have a nice day!

yoyo

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us

Answer 13

Hi CSharp,

I temporarily mark the reply as answer to push the case forward. You can unmark it if it provides no help. Please feel free to let me know if you need any help. I will keep an eye on this thread in the future.

Thanks for your understanding and have a nice day!

yoyo

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us