Nannette Thacker ShiningStar.net

ASP.net Web Application Development

Sponsors

News

See all Blog Posts by Nannette.

Nannette Thacker, consultant and owner of Shining Star Services LLC, specializes in development of custom dynamic database driven web applications utilizing ASP.net technologies. Nannette has been developing ASP sites since 1997. Nannette has written numerous articles on web development techniques and tutorials.

Nannette is the owner and developer of ChristianSinglesDating.com.

 Subscribe in a reader





View Nannette  Thacker's profile on LinkedIn

Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters

By Nannette Thacker

In this tutorial, learn how to import data from an Excel Spreadsheet to a Database by creating a tiered application architecture using Visual Studio's Typed DataSets and TableAdapters. The application consists of an ASP.NET page that enables a user to upload an Excel spreadsheet, view the data in the spreadsheet, and import the data from the spreadsheet into the application's database. A Zip file with the complete source code, Excel Spreadsheet, and SQL Server Database is available for download.

The article series hosted on 4guysfromrolla.com begins with Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters: Building the Database.

Download the Application in ZIP Format

May your dreams be in ASP.NET!

Nannette Thacker

Comments

Todd said:

Hi Nannette,

Thanks for the article.  I see that you have entered:

Extended Properties=Excel 8.0

in the connection string.  Does this mean that importing other versions of MS Excel will cause issues?

Thanks for the feedback.

Todd

# February 29, 2008 6:22 PM

Matt said:

Any ideas / solutions on how to eliminate empty rows from being inserted in the database table?  For example, my excel file contains 5 rows, however the data reader is being populated with 10 rows which 5 are obviously extra rows.  So, how do I prevent these extra 5 rows from either being read into the datareader or being inserted into the database.  Currently, I have added a nice long IF statement to check for null values for each cell I am expecting data but I was wondering if anyone had a more elegant solution to this nuance.

Matt

# March 3, 2008 9:32 AM

nannette said:

Todd,

I'm actually using a spreadsheet created using microsoft excel 2003 and have no problems.

I'd just try it.

Nannette

# March 3, 2008 1:46 PM

nannette said:

Matt,

>>I have added a nice long IF statement to check for null values for each cell I am expecting data

If there is one field that you know has to be entered for every valid row, such as a name, you could check it for null values and if it is empty, assume it is one of your blank rows.

But I'm unsure why your sheet has 5 rows and the datareader populates with 10 rows. Have you tried copying and pasting the 5 rows into a new spreadsheet to make sure there's not some hidden data in those 5 rows? Maybe some type of calculation or something.

Sorry I can't be of more help.

Nannette

# March 3, 2008 1:48 PM

Gabriel de Figueiredo said:

I have tried a lot of import routines to import a spreadsheet of product data into a Database, both using C# (using ado.net, treating the spreadsheet as a database) as well as SSIS import.  

None of these techniques work properly for a large numerical string treated as text in the spreadsheet, e.g. a product with a serial number 1234567890123 will be imported in scientific notation (1.23457E+12).  This is even when the connect string has "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"", also the registry settings been changed to ImportMixedTypes=1 and TypeGuessRows=1 (I had the first non-header row consist entirely of Xs).

I reckon the above behaviour is due to the fact that the Microsoft.Jet.OLEDB.4.0 is meant for the dumb user, not allowing the programmer much freedom in the choice of import, because Jet makes decisions for itself as to the type of data it is importing.

So back to square one, I had to finally resort to using Interop.Excel in C#, use ranges and inspect each cell to get the required data into the database the way I, as a programmer, would like it to.

# March 5, 2008 6:38 PM

SSahni said:

Hi Matt,

Did you find any solution to your problem where you are getting more rows than the rows present in excel? I am facing a similar issue but unable to find a resolution.

# May 1, 2008 9:25 AM

seven said:

Will this work with 1.1 frame works visual studio 2003

# May 9, 2008 4:48 PM

Sreelu said:

My application needs the data to be read from an excel and store the information into the oracle database table.

The data is copyied to the table. But what if for any reason, if any row fails to be stored , i want them to store to another table , say bad_table.

how do I achieve it?

Please help me

# November 6, 2008 11:14 PM

nannette said:

this has less to with importing an excel spreadsheet, and more to do with rolling back transactions in the database or something. You should ask this question in an oracle forum.

Sorry I can't be of assistance.

# November 7, 2008 1:28 AM

beleami said:

How do you implement the UPDATE option so when the file is uploaded into the database it will update a row.

# February 2, 2009 10:50 PM

santosh kr sah said:

i want to call excel spread sheet in asp.net C# and display information on visual studio

# June 25, 2009 12:47 PM

nannette said:

Here is a version of this application and article using C#:

www.shiningstar.net/.../DataSetProject.aspx

# July 11, 2009 8:01 AM

joseph said:

i have a spreadsheet that containes student details and  want to import all this data into an SQL 2005/2008 database using visual studio 2008.net...what sort of parameters should i pass?

Thanx for the article,its very helpful

# September 13, 2009 10:24 AM
Leave a Comment

(required) 

(required) 

(optional)

(required)