Asp.Net Excel File Import and Transfer to Sql Server

Excel file import is one of the important repeatedly used feature in all the web applications and Windows application. The thing is Excel is very useful for ordinary people other than technical people. One doesn't need to know about queries when using Excel. Such a user friendly tool for the Business users.

So the scenario here is uploading them in Web Application , transferring the contents to a database like sql server , oracle or DB2. I am going to discusss about the Sql Server thing. There is feature available in Sql Server called DTS (upto 2000 version) and SSIS (From 2005 version). This can be done only by technical developers and DBA's.Developing the same in windows Application not seems to be challenging task . But when we tried to convert the same to Web Application it showed its own problem's. We did a lot of googling with different search terms and then found the solution.

Our application had some fileupload control through which the excel file is loaded into the server. Then clicking a button will transfer the Excel file into the Database. Searching the net we can find two connection strings which can be used to connect to the Excel file as a data source.

 <connectionStrings>

            <addname="xls"connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Test.xls;Extended Properties=Excel 8.0"/>

            <addname="xlsx"connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Test.xlsx;Extended Properties=Excel 12.0"/>

</connectionStrings>

This is what i can find which seemed to be not working when i tried in my code. Because there a lot which need's to be fixed in this when using in the Web Application.

DataSource path
Extended Properties

The datasource cannot contain the file name directly . We need to save the Excel file in the application path through file upload control. So we have to use the "Server.Mappath("Test.Xls")" . This brings up the actual file that we need to work with. The extended properties needs to be separated with a space. Then the content of this property needs to be present in a special format as "Extended Properties=""Excel 12.0""". Otherwise it will throw Jet Oleddb Error. So finally the connectionstring will be as follows.

 <connectionStrings>

            <addname="xls"connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ Server.MapPath(Test.xls) + ";Extended Properties=""Excel 8.0"""/>

            <addname="xlsx"connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ Server.MapPath(Test.xlsx) + ";Extended Properties=""Excel 12.0"""/>

</connectionStrings>

Microsoft.Jet.OLEDB.4.0 -  For Reading the Excel 97-2003 format
Microsoft.ACE.OLEDB.12.0-  For reading the Excel 2007 format
 We can switch between the file extensions when we need them as per the file extensions as Xls or Xlsx.

So a Simple code for fetching the data from Excel will look like as follows.


        Dim strconn As String
        strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("Stest.xlsx") + ";Extended Properties=""Excel 12.0"""
        Dim olConn As New OleDbConnection(strconn)
        Dim orr As OleDbDataReader
 
        olConn.Open()
        Dim od As New OleDbCommand("SELECT * FROM [Sheet1$]", olConn)
        orr = od.ExecuteReader()

The Excel file Name will be the name of the Table in Database. Meaning if the table is not there we have to create . This is where we got into problem .Because there are no methods in ADO.Net to create a table or Database. Well we can use the SQL SMO or DMO objects but that needs to be done with the help of some SQLSMO or DMO object references which we don't want. Simply we created a String to create the table if its not there in DB. The columns can be crated through looping the above datareader .

SqlBulkCopy:

This is one of the ADO.Net 2.0 feature to transfer the data to a table very fastly. We can use the datareader which we got above for doing this . The msdn link for SqlBulkCopy is . The sample code for doing this is given below and its very very easy to do.

Dim sqCopy As New SqlBulkCopy(sConn)

        sqCopy.DestinationTableName = "Test"

        sqCopy.WriteToServer(orr)

sConn points to Sql Server connection. DestinationtableName is the name of the table to which the data has to be transferred. WriteToServer actually transfer's the data to the Sql Server by reading row by row of datareader. Incase if the Columns are not matching between the Excel and Sql Server then we can use the ColumnMappings of SqlBulkCopy. You can see the following sample .

sqCopy.ColumnMappings.Add("ID", "EmpId")

sqCopy.ColumnMappings.Add("Name", "EName")

Thats it. The main davnatge of using SqlBulkCopy is its speed and performance over the other 
methods.So this is one of the cool features of the ADO.Net which we can use effectively and 
with that I am finishing this post Sharing with you all very cool features of Excel config 
settings and SqlBulkCopy.Please let me know your views about this.

posted on Monday, April 27, 2009 4:23 AM

Feedback

# re: Asp.Net Excel File Import and Transfer to Sql Server 5/5/2009 10:18 PM Amandeep Kaur

I understant whole code but its not working, actually Im using this in c# pls help me

# re: Asp.Net Excel File Import and Transfer to Sql Server 5/5/2009 11:37 PM Thanigainathan S

Hi,

please dont worry. I am giving the code in C# below

string strconn = null;
strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("Stest.xlsx") + ";Extended Properties=\"Excel 12.0\"";
OleDbConnection olConn = new OleDbConnection(strconn);
OleDbDataReader orr = default(OleDbDataReader);
olConn.Open();
OleDbCommand od = new OleDbCommand("SELECT * FROM [Sheet1$]", olConn);
orr = od.ExecuteReader();

SqlBulkCopy
-------------

SqlBulkCopy sqCopy = new SqlBulkCopy(sConn);
sqCopy.DestinationTableName = "Test";
sqCopy.WriteToServer(orr);

sqCopy.ColumnMappings.Add("ID", "EmpId");
sqCopy.ColumnMappings.Add("Name", "EName");

# re: Asp.Net Excel File Import and Transfer to Sql Server 5/26/2009 1:18 AM Program4trix

Idid all you stated here but got an error saying: "No value given for one or more required parameres"

in the line:

orr = od.ExecuteReader()

Im using vb, please help

# re: Asp.Net Excel File Import and Transfer to Sql Server 8/31/2009 2:13 AM ZafarUllah

can we validate data before bulkcopy operation start or during import, or we need toverify data in a seprate flow and shold give only validated data to bulk operation.

tx
Zafar Ullah
http://barchitect.blogspot.com

Thanigainathan Siranjeevi

News

Twitter

Archives

ASP.Net

EntityFramework

Linq

Microsoft Free Ebook

Silverlight

Feedback