How to Read CSV File in Asp.Net

Description: I have often read the common question in forum posts, how to upload CSV file records into a database but no one has provided the proper solution and many solutions contain a lot of code that is not required so by considering the preceding requirements I have decided to write this article to provide the solution to insert CSV file records into the database with a minimum amount of code. So let us start creating an application so beginners can also understand.

First create the table named Employee using the following script

CREATETABLE [dbo].[Employee](
    [id] [int] IDENTITY(1,1)NOTNULL,
    [Name] [varchar](50)NULL,
    [City] [varchar](50)NULL,
    [Address] [varchar](50)NULL,
    [Designation] [varchar](50)NULL,
CONSTRAINT [PK_Employee] PRIMARYKEYCLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]   
)ON [PRIMARY]

Now Let us create the sample web application as follows:

1.  "Start" - "All Programs" - "Microsoft Visual Studio 2010".
2.  "File" - "New Website" - "C#" - "Empty Website" (to avoid adding a master page). 
3.  Provide the web site a name such as "InsertCSVFileIntoDataBase" or another as you wish and specify the location.
4.  Then right-click on Solution Explorer - "Add New Item" - Add Web Form. 
5.  Drag and drop one Button and FileUploadercontroler onto the <form> section of the Default.aspx page.

Now the default.aspx Page source code will look such as follows.

<div style="color: White;">
    <table>
        <tr>
            <td>Select File</td>
            <td>
                <asp:FileUploadID="FileUpload1" runat="server" />
            </td>
            <td></td>
            <td>
                <asp:ButtonID="Button1" runat="server" Text="Upload" OnClick="Button1_Click" />
            </td>
        </tr>
    </table>
    <br/>
    <br/>
</div>

Create a function for SqlConnection as:

private void connection()
{
  sqlconn = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
  con = new SqlConnection(sqlconn);
} 

Create function to insert CSV file records into database using SqlBulkCopy as: 

//Function to Insert Records 
private void InsertCSVRecords(DataTable csvdt)
{
    connection();
    //creating object of SqlBulkCopy
    SqlBulkCopyobjbulk = newSqlBulkCopy(con);
    //assigning Destination table name   
    objbulk.DestinationTableName = "Employee";
    //Mapping Table column   
    objbulk.ColumnMappings.Add("Name", "Name");
    objbulk.ColumnMappings.Add("City", "City");
    objbulk.ColumnMappings.Add("Address", "Address");
    objbulk.ColumnMappings.Add("Designation", "Designation");
    //inserting Datatable Records to DataBase
    con.Open();
    objbulk.WriteToServer(csvdt);
    con.Close();
}

Write the following code  to read  CSV File Records and call InsertCSVRecords function on button click as

protected void Button1_Click(object sender, EventArgs e)
{
    //Creating object of datatable
    DataTable tblcsv = newDataTable();
    //creating columns 
    tblcsv.Columns.Add("Name");
    tblcsv.Columns.Add("City");
    tblcsv.Columns.Add("Address");
    tblcsv.Columns.Add("Designation");
    //getting full file path of Uploaded file 
    stringCSVFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);
    //Reading All text 
    stringReadCSV = File.ReadAllText(CSVFilePath);
    //spliting row after new line 
    foreach (stringcsvRowinReadCSV.Split('\n'))
    {
        if (!string.IsNullOrEmpty(csvRow))
        {
            //Adding each row into datatable
            tblcsv.Rows.Add();
            int count = 0;
            foreach (stringFileRecincsvRow.Split(','))
            {
                tblcsv.Rows[tblcsv.Rows.Count - 1][count] = FileRec;
                count++;
            }
        }
    }
    //Calling insert Functions 
    InsertCSVRecords(tblcsv);
}

Now click on the Upload button and see the records in the database table as.

Now you have seen how the records are inserted into the database with minimal code and effort.

Notes:-

For detailed code please download the sample Zip file.
Do a proper validation such as date input values when implementing.
Make the changes in the web.config file depending on your server details for the connection string.

Author : Umesh Patel

Related Posts