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;
 
        }
2.In second step insert the data table value into data base by using Bulk copy process...
 
public void BulkImport(DataTable ExcelDatatable, string TABLENAME)
  {
 
        if (ExcelDatatable.Rows.Count > 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 <= 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
		
		
		/** 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;
 
    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 410
1 Sergey Alexandrovich Kryukov 329
2 Afzaal Ahmad Zeeshan 264
3 BillWoodruff 235
4 CPallini 195
0 OriginalGriff 5,560
1 DamithSL 4,476
2 Maciej Los 3,942
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,175


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 22 Aug 2012
Copyright © CodeProject, 1999-2014
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