Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET
how can i transfer data from microsoft Excel to datadase by using c# asp.net
Posted 22-Aug-12 1:54am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

1.Get data from from Excel file in to data table by using below function..
public static DataTable exceldata(string filePath)
        {
            DataTable dtexcel = new DataTable();
            bool hasHeaders = false;
            string HDR = hasHeaders ? "Yes" : "No";
            string strConn;
            if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
            else
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            //Looping Total Sheet of Xl File
            /*foreach (DataRow schemaRow in schemaTable.Rows)
            {
            }*/
            //Looping a first Sheet of Xl File
            DataRow schemaRow = schemaTable.Rows[0];
            string sheet = schemaRow["TABLE_NAME"].ToString();
            if (!sheet.EndsWith("_"))
            {
                string query = "SELECT  * FROM [" + sheet + "]";
                OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
                dtexcel.Locale = CultureInfo.CurrentCulture;
                daexcel.Fill(dtexcel);
            }
            conn.Close();
            return dtexcel;
 
<pre>
    }

2.In second step insert the data table value into data base by using Bulk copy process...
 
public void BulkImport(DataTable ExcelDatatable, string TABLENAME)
  {
 
<pre>
    if (ExcelDatatable.Rows.Count &gt; 1)
    {
        try
        {
            if (con.State == ConnectionState.Closed)
                con.Open();
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy
            (
            con,
            SqlBulkCopyOptions.TableLock |
            SqlBulkCopyOptions.FireTriggers |
            SqlBulkCopyOptions.UseInternalTransaction,
            null
            ))
            {
 
                bulkCopy.DestinationTableName = TABLENAME;
 
                for (int i = 0; i &lt;= ExcelDatatable.Columns.Count - 1; i++)
                {
                    string colname = ExcelDatatable.Columns[i].ColumnName.ToString();
 
                        bulkCopy.ColumnMappings.Add(ExcelDatatable.Columns[i].ColumnName.ToString(), colname);
 
                }
 
                bulkCopy.WriteToServer(ExcelDatatable);
            }
            con.Close();
        }
        catch (Exception ee)
        {
        }
    }
}

}
  Permalink  
v2
Comments
santosh_k at 23-Aug-12 0:22am
   
gud answer...
Kamalakanta Nayak 2 at 23-Aug-12 3:47am
   
Thanks to all
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

This link will show you how to do this

Import Data from Excel to SQL Server[^]
  Permalink  
Comments
srinath.pothineni at 22-Aug-12 7:10am
   
thanks rahul,
i want to generate table dynamically without knowing the column names in the excel sheet.can u help me.......
Rahul Rajat Singh at 22-Aug-12 7:14am
   
just posted some code for you. it is working fine. check that out.
Nikhil Vaidya mis at 22-Aug-12 7:57am
   
Thanks Dear But it can work with SQL server 2005
Rahul Rajat Singh at 22-Aug-12 8:09am
   
Yes it will. see my other solution for the same answer. it is working with sqlserver 2005 too.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Try this...

Use Excel provider to get data from excel into Dataset.
Now use the Dataset to post data to sql server.

Connection string for excel 7
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

Hope this helps.
cheers
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

try creating a SQL Cursor for this task, sample below...
 
BEGIN TRY
BEGIN TRANSACTION
 
DECLARE @Column1 varchar(MAX)
DECLARE @Column2 varchar(MAX)
 
DECLARE db_cursor CURSOR FOR  
 
SELECT 
 Convert(CHAR,ltrim(rtrim(Column1 )),1000) As 'MyColumn1'
,Convert(CHAR,First_Name,1000) As 'MyColumn2'
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\PUBLIC\MyFiles\Demo.xls', [Sheet1$]) 
--where ltrim(rtrim(Column1)) = ltrim(rtrim('A00-1'))

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @SystemGenerateID,@First_Name,@Last_Name,@contact_email_address,@organization_name
WHILE @@FETCH_STATUS = 0
BEGIN
 
<pre>
    /** ANY SQL STATEMENT **/ 
 
    PRINT 'UPDATED - ' + ltrim(rtrim(@Column1))
 
    FETCH NEXT FROM db_cursor INTO @Column1,@Column2
END

CLOSE db_cursor

DEALLOCATE db_cursor

END TRY
BEGIN CATCH
CLOSE db_cursor

DEALLOCATE db_cursor
ROLLBACK TRANSACTION
 
    PRINT 'ERROR: ' + @Column1
    SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;                                    

END CATCH

COMMIT TRANSACTION
PRINT 'SUCCESS'
GO
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

NOTE: posting a separate solution to avoid clutter in original.

Here is a small code i just wrote to show how this can be done. it is working perfectly.
 
public partial class _Default : System.Web.UI.Page 
{
    OleDbConnection con = null;
 
<pre>
protected void Page_Load(object sender, EventArgs e)
{
    string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\testExcel.xls;Extended Properties='Excel 8.0;HDR=YES;'";
 
    con = new OleDbConnection(connectionString);
}
 
protected void Button1_Click(object sender, EventArgs e)
{
    try
    {
        con.Open();
 
        Label1.Text = con.State.ToString();
 
        if (con.State == ConnectionState.Open)
        {
            OleDbCommand cmd = con.CreateCommand();
            cmd.CommandText = "Select * from [Sheet1$]";
            cmd.CommandType = CommandType.Text;
 
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataTable table = new DataTable();
 
            da.Fill(table);
 
            //I am showing here on grid you can instead push them all in DB one by one 
            GridView1.DataSource = table;
            GridView1.DataBind();
 
        }
    }
    finally
    {
        con.Close();
    }
}

}
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

Perhaps this link would help you.
SQL Import Data from Excel[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 8

frend,

dotnet c#4 introduced dynamic api to ummanaged api .

check in google.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 475
1 Sergey Alexandrovich Kryukov 405
2 Maciej Los 290
3 ProgramFOX 265
4 CHill60 200
0 OriginalGriff 490
1 Sergey Alexandrovich Kryukov 405
2 Maciej Los 285
3 ProgramFOX 265
4 Peter Leow 210


Advertise | Privacy | Mobile
Web01 | 2.8.150331.1 | Last Updated 22 Aug 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100