Export and import data from the Database to Excelcsvtext files using ASP.NET 2.0

In this article, we will explore how to export data from a database table to Excel, csv and Text formats. And how to import data to database tables from Excel, csv and Text formats.

 

Let us start of by first creating a sample database and adding a table to it. We will call the database ‘ExportImportDB’ and the table will be called ‘Address’.

 

Also create two pages ‘ExportData.aspx’ For Data export. ‘ImportData.aspx’ for Data import.

I created a Class ConnectionDB.cs in App_Code Folder for manipulating database operation. Connection string is declared in web.config file. Create a folder ‘FileTemp’ in the root directory for temporary storage of files.

 

Step 1: Create Database And Table for Data Export and Import

 

Let us start off by first creating a sample database and adding a table to it. We will call the database ‘ExportImportDB’ and the table will be called ‘Address’. You can create your own Table for manipulating data. This table contains 6 columns. Run the following script in your SQL 2005 Query window (or server explorer) to construct the database and the table.

 

 

CREATE DATABASE ExportImportDB

 

Create Table Address

(

AddressId int primary key identity,

ContactName  varchar (50),

City varchar (50),

State varchar (50),

Country varchar (50),

Zip varchar (10)

)

 

Step 2: Create Database Connection Class

 

Create a new asp.net website. Add connection string to web.config file. My connection string is

 

<configuration>

      <appSettings/>

      <connectionStrings>

            <add name="DBConnectionString" connectionString="Data Source=.;Initial Catalog=ExportImportDB;Persist Security Info=True;User ID=sa;Password=" providerName="System.Data.SqlClient"/>

      </connectionStrings>

</configuration>

 

Create a class ConnectionDB and add it to App_code file. The ConnectionDB Class is used for database operation this class contain 2 methods GetRecords(string sql) and UpdateRecords(DataTable dtUpdatedRecords).

GetRecords(string sql) is used for getting records from database table. And UpdateRecords(DataTable dtUpdatedRecords) is used for updating the imported file records to database table.

 

using System;

using System.Data;

using System.Collections;

using System.Data.SqlClient;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

 

/// <summary>

/// This class is used for connecting to database

/// </summary>

public  class ConnectionDB

{

    //Get the connection string that stored in the web.config file.

    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);

 

    private void ConnectionOpen()

    {

         if(con.State!=ConnectionState.Open)

              con.Open();

    }

    private void ConnectionClose()

    {

        if (con.State != ConnectionState.Closed)

            con.Close();

    }

    /// <summary>

    /// This methods return datatable with given sql query.

    /// </summary>

    /// <param name="sql"></param>

    /// <returns></returns>

    public DataTable GetRecords(string sql)

    {

        SqlCommand cmd = new SqlCommand(sql, con);

 

        //Create the dataAdapter for getting records

        SqlDataAdapter adap = new SqlDataAdapter(sql, con);

        DataSet ds = new DataSet();

        ConnectionOpen();

        adap.Fill(ds, "TABLE_TEMP");

        ConnectionClose();

        return ds.Tables[0];

    }

    /// <summary>

    /// This method will update the given datatable records to server.

    /// </summary>

    /// <param name="dtUpdatedRecords"></param>

    /// <returns></returns>

    public  DataTable UpdateRecords(DataTable dtUpdatedRecords)

    {

        SqlDataAdapter adapter = new SqlDataAdapter();

        //The command text may varry with the table name.Here i am using Address table for my dummy operations.

        SqlCommand CMD = new SqlCommand("SELECT * FROM Address", con);

        adapter.SelectCommand = CMD;

        SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

        ConnectionOpen();

        DataTable dtOprjAssc = new DataTable();

        adapter.Fill(dtOprjAssc);

 

        //For each records in the new table add it to the database.

        for (int i = 0; i < dtUpdatedRecords.Rows.Count; i++)

        {

            dtOprjAssc.ImportRow(dtUpdatedRecords.Rows[i]);

        }

        //Finally update the dataAdapter

        adapter.Update(dtOprjAssc);

        dtOprjAssc.AcceptChanges();

 

        //Close the connection

        ConnectionClose();

        //Returns the updated record set

        return dtOprjAssc;

 

    }

 

 

}

 

Step 3: Export Data

 

Add new page ‘ExportData.aspx’ Drag drop 3 buttons and a GridView control.  GridView is used for displaying the records. Rename the text of buttons to Export to Excel, Export to csv, Export to Text. Double click each buttons to generate its click events.

 

In the Export to Excel button click add  the following code to create excel file.

 

protected void btnExcelConvert_Click(object sender, EventArgs e)

    {

        //export to excel

        string fileName = System.Guid.NewGuid().ToString().Replace("-", "") + ".xls";

        //Call the function for export the datat records to excel file.

        ExportToExcel(fileName, GetDataTable());

    }

 

In the above click event I called a function ExportToExcel(fileName, GetDataTable())  ,This function generate Excel file and transmit this file to browser.The GetDataTable() function return the data records from the database datable.

 

/// <summary>

    /// This function create excel file and transmit it to the browser as attachment

    /// </summary>

    public void ExportToExcel(string strFileName, DataTable dt)

    {

        Response.ClearContent();

        Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);

        Response.ContentType = "application/excel";

        System.IO.StringWriter sw = new System.IO.StringWriter();

        HtmlTextWriter htw = new HtmlTextWriter(sw);

        DataGrid dg = new DataGrid();

        dg.DataSource = dt;

        dg.DataBind();

        dg.RenderControl(htw);

        Response.Write(sw.ToString());

        Response.End();

    }

    /// <summary>

    /// For getting records from database

    /// </summary>

    private DataTable GetDataTable()

    {

        ConnectionDB objConnectionOP = new ConnectionDB();

        DataTable dtAddress = objConnectionOP.GetRecords("select * from Address");

        return dtAddress;

    }

 

In the Export to csv button click add  the following code to create csv file. And transmit to browser.

 

protected void btnCsvConvert_Click1(object sender, EventArgs e)

    {

        //export to csv

        string fileName = System.Guid.NewGuid().ToString().Replace("-", "") + ".csv";

        //Call the function for export the datat records to csv file.

        GenerateFile(GetDataTable(), Server.MapPath("FileTemp"), fileName);

        FileInfo inf = new FileInfo(Server.MapPath("FileTemp/" + fileName));

        if (inf.Exists)

        {

            //Transmit the file as attachment

            Response.ClearContent();

            Response.AddHeader("Content-Disposition", "attachment; filename=" + inf.Name);

            Response.AddHeader("Content-Length", inf.Length.ToString());

            Response.ContentType = "application/vnd.csv";

            Response.TransmitFile(inf.FullName);

            Response.End();

        }

    }

In the Export to txt button click add  the following code to create txt file. And transmit to browser.

 

protected void btnTextConvert_Click1(object sender, EventArgs e)

    {

        //export to text

        string fileName = System.Guid.NewGuid().ToString().Replace("-", "") + ".txt";

        //Call the function for export the datat records to txt file.

        GenerateFile(GetDataTable(), Server.MapPath("FileTemp"), fileName);

        FileInfo inf = new FileInfo(Server.MapPath("FileTemp/" + fileName));

        if (inf.Exists)

        {

            //Transmit the file as attachment

            Response.ClearContent();

            Response.AddHeader("Content-Disposition", "attachment; filename=" + inf.Name);

            Response.AddHeader("Content-Length", inf.Length.ToString());

            Response.ContentType = "application/vnd.text";

            Response.TransmitFile(inf.FullName);

            Response.End();

 

        }

    }

 

 

Step 4: Import Data from File to Database table

 

Add new page ‘ImportData.aspx’ and copy paste the below code.

 

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ImportData.aspx.cs" Inherits="ImportData" %>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>Import data</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <div>

            <div style="text-align: left">

                <strong>

                Import Data To Server<br />

                    <br />

                </strong>

                <table border="0" cellpadding="5" cellspacing="0">

                    <tr>

                        <td style="width: 100px">

                            Select File Type</td>

                        <td style="width: 100px">

                            <asp:DropDownList ID="ddlType" runat="server" Width="160px">

                                <asp:ListItem Value=".txt">Text</asp:ListItem>

                                <asp:ListItem Value=".csv">CSV</asp:ListItem>

                                <asp:ListItem Value=".xls">XLS</asp:ListItem>

                            </asp:DropDownList></td>

                        <td style="width: 100px">

                        </td>

                    </tr>

                    <tr>

                        <td style="width: 100px">

                            Brows File</td>

                        <td style="width: 100px">

                            <asp:FileUpload ID="FileUpload1" runat="server" /></td>

                        <td style="width: 100px">

                        </td>

                    </tr>

                    <tr>

                        <td style="width: 100px; height: 34px">

                        </td>

                        <td style="width: 100px; height: 34px">

                            <asp:Button ID="btnUpload" runat="server" OnClick="btnUpload_Click" Text="Upload" /></td>

                        <td style="width: 100px; height: 34px">

                        </td>

                    </tr>

                </table>

            </div>

        </div>

   

    </div>

        <asp:Label ID="lblError" runat="server" ForeColor="Red"></asp:Label>

        <br />

        <br />

        <asp:GridView ID="MyGrid" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">

            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />

            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />

        </asp:GridView>

    </form>

</body>

</html>

 

 

In this page users can upload csv, text or Excel file, and the records in these files are updated to server. The button Upload handles this operation. The code behind file ImportData.aspx.cs does all the operation.

 

 

TextToDataTable(string file, bool isRowOneHeader) is used for converting a text file data to DataTable of records.

 

public static DataTable TextToDataTable(string file, bool isRowOneHeader)

    {

        DataTable csvDataTable = new DataTable();

        //No try/catch - add these in yourselfs or let exception happen

        String[] csvData = File.ReadAllLines(file);

 

        //If no data in file ‘manually’ throw an exception

        if (csvData.Length == 0)

        {

            throw new Exception("Text File Appears to be Empty");

        }

        String[] headings = csvData[0].Split(',');

        int index = 0; //will be zero or one depending on isRowOneHeader

 

        if (isRowOneHeader) //if first record lists headers

        {

            index = 1; //so we won’t take headings as data

            //for each heading

            for (int i = 0; i < headings.Length; i++)

            {

                //replace spaces with underscores for column names

                headings[i] = headings[i].Replace(" ", "_");

 

                //add a column for each heading

                csvDataTable.Columns.Add(headings[i], typeof(string));

            }

        }

        else //if no headers just go for col1, col2 etc.

        {

            for (int i = 0; i < headings.Length; i++)

            {

                //create arbitary column names

                csvDataTable.Columns.Add("col" + (i + 1).ToString(), typeof(string));

            }

        }

        //populate the DataTable

        for (int i = index; i < csvData.Length; i++)

        {

            //create new rows

            DataRow row = csvDataTable.NewRow();

 

            for (int j = 0; j < headings.Length; j++)

            {

                //fill them

                row[j] = csvData[i].Split(',')[j];

            }

 

            //add rows to over DataTable

            csvDataTable.Rows.Add(row);

        }

        //return the Text DataTable

        return csvDataTable;

    }

 

The csvToDataTable(string file, bool isRowOneHeader) is used for converting csv file data to DataTable of records.  

 

public static DataTable csvToDataTable(string file, bool isRowOneHeader)

    {

        DataTable csvDataTable = new DataTable();

        //No try/catch - add these in yourselfs or let exception happen

        String[] csvData = File.ReadAllLines(file);

        //If no data in file ‘manually’ throw an exception

        if (csvData.Length == 0)

        {

            throw new Exception("CSV File Appears to be Empty");

        }

        String[] headings = csvData[0].Split(',');

        int index = 0; //will be zero or one depending on isRowOneHeader

        if (isRowOneHeader) //if first record lists headers

        {

            index = 1; //so we won’t take headings as data

            //for each heading

            for (int i = 0; i < headings.Length; i++)

            {

                //replace spaces with underscores for column names

                headings[i] = headings[i].Replace(" ", "_");

                //add a column for each heading

                csvDataTable.Columns.Add(headings[i], typeof(string));

            }

        }

        else //if no headers just go for col1, col2 etc.

        {

            for (int i = 0; i < headings.Length; i++)

            {

                //create arbitary column names

                csvDataTable.Columns.Add("col" + (i + 1).ToString(), typeof(string));

            }

        }

        //populate the DataTable

        for (int i = index; i < csvData.Length; i++)

        {

            //create new rows

            DataRow row = csvDataTable.NewRow();

 

            for (int j = 0; j < headings.Length; j++)

            {

                //fill them

                row[j] = csvData[i].Split(',')[j];

            }

            //add rows to over DataTable

            csvDataTable.Rows.Add(row);

        }

        //return the CSV DataTable

        return csvDataTable;

    }

 

The LoadExcellData(string _filepath) is used for converting Excel file data to DataTable. This method returns a DataTable that contains records that are in the excel file.

 

    private OleDbConnection _myConn;

    private string[] _worksheetnames;

    private string _connectionstring;

    private string _error;

    private DataSet _ds = new DataSet();

 

private DataTable LoadExcellData(string _filepath)

    {

        this._connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;" +

          "Data Source=" + _filepath + ";Extended Properties=HTML Import";

        _worksheetnames = GetWorkSheetNames();

        try

        {

            // If the worksheet name exists...

            for (int i = 0; i < _worksheetnames.Length; i++)

            {

                // Open the connection to the Excel document, and select the data from the first worksheet

                _myConn = new OleDbConnection(_connectionstring);

                _myConn.Open();

                OleDbDataAdapter _da = new OleDbDataAdapter(@"SELECT * FROM [" + _worksheetnames[i] + "]", _myConn);

                _da.Fill(_ds, "Table" + i);

            }

        }

        catch (Exception e)

        {

            _error += e.Message + "<br /><br />";

        }

        finally

        {

            _myConn.Close();

        }

        if (_ds.Tables.Count > 0) return _ds.Tables[0];

        else return new DataTable();

    }

 

The GetWorkSheetNames() is used for getting the Table name used in the Excel Sheet.

 

 

private string[] GetWorkSheetNames()

    {

        _myConn = new OleDbConnection(_connectionstring);

        try

        {

            _myConn.Open();

            // Get all of the Table names from the Excel workbook

            DataTable dt = _myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            _worksheetnames = new string[dt.Rows.Count];

            //Add the Table name to the string array.

            for (int i = 0; i < dt.Rows.Count; i++)

            {

                _worksheetnames[i] = (string)dt.Rows[i]["TABLE_NAME"];

            }

        }

        catch (Exception e)

        {

            _error += e.Message + "<br /><br />";

        }

        finally

        {

            _myConn.Close();

        }

        return _worksheetnames;

    }

 

The user can upload csv, text or excel file according to the selection of dropdown list. The upload button event handles the operation. It redirects to different functions according to the type of file. Finally the updated data is displayed in the grid.

 

//This event handle the importing of data.

    protected void btnUpload_Click(object sender, EventArgs e)

    {

        lblError.Text = "";

        string ExcelFilename = FileUpload1.FileName;

        string _path = "";

        if (FileUpload1.HasFile)

        {

            try

            {

 

                if (Path.GetExtension(FileUpload1.PostedFile.FileName) != ddlType.SelectedValue)

                {

                    lblError.Text ="Invalid file format";

                    return;

                }

                _path = Server.MapPath("FileTemp/") + FileUpload1.FileName;

                //save this file

                //Here i am using a temporary folder for saving uploade file.

                FileUpload1.PostedFile.SaveAs(_path);

            }

            catch (Exception ex)

            {

                lblError.Text = ex.Message;

                return;

            }

        }

        else

        {

            lblError.Text = "Please upload file.";

            return;

        }

 

        if (ddlType.SelectedValue == ".txt")

        {

            try

            {

               

                //Convert exported data to datatable.

                DataTable textDataTable = TextToDataTable(_path, true);

                //To update imported data to server.

                DataTable _updatedTabe = (new ConnectionDB()).UpdateRecords(textDataTable);

                //Display the updated records.

                MyGrid.DataSource = _updatedTabe;

                MyGrid.DataBind();

            }

            catch (Exception ex)

            {

                lblError.Text = ex.Message;

                return;

            }

 

        }

        else if (ddlType.SelectedValue == ".csv")

        {

            try

            {

                //Convert exported data to datatable.

                DataTable csvDataTable = csvToDataTable(_path, true);

                //To update imported data to server.

                DataTable _updatedTabe = (new ConnectionDB()).UpdateRecords(csvDataTable);

                //Display the updated records.

                MyGrid.DataSource = _updatedTabe;

                MyGrid.DataBind();

            }

            catch (Exception ex)

            {

                lblError.Text = ex.Message;

                return;

            }

        }

        else

        {

            //To import from excel file

            try

            {

                //Convert exported data to datatable.

                DataTable dtUploadedRecords = LoadExcellData(_path);

                //To update imported data to server

                DataTable _updatedTabe = (new ConnectionDB()).UpdateRecords(dtUploadedRecords);

                //Display the updated records.

                MyGrid.DataSource = _updatedTabe;

                MyGrid.DataBind();

            }

            catch (Exception ex)

            {

                lblError.Text = ex.Message;

                return;

            }

        }

    }

Tags:

View Live Demo Download Source

Back to Main Top of Page

Search Resources

Subscribe Free Resource