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

13 Comments

  • 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

  • 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

  • Todd,

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

    I'd just try it.

    Nannette

  • 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

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

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

  • Will this work with 1.1 frame works visual studio 2003

  • 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

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

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

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

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

    http://www.shiningstar.net/ASPNet_Articles/DataSet/DataSetProject.aspx

  • 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

Comments have been disabled for this content.