Trying to import csv and xls files to an SQL database using ASP.NET and C#

Question

I am a student and quite new to programming, and I was given a task to do using ASP.NET and C#, without being taught either. The plan is to learn to teach ourselves.

The task I am stuck on is making a website import from a CSV or XLS file to a SQL database of last year's room bookings around the campus.

While I have learned a few things by following tutorials, I am stuck on finding a way to program c# to "read" a csv file (with the delimiter used to separate entries being the comma ",") and an xls file into a table using Microsoft's SQL database.

So, what I did is, after having downloaded Visual Studio, and getting started with asp.net's webforms, I started by creating a button on the .aspx form to trigger the import upon being clicked:

 <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Import_button_Click"/>

I would name my function "Import_Button_Click"

Here, I found at a tutorial a .dll library called Filehelpers. I it looked promising, so I gave it a try. I added a reference of it at Visual Studio and also added "using Filehelpers;" at the beginning of the C# form.

Here is a link: http://filehelpers.sourceforge.net/

So, I use:

FileHelperEngine engine = new FileHelperEngine(typeof(CSVfile));

and

 CSVfile[] result = engine.ReadFile("C:\\CSVDATA\\previousyear.csv") as CSVfile[];

to make it read the CSV file. Meanwhile, I create a class to store every entry of the CSV file as a variable:

 [DelimitedRecord(",")]
    public class CSVfile  //CSVfile class being defined
    {
        public int Request_ID;
        public int Priority;
        public int Module_ID;
        public string Day;
        public string Start_Time;
        public int Length;
        public string Park;
        public int Students;
        public string Room_Code;
        public string Status;
        public int Semester_ID;
        public int Linked_Request;
      public int Week_1;
        public int Week_2;
        public int Week_3;
        public int Week_4;
        public int Week_5;
        public int Week_6;
        public int Week_7;
        public int Week_8;
        public int Week_9;
        public int Week_10;
        public int Week_11;
        public int Week_12;
        public int Week_13;
        public int Week_14;
        public int Week_15;

}

and after that, right below where it reads previousyear.csv, I do the loop that will take every entry and put it at the correct variables:

 DataTable table = new DataTable();
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(int));
       table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(string));
        table.Columns.Add(" ", typeof(string));
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(string));
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(string));
        table.Columns.Add(" ", typeof(string));
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(int)); 
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(int));
        table.Columns.Add(" ", typeof(int));

foreach (CSVfile row in result) { Console.WriteLine(row.Request_ID + " " + row.Priority);

            table.Rows.Add(row.Request_ID, row.Priority);

        }

but the thing is, I can't make it output the results anywhere, not even using a gridview

Also, it turns that it's quite troublesome to run it on other computers with the added library.

Today, a teammate of mine gave me the SQL code that I should use to put my variables to the database, which is this:

public string GetConnectionString()
    {
        return System.Configuration.ConfigurationManager.ConnectionStrings["team03ConnectionString"].ConnectionString;
        //the "ConnStringName" is the name of your Connection String that was set up from the Web.Config


    }

    protected void BookRoom_Click(object sender, EventArgs e)
    {


        System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(GetConnectionString());
        // string sql = "INSERT INTO tests (column6,Column2,Column3,Column4,Column5) VALUES (@Val1,@Val2,@Val3,@Val4,@Val5)";
        // string sql = "Insert INTO Requests (Priority, Module_ID, Day,Start_Time, Length, Park, Students, Room_Code, Status, Semester_ID, Week_1,Week_2,Week_3,Week_4,Week_5,Week_6,Week_7,Week_8,Week_9,Week_10,Week_11,Week_12,Week_13,Week_14,Week_15) VALUES (@Priority, @Module_ID, @Day,@Start_Time, @Length, @Park, @Students, @Room_Code, @Status, @Semester_ID, @Week_1, @Week_2, @Week_3, @Week_4, @Week_5, @Week_6, @Week_7, @Week_8, @Week_9, @Week_10, @Week_11, @Week_12, @Week_13, @Week_14, @Week_15)";
        string sql = "Insert INTO Requests (Priority, Module_ID, Day,Start_Time, Length, Park, Students, Room_Code, Status,Room_Allocated, Semester_ID, Week_1,Week_2,Week_3,Week_4,Week_5,Week_6,Week_7,Week_8,Week_9,Week_10,Week_11,Week_12,Week_13,Week_14,Week_15) OUTPUT INSERTED.Request_ID VALUES (@Priority, @Module_ID, @Day,@Start_Time, @Length, @Park, @Students, @Room_Code, @Status,@Room_Allocated, @Semester_ID, @Week_1, @Week_2, @Week_3, @Week_4, @Week_5, @Week_6, @Week_7, @Week_8, @Week_9, @Week_10, @Week_11, @Week_12, @Week_13, @Week_14, @Week_15)";

        try
        {


            conn.Open();
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@Priority", "0");
            cmd.Parameters.AddWithValue("@Module_ID", moduleSelect.Text);
            cmd.Parameters.AddWithValue("@Day", Day.Text);
            cmd.Parameters.AddWithValue("@Start_Time", StartTime.Text);
            cmd.Parameters.AddWithValue("@Length", "1");
            cmd.Parameters.AddWithValue("@Park", Request.QueryString["Pk"].ToString());
            cmd.Parameters.AddWithValue("@Students", NumOfStudents.Text);
            cmd.Parameters.AddWithValue("@Room_Code", roomChosen.Text);
            cmd.Parameters.AddWithValue("@Status", "Pending");
            cmd.Parameters.AddWithValue("@Room_Allocated", roomChosen.Text);
            cmd.Parameters.AddWithValue("@Semester_ID", "7");
            cmd.Parameters.AddWithValue("@Week_1", weeknumber1.Text);
            cmd.Parameters.AddWithValue("@Week_2", weeknumber2.Text);
            cmd.Parameters.AddWithValue("@Week_3", weeknumber3.Text);
            cmd.Parameters.AddWithValue("@Week_4", weeknumber4.Text);
            cmd.Parameters.AddWithValue("@Week_5", weeknumber5.Text);
            cmd.Parameters.AddWithValue("@Week_6", weeknumber6.Text);
            cmd.Parameters.AddWithValue("@Week_7", weeknumber7.Text);
            cmd.Parameters.AddWithValue("@Week_8", weeknumber8.Text);
            cmd.Parameters.AddWithValue("@Week_9", weeknumber9.Text);
            cmd.Parameters.AddWithValue("@Week_10", weeknumber10.Text);
            cmd.Parameters.AddWithValue("@Week_11", weeknumber11.Text);
            cmd.Parameters.AddWithValue("@Week_12", weeknumber12.Text);
            cmd.Parameters.AddWithValue("@Week_13", weeknumber13.Text);
            cmd.Parameters.AddWithValue("@Week_14", weeknumber14.Text);
            cmd.Parameters.AddWithValue("@Week_15", weeknumber15.Text);
            //cmd.CommandType = System.Data.CommandType.Text;
            //Int32 newId = (Int32)cmd.ExecuteScalar();
            cmd.ExecuteNonQuery();


        } //End of try

        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Insert Error:";
            msg += ex.Message;
            throw new Exception(msg);

        }

        catch (FormatException ee)
        {

            System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('Please enter a valid value');</SCRIPT>");
        }

        catch (System.Exception eeee)
        {
            System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('System Exception');</SCRIPT>");

        }

        finally
        {
            conn.Close();
        }
    }//End of insertInfor()

}

and he told me that I had until Sunday to figure out the rest.

So having said all these, I am not looking for someone to do my coursework, but rather advice from experienced people here, because asp.net seems so confusing. Is there a way to do this without using external libraries? Can I make gridview show the table from the database? What would be the best approach?

Thanks a lot!

UPDATE:

@Tim

Thanks again for all the help!

This is what I have at the moment:

        using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data;
    using System.Data.OleDb;
    using FileHelpers;


namespace ImportPage
{

    public class CSVFile
    {

        public int Request_ID { get; set; }
        public int Priority { get; set; }
        //...

    }




    public class CSV
    {
        public string GetConnectionString()
        { return System.Configuration.ConfigurationManager.ConnectionStrings["team03ConnectionString"].ConnectionString; }


        protected void button1_Click(object sender, EventArgs e)
        {

            List<CSVFile> entries = new List<CSVFile>();

            using (TextFieldParser parser = new TextFieldParser(@"C:\CSVDATA\PreviousYear.csv"))
            {

                parser.TextFieldType = FieldType.Delimited;
                parser.Delimiters = new string[] { "," };
                string[] fields;

                while (!parser.EndOfData)
                {
                    fields = parser.ReadFields();
                    entries.Add(new CSVFile()
                    {
                        Request_ID = Convert.ToInt32(fields[0]),
                        Priority = Convert.ToInt32(fields[1]),

                        //...

                    });
                }

            }


        System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(GetConnectionString());

        string sql = "Insert INTO Requests (Priority) OUTPUT INSERTED.Request_ID VALUES (@Priority)";

        try
        {


            conn.Open();
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@Priority", "0");


            cmd.ExecuteNonQuery();


        } //End of try

        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Insert Error:";
            msg += ex.Message;
            throw new Exception(msg);

        }

        catch (FormatException ee)
        {

            System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('Please enter a valid value');</SCRIPT>");
        }

        catch (System.Exception eeee)
        {
            System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('System Exception');</SCRIPT>");

        }

        finally
        {
            conn.Close();
        }


}


        }
}

I am attempting to make it work for a CSV file with only 2 entries for starters: Request ID and Priority, to make something simple and build upon it.

To make things short, I am confused on how to put my code, your code and my teammates code to make something working (as I dont understand everything in the code)

UPDATE 2:

Here is my .aspx code, nothing special, just 1 button , 1 gridview

<!DOCTYPE html>
<script runat="server">

    Protected Sub Import_button_Click(sender As Object, e As EventArgs)

    End Sub

    Protected Sub Page_Load(sender As Object, e As EventArgs)

    End Sub
</script>
<html lang="en">

  <head>
    <meta charset="utf-8">

    <title>Timetabling Support Website</title>

    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <!-- Loading Bootstrap -->
    <link href="css/bootstrap.css" rel="stylesheet">

    <!-- Loading Flat UI -->
    <link href="css/flat-ui.css" rel="stylesheet">

     <!-- Loading Unsemantic -->
    <link href="css/unsemantic-grid-responsive.css" rel="stylesheet"> 

    <!-- Loading Personalized Style -->
    <link href="css/style.css" rel="stylesheet"> 
    <link rel="shortcut icon" href="images/favicon.ico">  


    <!-- HTML5 shim, for IE6-8 support of HTML5 elements. All other JS at the end of file. -->
    <!--[if lt IE 9]>
      <script src="js/html5shiv.js"></script>
    <![endif]-->
  </head>

  <body>
      <form id="form1" runat="server">
    <div class="grid-container">

      <div class="header grid-100">
        <div class="banner grid-70">
          <img src="images/banner3.png" id="banner" alt="Loughborough Uni Logo" />
        </div>
        <div class="logout grid-30">
          <p id="logout"> Welcome, Computer Science Timetabler. | <a href="index.html">Logout</a></p>
        </div>
      </div>


      <div class="navbar navbar-inverse">
          <div class="navbar-inner">
            <ul class="nav">
              <li>
                <a href="home.html">
                  Home
                </a>
              </li>
              <li>
                <a href="#">
                  Requests
                </a>
                <ul>
                  <li>
                    <a href="request_new.html">New Request</a>
                  </li>
                  <li>
                    <a href="request_import.html">Import Requests</a>
                  </li>
                  <li>
                    <a href="request_current.html">Current Requests</a>
                  </li>
                  <li>
                    <a href="request_adhoc.html">Ad-Hoc Request</a>
                  </li>
                </ul> <!-- /Sub menu -->
              </li>
              <li>
                <a href="room_availability.html">
                  Room Availability
                </a>
              </li>
              <li>
                <a href="#">
                  History
                </a>
                <ul>
                  <li>
                    <a href="#">Semester 1</a>
                    <ul>
                      <li>
                        <a href="history_s1priority.html">Priority Round</a>
                      </li>
                      <li>
                        <a href="history_s1round1.html">Round 1</a>
                      </li>
                      <li>
                        <a href="history_current.html">Round 2</a>
                      </li>
                      <li>
                        <a href="history.html">Final Allocations</a>
                      </li>
                    </ul> <!-- /Sub menu -->
                  </li>
                  <li>
                    <a href="#">Semester 2</a>
                    <ul>
                      <li>
                        <a href="history.html">Priority Round</a>
                      </li>
                      <li>
                        <a href="history.html">Round 1</a>
                      </li>
                      <li>
                        <a href="history.html">Round 2</a>
                      </li>
                      <li>
                        <a href="history.html">Final Allocations</a>
                      </li>
                    </ul> <!-- /Sub menu -->
                  </li>
                </ul> <!-- /Sub menu -->
              </li>
              <li>
                <a href="#">
                  Maintenance
                </a>
                <ul>
                  <li>
                    <a href="module_add.html">Add Module</a>
                  </li>
                  <li>
                    <a href="module_edit.html">Edit Module</a>
                  </li>
                </ul> <!-- /Sub menu -->
              </li>
              <li>
                <a href="help.html">
                  Help
                </a>
              </li>
            </ul>
          </div><!--/.nav-collapse -->
      </div>

      <div class="content center">
          <h1>Import Request
          </h1>
         <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Import_button_Click"/>
          <asp:GridView ID="GridView1" runat="server">


          </asp:GridView>

           </div>

         <div class="grid-100 footer">
        <p>Copyright © 2013 Team 3 Timetabling Support Website</p> 
      </div>

    </div> <!-- /container -->


      <asp:SqlDataSource ID="SqlDataSource1" runat="server"></asp:SqlDataSource>
    <!-- Load JS here for greater good =============================-->
    <script src="js/jquery-1.8.2.min.js"></script>
    <script src="js/jquery-ui-1.10.0.custom.min.js"></script>
    <script src="js/jquery.dropkick-1.0.0.js"></script>
    <script src="js/custom_checkbox_and_radio.js"></script>
    <script src="js/custom_radio.js"></script>
    <script src="js/jquery.tagsinput.js"></script>
    <script src="js/bootstrap-tooltip.js"></script>
    <script src="js/jquery.placeholder.js"></script>
    <script src="http://vjs.zencdn.net/c/video.js"></script>
    <script src="js/application.js"></script>
    <!--[if lt IE 8]>
      <script src="js/icon-font-ie7.js"></script>
      <script src="js/icon-font-ie7-24.js"></script>
    <![endif]-->
      </form>
  </body>
</html>

It appears to have lots of problems with this part of the code:

 string sql = "Insert INTO Requests (Priority); 
                   OUTPUT INSERTED.Request_ID 
                   VALUES (@Priority)";

Update 3:

Ok, fixed that piece of code. Just had to put it all at the same row. Now there is only 1 error when I try to build the app:

Error 3 The type or namespace name 'SqlCommand' could not be found (are you missing a using directive or an assembly reference?)

So, SqlCommand is not recognized. Do I need to add a reference or something for it to be recognized? The same goes for SqlConnection

Update 4

This is the code I am using now, as I make it work in Visual Studio

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.VisualBasic.FileIO;
using System.Data.SqlClient;

namespace ImportPage
{

    public class CSVFile
    {

        public int Request_ID { get; set; }
        public int Priority { get; set; }
        public int Module_ID { get; set; }
        //...

    }




    public class CSV
    {
        public string GetConnectionString()
        { return System.Configuration.ConfigurationManager.ConnectionStrings["team03ConnectionString"].ConnectionString; }


        protected void button1_Click(object sender, EventArgs e)
        {

            List<CSVFile> entries = new List<CSVFile>();

            using (TextFieldParser parser = new TextFieldParser(@"PreviousYear.csv"))
            {

                parser.TextFieldType = FieldType.Delimited;
                parser.Delimiters = new string[] { "," };
                string[] fields;

                while (!parser.EndOfData)
                {
                    fields = parser.ReadFields();
                    entries.Add(new CSVFile()
                    {
                        Request_ID = Convert.ToInt32(fields[0]),
                        Priority = Convert.ToInt32(fields[1]),
                        Module_ID = Convert.ToInt32(fields[2])
                        //...

                    });
                }

            }


        using (SqlConnection conn = new SqlConnection(GetConnectionString()))
{


    string sql = "Insert INTO Requests (Priority, Module_ID) OUTPUT INSERTED.Request_ID VALUES (@Priority, @Module_ID)";

    try
    {

        conn.Open();

        foreach (CSVFile entry in entries)
        {

            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {

                cmd.Parameters.AddWithValue("@Priority", entry.Priority);
                cmd.Parameters.AddWithValue("@Module_ID", entry.Module_ID);

                // ...
                cmd.ExecuteNonQuery();
            }
        }
    }
    catch (System.Data.SqlClient.SqlException ex)
    {
        string msg = "Insert Error:";
        msg += ex.Message;
        throw new Exception(msg);
    }
    catch (FormatException ee)
    {
        System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('Please enter a valid value');</SCRIPT>");
    }
    catch (System.Exception eeee)
    {
        System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('System Exception');</SCRIPT>");

    }
}


}










}


        }

Answer 1

There is a little known but surprising useful file parser in the .NET framework - Microsoft.VisualBasic.FileIO.TextFieldParser (despite it's name, it can be used in C#). To give an abbreviated example of its use, I would do the following.

First, I'd implement automatic properties in your CSVFile class, rather than public fields:

public class CSVFile
{

    public int Request_ID { get; set; }
    public int Priority { get; set; }
    public int Module_ID { get; set; }
    public string Day { get; set; }
    public string Start_Time { get; set; }
    // and so on
}

Next, I would build a List<T> (generic list) of CSVFile instances, populating each instance with the data from the CSV file (using TextFieldParser). You'll need to add a reference to Microsoft.VisualBasic.FileIO and the using directive.

using Microsoft.VisualBasic.FileIO;

List<CSVFile> entries = new List<CSVFile>();

using (TextFieldParser parser = new TextFieldParser(@"C:\CSVDATA\PreviousYear.csv"))
{

    parser.TextFieldType = FieldType.Delimited;
    parser.Delimiters = new string[]{','};
    string[] fields;

    while (!parser.EndOfData)
    {
        fields = parser.ReadFields();
        entries.Add(new CSVFile() { Request_ID = Convert.ToInt32(fields[0]), 
                                    Priority = Convert.ToInt32(fields[1]),
                                    Module_ID = Convert.ToInt32(fields[2]),
                                    Day = fields[3],
                                    Start_Time = fiedls[4],
                                    // and the rest of the properties, casting as needed
                                   };
    }
}

The end result of the above code snippet will be a List<T> of CSVFiles. With the automatic properties, you can initialize the instance when you create it and add it to the list entries.

You can then bind the list to a GridView like this:

GridView1.DataSource = entries;
GridView1.DataBind();

It's not clear from your question whether you're updating the table with all the entries or just selected ones. Let's assume (for purposes of this example) that it's all of them. Your teammate has given you what you need - put it inside a loop and you can enter all the data from the CSV file into the SQL database, like this:

// It's considered best practice to use a `using` block with `SqlConnection`
// (among other objects). The `using` block ensures the connection is properly
// disposed of once execution leaves the block.
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{

    // I'm breaking into individual lines here simply 
    // so it's easier to read on SO
    string sql = "Insert INTO Requests (Priority, Module_ID, Day, 
                                        Start_Time, Length, Park, 
                                        Students, Room_Code, Status,
                                        Room_Allocated, Semester_ID,
                                        Week_1, Week_2, Week_3, Week_4,
                                        Week_5, Week_6, Week_7, Week_8,
                                        Week_9, Week_10, Week_11, Week_12,
                                        Week_13, Week_14, Week_15) 
                   OUTPUT INSERTED.Request_ID 
                   VALUES (@Priority, @Module_ID, @Day, @Start_Time, 
                           @Length, @Park, @Students, @Room_Code,
                           @Status, @Room_Allocated, @Semester_ID, 
                           @Week_1, @Week_2, @Week_3, @Week_4, @Week_5, 
                           @Week_6, @Week_7, @Week_8, @Week_9, @Week_10, 
                           @Week_11, @Week_12, @Week_13, @Week_14, @Week_15)";

    try
    {

        conn.Open();

        foreach (CSVFile entry in entries)
        {

            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {

                cmd.Parameters.AddWithValue("@Priority", entry.Priority);
                cmd.Parameters.AddWithValue("@Module_ID", entry.Module_ID);
                cmd.Parameters.AddWithValue("@Day", entry.Day);
                cmd.Parameters.AddWithValue("@Start_Time", entry.Start_Time);
                // And so on

                cmd.ExecuteNonQuery();
            }
        }
    }
    catch (System.Data.SqlClient.SqlException ex)
    {
        string msg = "Insert Error:";
        msg += ex.Message;
        throw new Exception(msg);
    }
    catch (FormatException ee)
    {
        System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('Please enter a valid value');</SCRIPT>");
    }
    catch (System.Exception eeee)
    {
        System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('System Exception');</SCRIPT>");

    }
} // Since we're in a `using` block, there is no need for the `finally` block 
  // close the connection.

By the way, kudos to your teammate for showing you paramaterized SQL queries - these are very important to defend against SQL Injection Attacks.

Hopefully this will help you.

Answer 2

You need to create a table with the same fields from your csv file for example your csv file have id,FirstName,LastName,BirthDate

The location of the file is C:\csvtest.txt

1,James,Smith,19750101

2,Meggie,Smith,19790122

3,Robert,Smith,20071101

4,Alex,Smith,20040202

CREATE TABLE tablename
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

After Creation of the table you can insert the csv data in this table by following command. You need to pass the your csv file location.

BULK
INSERT tablename
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO