Click here to Skip to main content
12,399,799 members (48,515 online)
Rate this:
 
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 0: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 23-Aug-12 0:22am
   
gud answer...
Kamalakanta Nayak 2 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 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 22-Aug-12 7:14am
   
just posted some code for you. it is working fine. check that out.
Nikhil Vaidya mis 22-Aug-12 7:57am
   
Thanks Dear But it can work with SQL server 2005
Rahul Rajat Singh 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 22 Aug 2012
Copyright © CodeProject, 1999-2016
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