Read two text files and join columns in a datagridview c#

Question

I have two text files like this:

Text File 1:

Campionato;Data;Home;Away;HSFT;ASFT;HSHT;HSHT
DenDiv1;07.09.2015;Silkeborg;Helsingor;1;0;0;0
DenDiv1;06.09.2015;Naestved;Roskilde;1;1;0;0
DenDiv1;06.09.2015;Lyngby;Vejle;3;2;2;0

Text File 2:

Data;Home;Away;HODD;AODD
07.09.2015;Silkeborg;Helsingor;1.50;4.09;5.71
06.09.2015;Naestved;Roskilde;2.54;3.20;2.63
06.09.2015;Lyngby;Vejle;2.12;3.33;3.20

I need to join and show them in a datagridview. Could you help me please? Thanks! :)

UPDATE: Home and Away columns are both my primary key.

Is there a problem reading the textfiles or binding the results to a gridview? Show us what you've tried so far. — Bgl86, Sep 14 '15 at 09:09
Create a data class that has all needed properties (from both files); read both files into memory (maybe by hand as `List` or using e.g. Lumenworks CSV reader). Then join both lists by using LINQ and create data class instances. Put these instances into a list which you use as data source for a data grid view. If any of these steps makes trouble, ask a more specific question. — Oliver, Sep 14 '15 at 09:29
@Bgl86 I know how I can read text files but i don't know how I can put them into datatables, join them and show in a datagridview. Thanks :) — marcello gabrielli, Sep 14 '15 at 09:43
@Oliver I am a newbie about C# so I don't know how I can do what you say :( — marcello gabrielli, Sep 14 '15 at 09:44
Do you need to put into database, or JUST join them and show in datagrid? — Arghya C, Sep 14 '15 at 09:47
What is the join criteria for these two files? I can see *duplicates* in the columns `Data`, `Home` and `Away`. Have all three of them to match or just one or two? Please don't write it here as comment, update your question please. — Oliver, Sep 14 '15 at 09:58

Arghya C · Accepted Answer · 2015-09-14T15:29:23.253

These are the class I'm assuming you already have, or you'll create

public class Data1
{
    public string Campionato { get; set; }
    public string Data { get; set; }
    public string Home { get; set; }
    public string Away { get; set; }
    public int HSFT { get; set; }
    public int ASFT { get; set; }
    public int HSHT { get; set; }
    public int HSHT2 { get; set; }
}

public class Data2 //you can use inheritance and reduce duplication of properties
{
    public string Data { get; set; }
    public string Home { get; set; }
    public string Away { get; set; }
    public double HODD { get; set; }
    public double AODD { get; set; }
    public double XODD { get; set; } //no name in sample
}

public class CombinedData //you can use inheritance here too
{
    public string Campionato { get; set; }
    public string Data { get; set; }
    public string Home { get; set; }
    public string Away { get; set; }
    public int HSFT { get; set; }
    public int ASFT { get; set; }
    public int HSHT { get; set; }
    public int HSHT2 { get; set; }
    public double HODD { get; set; }
    public double AODD { get; set; }
    public double XODD { get; set; } //some name
}

You can read the raw data like this

var data1 = File.ReadAllLines(@"C:\YourFolder\Data1.txt").ToList();
data1.RemoveAt(0); //reamoving the header
var data2 = File.ReadAllLines(@"C:\YourFolder\Data2.txt").ToList();
data2.RemoveAt(0); //reamoving the header

Now map them to proper (typed) data list

var dataList1 = new List<Data1>();
foreach(var data in data1)
{
    var columns = data.Split(';'); //add data validations
    dataList1.Add(new Data1
    {
        Campionato = columns[0],
        Data = columns[1],
        Home = columns[2],
        //other string properties
        HSFT = int.Parse(columns[4])
        //other int properties
    });
}

var dataList2 = new List<Data2>();
foreach (var data in data2)
{
    var columns = data.Split(';'); //add data validations
    dataList2.Add(new Data2
    {
        Data = columns[0],
        Home = columns[1],
        //other string properties
        HODD = double.Parse(columns[3])
        //other double properties
    });
}

Now you can join them and bind in your Grid.

Note: This is joining on Data column, which will result in 5 rows total.

var combinedDataList = from d1 in dataList1 //Joining on Data here, use the key you have
                        join d2 in dataList2 on d1.Data equals d2.Data
                        select new CombinedData { Campionato = d1.Campionato, 
                                                    Data = d1.Data, 
                                                    HSFT = d1.HSFT, 
                                                    HODD = d2.HODD }; //map all properties

yourDataGrid.DataSource = combinedDataList; //check syntax here, not tested
yourDataGrid.Bind();

Update

If you want to join on multiple columns (e.g. Data & Home), use this syntax

var combinedDataList = from d1 in dataList1 //Joining on Data & Home, yields 3 rows
                        join d2 in dataList2 on new { d1.Data, d1.Home } equals new { d2.Data, d2.Home }
                        select new CombinedData
                        {
                            Campionato = d1.Campionato,
                            Data = d1.Data,
                            HSFT = d1.HSFT,
                            HODD = d2.HODD //map rest of the properties
                        };

Thanks for your code, but I am a newbie so I will ask you some silly questions :) — marcello gabrielli, Sep 14 '15 at 14:31
@marcellogabrielli Assuming you a newbie only, I made the code simpler than compact. And added explanation as well, but feel free to ask. — Arghya C, Sep 14 '15 at 14:33
My silly questions :) : 1) I have to put class after "name space" or "public partial class"? 2) I have to put other code into a button? 3) I got an error here: yourDataGrid.Bind(); Could you help me please? :) thanks! — marcello gabrielli, Sep 14 '15 at 14:34
@marcellogabrielli [1] Enclose classes within namespace, no need for partial class. [2] I'm not sure which button you are talking about, but yes create a wrapper method which will have all these code, except the class definitions. You can call that method from some button click. [3] I hope you have used the name of your-grid-control here :) Else post the error here. — Arghya C, Sep 14 '15 at 14:38
ok, I checked and correct something and it's almost working, but i need two equals, something like this: join d2 in dataList2 on d1.Home equals d2.Home AND d1.Away equals d2.Away How can I do it please? Thanks :) — marcello gabrielli, Sep 14 '15 at 15:15
@marcellogabrielli check the update :) They could have made the syntax cleaner! — Arghya C, Sep 14 '15 at 15:30
Sorry if I disturb you again, but when I click on a Header column, I can't sort datagridview by any column... Could you help me, please? Thanks! :) — marcello gabrielli, Sep 18 '15 at 18:29
Sorting a table/grid by the column values is not related to this problem, and a completely separate topic in itself :) Can you please try searching in Google or SO? If you don't get appropriate answer, you can post a separate question on that. Make sure you ask specific question on what you are trying to do, and don't forget to show the code that you tried. — Arghya C, Sep 18 '15 at 18:51

score 0 · Answer 2 · answered Sep 14 '15 at 10:17

Here is the data class:

public class MyData
{
    public String Campionato { get; set; }
    public DateTime Data { get; set; }
    public String Home { get; set; }
    public String Away { get; set; }
    public int Hsft { get; set; }
    public int Asft { get; set; }
    public int Hsht { get; set; }
    public int Hsht_2 { get; set; }
    public float Hodd { get; set; }
    public float Aodd { get; set; }
    public float Unknown { get; set; }
}

And here the code to read the files and joining the data:

var file1Data = File.ReadLines(@"File1.csv")
                    .Skip(1) // Skip header
                    .Select(line => line.Split(';'))
                    .Select(elements => new MyData
                    {
                        Campionato = elements[0],
                        Data = DateTime.ParseExact(elements[1], "MM'.'dd'.'yyyy", CultureInfo.InvariantCulture),
                        Home = elements[2],
                        Away = elements[3],
                        Hsft = Int32.Parse(elements[4]),
                        Asft = Int32.Parse(elements[5]),
                        Hsht = Int32.Parse(elements[6]),
                        Hsht_2 = Int32.Parse(elements[7])
                    });

var file2Data = File.ReadLines(@"File2.csv")
                    .Skip(1) // Skip header
                    .Select(line => line.Split(';'))
                    .Select(elements => new MyData
                    {
                        Data = DateTime.ParseExact(elements[0], "MM'.'dd'.'yyyy", CultureInfo.InvariantCulture),
                        Home = elements[1],
                        Away = elements[2],
                        Hodd = float.Parse(elements[3]),
                        Aodd = float.Parse(elements[4])
                    });

var joinedData = file1Data.Join(
                        file2Data,
                        // Key generation should be optimized. Maybe take a look at http://stackoverflow.com/q/263400/1838048
                        myData => myData.Data.GetHashCode() + myData.Home.GetHashCode() + myData.Away.GetHashCode(),
                        myData => myData.Data.GetHashCode() + myData.Home.GetHashCode() + myData.Away.GetHashCode(),
                        (file1, file2) => new MyData
                        {
                            Campionato = file1.Campionato,
                            Data = file1.Data,
                            Home = file1.Home,
                            Away = file1.Away,
                            Hsft = file1.Hsft,
                            Asft = file1.Asft,
                            Hsht = file1.Hsht,
                            Hsht_2 = file1.Hsht_2,
                            Hodd = file2.Hodd,
                            Aodd = file2.Aodd,
                            Unknown = file2.Unknown
                        });


myDataGridView.DataSource = joinedData.ToList();

There is a lot of hard-coded stuff and no kind of error checking in there. So a lot of improvement for yourself, but it should give you a good starting point.

Read two text files and join columns in a datagridview c#

2 Answers2