Step by step instructions on how to convert Excel spreadsheet to Entity Framework solution

Sometimes you have some Excel data and all you want to do is quickly analyse it. This article details how to quickly import the data into an SQL database and get an entity framework model mapped to it so that you can quickly mess with the data however you like.

For this example, I have a spreadsheet of all the AFL matches since 2009 and their respective odds. I got this data from this link (http://www.aussportsbetting.com/data/historical-afl-results-and-odds-data/). 

Depending on the excel spreadsheet, you might need to clean it up a little bit. In the case of this dataset, I had to delete the first row because it was an extra header that was confusing my import tool.

1.       Firstly, import data into a sql table using the SQL import data tool (if table doesn’t have a primary key, you’ll need to create one)

a. Open up SQL Server and create a new database. Mine will be called AFLOdds.

b. Right click properties and select Import Data

c. In Choose a data source, select the Excel file

d. In Choose a Destination, find your Database

e. In Specify Table Copy or Query – Choose Copy data from one or more tables or views

f. Choose the sheet that you would like to copy

g. After going through the process, you should successfully see that a table is created. Check that there is data inside the table.

h. (Optional) You may need to actually add a primary key.

2.       Create new visual studio solution

3.       Add entity framework from nuget

4.       Add a new file -> ADO entity data object

5.       Select Code First from database

Note – if it’s not appearing, you may need to install it http://stackoverflow.com/questions/22647674/code-first-from-database-template-not-showing-in-visual-studio-entity-data-mod

6.       Find your database and select all tables and views you want. Models should be generated

7.Your solution can now connect to the database.  You can paste something like below to test that it works.

static void Main(string[] args)

        {

            using (var db = new Model1())

            {

                var datums = db.Data.ToList();

                foreach (var rows in datums)

                {

                    Console.WriteLine(String.Format("{0} vs {1}", rows.Home_Team, rows.Away_Team));

                }

            }

            Console.ReadLine();

        }

Now you can use entity framework and the power of LINQ queries to analyse the data however you like.