Comparing excel sheet to text file

Question

I have the following data from an excel sheet:

06:07:00     6:07

             Data1
             Data2
             Data3
             Data4  

06:15:00    06:15

            Data5
            Data6
            Data7
            Data8

I want to compare this to the following data from text file:

XXXXXXXXXX   06:08:32   13.0 Data1
XXXXXXXXXX   06:08:45    6.0 Data2
xxxxxxxxxx   06:08:51    5.0 Data3
xxxxxxxxxx   06:08:56   13.0 Data4
xxxxxxxxxx   06:13:44    9.0 Data5      
xxxxxxxxxx   06:13:53   11.0 Data6 
xxxxxxxxxx   06:14:04    6.0 Data7
xxxxxxxxxx   06:14:10   13.0 Data8

As I want to use the time to compare the two files (excel with text), Time is different for each group. Group1(data1 to Data4), group2 (Data5-data8).

Does anyone have any idea how to go about this situation.

EDIT1:

Here is what I tried to do:

private void doTest(string time)
    {
        TimeSpan ts = TimeSpan.Parse(time);
        int hours = ts.Hours;
        int min = ts.Minutes;
        int sec = ts.Seconds;
        int minstart, minend;
        string str;

        minstart = min - 5;
        minend = min + 5;

        while (min != minend)
        {
            sec = sec + 1;

            if (sec < 60)
            {
                if (hours < 10)
                    str = hours.ToString().PadLeft(2, '0');
                else str = hours.ToString();
                if (minstart < 10)
                    str = str + minstart.ToString().PadLeft(2, '0');
                else str = str + minstart.ToString();
                if (sec < 10)
                    str = str + sec.ToString().PadLeft(2, '0');
                else str = str + sec.ToString();



                chkwithtext(str);

            }

            else if (sec == 60)
            {
                sec = 00;
                min = min + 1;
                str = hours.ToString() + min.ToString() + sec.ToString();
                chkwithtext(str);
            }

        }

    }


    private void chkwithtext(string str)
    {
        // check with the text file here if time doesn't match go
        // back increment the time with 1sec and then check here again        

    }

Please see my EDIT1. This method of acheiving what I want seems to be complex, does anyone have a shorter way. — user726720, Jan 27 '14 at 07:17
I don't understand what do you mean by "comparing two files". I don't see how is the doTest() method related to excel-txt comparing. — Tarec, Jan 29 '14 at 13:35
I can't tell what you're trying to do either. Can you explain where the data comes from and what you're responsible for doing with it? — Beth, Feb 04 '14 at 15:58

Neil T · Answer 1 · 2014-02-05T13:53:47.877

It's not precisely clear how you are 'comparing' the times, but for this answer I'll make the assumption that data from the text file is to be compared if, and only if, its timestamp is within x minutes (defaulting to x = 5) of the Excel timestamp.

My recommendation would be to use an Excel add-in called Schematiq for this - you can download this (approx. 9MB) from http://schematiq.htilabs.com/ (see screenshots below). It's free for personal, non-commercial use. (Disclaimer: I work for HTI Labs, the authors of Schematiq.)

However, I'd do the time handling in Excel. First we'll calculate the start/stop limits for the Excel timestamps. For example, for the first time (06:07:00) we want the range 6:02-6:12. We'll also break the actual, 'start' and 'end' times into hours, minutes and seconds for ease later on. The Excel data sheet looks like this:

enter image description here

Next we need a Schematiq 'template function' which will take the start and end times and return us a range of times. This template is shown here:

enter image description here

The input values to this function are effectively 'dummy' values - the function is compiled internally by Schematiq and can then be called with whatever inputs are required. The 'Result' cell contains text starting with '~#...' (and likewise several of the previous cells) - this indicates a Schematiq data-link containing a table, function or other structure. To view it, you can click the cell and look in the Schematiq Viewer which appears as a task pane within Excel like this:

enter image description here

In other words, Schematiq allows you to hold an entire table of data within a single cell.

Now everything is set up, we simply import the text file and get Schematiq to do the work for us. For each 'time group' within the Excel data, a suitable range of times is generated and this is matched against the text file. You are returned all matching data, plus any unmatched data from both Excel and the text file. The necessary calculations are shown here:

enter image description here

Your Excel worksheet is therefore tiny, and clicking on the final cell will display the final results in the Schematiq Viewer. The results, including the Excel data and the 'template calculation', are shown here:

enter image description here

To be clear, what you see in this screenshot is the entire contents of the workbook - there are no other calculations taking place anywhere other than in the actual cells you see.

The 'final results' themselves are shown enlarged here:

enter image description here

This is exactly the comparison you're after (with a deliberately introduced error - Data9 - in the text file, to demonstrate the matching). You can then carry out whatever comparisons or further analysis you need to.

All of the data-links represent the use of Schematiq functions - the syntax is very similar to Excel and therefore easy to pick up. As an example, the call in the final cell is:

=tbl.SelectColumns(D21, {"Data","Text file"}, TRUE)

This selects all columns from the Schematiq table in cell D21 apart from the 'Data' and 'Text file' columns (the final Boolean argument to this function indicates 'all but').

I'd recommend downloading Schematiq and trying this for yourself - I'd be very happy to email you a copy of the workbook I've put together, so it should just run immediately.

score 0 · Answer 2 · answered Jan 29 '14 at 14:05

I'm not sure if I understand what do you mean, but I'd start with exporting excel file to csv with ; separator - it's way much easier to work this way. Then some simple container class:

public class DataTimeContainer
{
    public string Data;
    public string TimeValue1 = string.Empty;
    public string TimeValue2 = string.Empty;
}

And use it this way:

//Processint first file
List<DataTimeContainer> Container1 = new List<DataTimeContainer>();
string[] lines = File.ReadAllLines("c:\\data1.csv");
string groupTimeValue1 = string.Empty;
string groupTimeValue2 = string.Empty;
foreach (string[] fields in lines.Select(l => l.Split(';')))
{
    //iterating over every line, splited by ';' delimiter
    if (!string.IsNullOrWhiteSpace(fields[0]))
    {
        //we're in a line having both values, like:
        //06:07:00 ; 6:07
        groupTimeValue1 = fields[0];
        groupTimeValue2 = fields[1];
    }
    else
        //we're in line looking like this:
        //            ; DataX
        Container1.Add(new DataTimeContainer(){Data = fields[1], TimeValue1 = groupTimeValue1, TimeValue2 = groupTimeValue2});
}

//Processing second file
List<DataTimeContainer> Container2 = new List<DataTimeContainer>();
lines = File.ReadAllLines("c:\\data2.txt");
foreach (string[] fields in lines.Select(l => l.Split(';')))
{
    Container2.Add(new DataTimeContainer() { TimeValue1 = fields[1], TimeValue2 = fields[2], Data = fields[3]});
}

DoSomeComparison();

Of course I'm using strings as data types because I do not know what kind of objects they're supposed to be. Let me know how's that working for you.

score 0 · Answer 3 · answered Feb 03 '14 at 18:41

If this is a one-time comparison, I would recommend just pulling the text file into Excel (using the Text-to-Columns tools if needed) and running a comparison there with the built-in functions.

If however you need to do this frequently, something like Tarec suggested would be a good start. It seems like you're trying to compare separate event logs within a given timespan (?) - your life will be easier if you parse to objects with DateTime properties instead of comparing text strings.

score 0 · Answer 4 · edited May 23 '17 at 12:11

0

Populate your Data from your 2 sources(excel and text file) into 2 lists .
Make sure that Lists are of same type . I would recommend Convert your Excel data to Text File Format .. and then populate Each line of text file and Excel file data into string List.
And then you can compare your List by using the LINQ or Enumerable Methods . Quickest way to compare two List<>

edited May 23 '17 at 12:11

Community

1
1

answered Feb 04 '14 at 14:41

spetzz

679
8
19

Comparing excel sheet to text file

4 Answers4