How to import / export database data from / to csv/xml/excel using ASP.NET and C#

Here I explain Part 1, import / export database data from/to CSV file.

Step - 1 : Create New Project.

Go to File > New > Project > Select asp.net web forms application > Entry Application Name > Click OK.

Step-2: Add a Database.

Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.

Step-3: Create table for import / export data.

Open Database > Right Click on Table > Add New Table > Add Columns > Save > Enter table name > Ok.

In this example, I have used one tables as below

Step-4: Add Entity Data Model.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.
A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >

Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish.

Step-5: Add a Webpage and Design for import, show and export data.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select web form/ web form using master page under Web > Enter page name > Add.

HTML Code 

     <h3>Import / Export database data to / from csv/xml/excel format.</h3>
    <div>
        <h3>Import / Export data from csv.</h3>
        <div>
            <table>
                <tr>
                    <td>Select File : </td>
                    <td>
                        <asp:FileUpload ID="FileUpload1" runat="server" /></td>
                    <td>
                        <asp:Button ID="btnImportFromCSV" runat="server" Text="Import Data to Database" OnClick="btnImportFromCSV_Click" />
                    </td>
                </tr>
            </table>
            <div>
                <br />
                <asp:Label ID="lblMessage" runat="server" Font-Bold="true" />
                <br />
                <asp:GridView ID="gvData" runat="server" AutoGenerateColumns="false">
                    <EmptyDataTemplate>
                        <div style="padding:10px;">No Data Found!</div>
                    </EmptyDataTemplate>
                    <Columns>
                        <asp:BoundField HeaderText="Employee ID" DataField="EmployeeID" />
                        <asp:BoundField HeaderText="Company Name" DataField="CompanyName" />
                        <asp:BoundField HeaderText="Contact Name" DataField="ContactName" />
                        <asp:BoundField HeaderText="Contact Title" DataField="ContactTitle" />
                        <asp:BoundField HeaderText="Address" DataField="EmployeeAddress" />
                        <asp:BoundField HeaderText="Postal Code" DataField="PostalCode" />
                    </Columns>
                </asp:GridView>
                <br />
                <asp:Button ID="btnExportToCSV" runat="server" Text="Export Data to CSV" OnClick="btnExportToCSV_Click" />
            </div>
        </div>
    </div>    
        

Step-6: Write code into page load event for show data.

Write below code into Page_Load event for show data from database.

            
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                populateDatabaseData();
                lblMessage.Text = "Current Database Data.";
            }
        }
        

   And here is the functioin populateDatabaseData

            private void populateDatabaseData()
            {
                using (MuDatabaseEntities dc = new MuDatabaseEntities())
                {
                    gvData.DataSource = dc.EmployeeMasters.ToList();
                    gvData.DataBind();
                }
            }
        

Step-7: Write code for import Data from csv to database

Write below code into button click event for import Data from csv to database.

            protected void btnImportFromCSV_Click(object sender, EventArgs e)
            {
                if (FileUpload1.PostedFile.ContentType == "text/csv" || FileUpload1.PostedFile.ContentType == "application/vnd.ms-excel")
                {
                    string fileName = Path.Combine(Server.MapPath("~/UploadDocuments"), Guid.NewGuid().ToString() + ".csv");
                    try
                    {
                        FileUpload1.PostedFile.SaveAs(fileName);
 
                        string[] Lines = File.ReadAllLines(fileName);
                        string[] Fields;
 
                        //Remove Header line
                        Lines = Lines.Skip(1).ToArray();
                        List<EmployeeMaster> emList = new List<EmployeeMaster>();
                        foreach (var line in Lines)
                        {
                            Fields = line.Split(new char[] { ',' });
                            emList.Add(
                                new EmployeeMaster
                                {
                                     EmployeeID = Fields[0].Replace("\"",""), // removed "" 
                                     CompanyName = Fields[1].Replace("\"", ""),
                                     ContactName = Fields[2].Replace("\"", ""),
                                     ContactTitle = Fields[3].Replace("\"", ""),
                                     EmployeeAddress = Fields[4].Replace("\"", ""),
                                     PostalCode = Fields[5].Replace("\"", ""),
                                });
                        }
 
                        // Update database data
                        using (MuDatabaseEntities dc = new MuDatabaseEntities())
                        {
                            foreach (var i in emList)
                            {
                                var v = dc.EmployeeMasters.Where(a => a.EmployeeID.Equals(i.EmployeeID)).FirstOrDefault();
                                if (v != null)
                                {
                                    v.EmployeeID = i.EmployeeID;
                                    v.CompanyName = i.CompanyName;
                                    v.ContactName = i.ContactName;
                                    v.ContactTitle = i.ContactTitle;
                                    v.EmployeeAddress = i.EmployeeAddress;
                                    v.PostalCode = i.PostalCode;
                                }
                                else
                                {
                                    dc.EmployeeMasters.Add(i);
                                }
                            }
 
                            dc.SaveChanges();
 
                            // populate updated data 
                            populateDatabaseData();
                            lblMessage.Text = "Successfully Done. Now upto data is following.....";
                        }
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }
            }
        

Step-8: Write code for export Data from database to csv.

Write below code into button click event for export Data from database to csv.

            protected void btnExportToCSV_Click(object sender, EventArgs e)
            {
                List<EmployeeMaster> emList = new List<EmployeeMaster>();
                using (MuDatabaseEntities dc = new MuDatabaseEntities())
                {
                    emList = dc.EmployeeMasters.ToList();
                }
 
                if (emList.Count > 0)
                {
                    string header = @"""Employee ID"",""Company Name"",""Contact Name"",""Contact Title"",""Employee Address"",""Postal Code""";
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine(header);
 
                    foreach (var i in emList)
                    {
                        sb.AppendLine(string.Join(",",
                            string.Format(@"""{0}""", i.EmployeeID),
                            string.Format(@"""{0}""", i.CompanyName),
                            string.Format(@"""{0}""", i.ContactName),
                            string.Format(@"""{0}""", i.ContactTitle),
                            string.Format(@"""{0}""", i.EmployeeAddress),
                            string.Format(@"""{0}""", i.PostalCode)));
                    }
 
                    // Download Here
 
                    HttpContext context = HttpContext.Current;
                    context.Response.Write(sb.ToString()); 
                    context.Response.ContentType = "text/csv";
                    context.Response.AddHeader("Content-Disposition", "attachment; filename=EmployeeData.csv");
                    context.Response.End();
                }
                else
                {
                    lblMessage.Text = "Data not Found!";
                }
            }
        

Step-9: Run Application.