Geeks With Blogs
Thanigainathan Siranjeevi Sharing my learning
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.
  1. DataSource path
  2. 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 | Back to top


Comments on this post: Asp.Net Excel File Import and Transfer to Sql Server

# re: Asp.Net Excel File Import and Transfer to Sql Server
Requesting Gravatar...
I understant whole code but its not working, actually Im using this in c# pls help me
Left by Amandeep Kaur on May 05, 2009 10:18 PM

# re: Asp.Net Excel File Import and Transfer to Sql Server
Requesting Gravatar...
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");
Left by Thanigainathan S on May 05, 2009 11:37 PM

# re: Asp.Net Excel File Import and Transfer to Sql Server
Requesting Gravatar...
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
Left by Program4trix on May 26, 2009 1:18 AM

# re: Asp.Net Excel File Import and Transfer to Sql Server
Requesting Gravatar...
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
Left by ZafarUllah on Aug 31, 2009 2:13 AM

# re: Asp.Net Excel File Import and Transfer to Sql Server
Requesting Gravatar...
its not working. Please provide proper & complete codes in c#
Left by Raman on Jul 24, 2010 2:28 AM

# re: Asp.Net Excel File Import and Transfer to Sql Server
Requesting Gravatar...
I have a "Code" column in the database that is defined as nvarchar(50). My excel file has alphanumeric data in this column. When I do the bulk copy, the code only copies the numeric data and puts NULL for the rows that have alphanumeric values. Do you have any ideas why that would happen?

Thanks

Vani
Left by Vani on Jul 26, 2010 3:25 PM

# re: Asp.Net Excel File Import and Transfer to Sql Server
Requesting Gravatar...
I want its vice versa like i want to download data from sql server to excel.Kindly help me asap

Thanks
Left by ns on Oct 26, 2010 1:24 AM

# re: Asp.Net Excel File Import and Transfer to Sql Server
Requesting Gravatar...
The Microsoft Office Access database engine could not find the object 'Detail'. Make sure the object exists and that you spell its name and the path name correctly.
Left by babi on Jan 26, 2011 11:08 PM

# re: Asp.Net Excel File Import and Transfer to Sql Server
Requesting Gravatar...
i found the c# version of bulkuploading excel sheet data to sql server
here
url:http://dotnetshot.com/topic.aspx?title=Uploading_an_Excel_file_to_SQL_through_aspnet_webform&id=34
Left by narendra on Aug 21, 2011 7:33 PM

# re: Asp.Net Excel File Import and Transfer to Sql Server
Requesting Gravatar...
Thanks!
Searched a number of forums before I found your comment about quotes around the Extended Properties in the connection string.

No more "Could not find installable ISAM." error.

Much appreciated
Left by Nick on Dec 20, 2011 5:49 AM

# re: Asp.Net Excel File Import and Transfer to Sql Server without using oledb connection
Requesting Gravatar...
please write this program
Left by vasundhara on Sep 17, 2012 6:45 PM

Your comment:
 (will show your gravatar)


Copyright © thanigai | Powered by: GeeksWithBlogs.net