excel to sql server 2007 data transfer

Question

1.00/5 (1 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

srinath.pothineni

Add a Solution

7 solutions

Add a Solution

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

Kamalkant(kk) · Answer 1 · 2012-08-22T01:16:00

1.Get data from from Excel file in to data table by using below function..

C#

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...

C#

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)
            {
            }
        }
    }
}

Rahul Rajat Singh · Answer 2 · 2012-08-22T01:00:00

Solution 1

This link will show you how to do this

Import Data from Excel to SQL Server[^]

Posted 22-Aug-12 1:00am

Rahul Rajat Singh

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.

Sandip.Nascar · Answer 3 · 2012-08-22T01:01:00

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

Posted 22-Aug-12 1:01am

Sandip.Nascar

Updated 22-Aug-12 11:30am

v2

PJ003 · Answer 4 · 2012-08-22T01:09:00

try creating a SQL Cursor for this task, sample below...

SQL

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

Rahul Rajat Singh · Answer 5 · 2012-08-22T01:13:00

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.

C#

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();
        }
    }
}

pramod.hegde · Answer 6 · 2012-08-22T01:25:00

Solution 6

Perhaps this link would help you.
SQL Import Data from Excel[^]

Posted 22-Aug-12 1:25am

pramod.hegde

jameschowdaree · Answer 7 · 2012-08-22T02:28:00

Solution 8

frend,

dotnet c#4 introduced dynamic api to ummanaged api .

check in google.

Posted 22-Aug-12 2:28am

jameschowdaree

excel to sql server 2007 data transfer

7 solutions

Solution 5

Solution 1

Solution 2

Solution 3

Solution 4

Solution 6

Solution 8

Add your solution here

Preview 0

excel to sql server 2007 data transfer

7 solutions

Solution 5

Solution 1

Solution 2

Solution 3

Solution 4

Solution 6

Solution 8

Add your solution here

Preview 0

Existing Members

...or Join us