ASP.NET - how to parse data from a Excel file and save to database.

Asked By aman on 01-Oct-11 01:20 AM
hi all,

i have created a page where user can upload their Excel file and want to parse that file data and save in to sql server data base .......please help me how cani achieve the above in simpler way.

please give me some example so that i can make use of it....

thanks and regards
Aman Khan
Reena Jain replied to aman on 01-Oct-11 01:38 AM
Hi,

try this and let me know its working for me perfectly

using System.Data;
using System.Data.OleDb;
  
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0");
OleDbDataAdapter da = new OleDbDataAdapter("select * from MyObject", con);
DataTable dt = new DataTable();
da.Fill(dt);
  
/* Read the DataTable Item and Insert it One by One into Database using Insert Query */
 
SqlConnection con = new SqlConnection("ConnectionStr");
con.Open();
foreach(DataRow r in dt.Rows)
{
  /* Inset Logic */
  SqlCommand comm = new SqlCommand("insert into table1(id,col1) values(@id,@col1)", con);
  comm.Parameters.AddWithValue("@id", r[0].ToString());
  comm.Parameters.AddWithValue("@col1", r.Cells[1].ToString());
  comm.ExecuteNonQuery();
  con.Close();
  }
}
Rohan Dave replied to aman on 01-Oct-11 01:44 AM
try by using SqlBulkCopy...

 protected void ProcessExcelWithSqlBulkCopy()
 {
        //Create connection string to Excel work book
        string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Data.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

      //SQL Server Connection string..
      strSQLConnection = "DataSource=yourdbservername;Initial Catalog=yourdbname; UserName=sa; Password=sa; Integrated Security=true";

        //Create Connection to Excel work book
        OleDbConnection objExcelCon=  new OleDbConnection(excelConnectionString);

        //Create OleDbCommand to fetch data from Excel
        OleDbCommand objCmd = new OleDbCommand ("Select [UserId],[UserName] from [Data$]",        excelConnection);
        objExcelCon.Open();
        OleDbDataReader objDR;
        objDR= objCmd .ExecuteReader();

        // Create object for SqlBulkCopy
        SqlBulkCopy objSqlBulk = new SqlBulkCopy(strSQLConnection);
        objSqlBulk .DestinationTableName = "your destinataion table name";
       
        // Here you can do the column mapping of excel and SQL table
        objSqlBulk .ColumnMappings.Add("UserId", "UserId");
        objSqlBulk .ColumnMappings.Add("UserName", "UserName");
        objSqlBulk .WriteToServer(objDR);
      }

dipa ahuja replied to aman on 01-Oct-11 01:45 AM
Untitled document
private void button2_Click(object sender, EventArgs e)
{
   string ExcelConstr = @"Provider=Microsoft.ACE.OLEDB.12.0";
   ExcelConstr += "Data Source=d:\book1.xls;Extended Properties=Excel 12.0";
 
   string SqlConstr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;";
   SqlConstr += "Integrated Security=True;User Instance=True";
 
   using (OleDbConnection con = new OleDbConnection(ExcelConstr))
   {
     con.Open();
     OleDbCommand com = new OleDbCommand("Select * from [Sheet1$]", con);
     OleDbDataReader dr = com.ExecuteReader();
     using (SqlConnection sqlcon = new SqlConnection(SqlConstr))
     {
       sqlcon.Open();
       using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
       {
         bulkCopy.DestinationTableName = "Table1";
         bulkCopy.ColumnMappings.Add("srno", "srno");
         bulkCopy.ColumnMappings.Add("amount", "amount");
         bulkCopy.WriteToServer(dr);
       }
     }
     dr.Close();
     dr.Dispose();
   }
   MessageBox.Show("successfully imported!");
   //display the imported data in the datagrid
   SqlDataAdapter da = new SqlDataAdapter("select * from Table1", SqlConstr);
   DataTable dt = new DataTable();
 
   da.Fill(dt);
 
   dataGridViewX1.DataSource = dt;
}
Anoop S replied to aman on 01-Oct-11 01:49 AM
There's a few different ways of doing this depending on what your circumstances are.

Is there a folder where Excel files saved and then must be uploaded? Do your users select the file to be uploaded? You could use SQL server and a DTS package to upload the data from the Excel file to the table. What's your scenario?

Please look here:

http://support.microsoft.com/kb/319951/EN-US/

or here

first you have to store excel data in datatable,
you can store data in datatable as

string constring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MySpreadsheet.XLS;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
datatable dt=new datatable();
Oledbconnection con=new oledbconnection(constring);
oledbdataadapter da=new oledbdataadapter("select * from sheet1",con)
da.fill(dt);

now you can store datatable data into sql using for/while loop.

http://www.databasejournal.com/features/mssql/article.php/3331881

or this

Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
Just a little bit of code transfers the data from the Excel Spreadsheet into the SQL Server Database Table:

// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

// Create Connection to Excel Workbook
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("Select ID,Data FROM [Data$]", connection);

connection.Open();

// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "Sqltablename";
bulkCopy.WriteToServer(dr);
}
}
}

Chintan Vaghela replied to aman on 01-Oct-11 02:14 AM
check out following function,

public DataTable csvToDataTable(string file)
    {
      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 = 1; //will be zero or one depending on isRowOneHeader

      //for each heading
      for (int i = 0; i < headings.Length; i++)
      {
        //replace spaces with underscores for column names
        headings[i] = headings[i].Replace(" ", "_");
        headings[i] = headings[i].Replace(".", "#");
        //add a column for each heading
        if(csvDataTable.Columns.Contains(headings[i]))
        {
          headings[i] += "1";
        }
        csvDataTable.Columns.Add(headings[i], 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;
    }

After then this DataTable Record Save into DB Table.

Hope this helps !
Devil Scorpio replied to aman on 01-Oct-11 03:27 AM
Hi aman,

See http://msdn.microsoft.com/en-us/library/bb448854.aspx from MS...

It is a free library that does NOT require office to be installed... you can read + write Office files including Excel and Word (Version 2007 and up...).

IF you need more features (like rendering to PDF etc.) then you would need some 3rd-party library...

For the SQL Server part you can ADO.NET / Entity Framework or whatever technology you want