none
Accessing data from excel, tab delimited and csv files RRS feed

  • Question

  • HI I have files which are either xls, txt(tab), csv format.
    They have a column name and data for eg:

    xyz      abc       cde     efg
    ---------------------------------------
    1        "xyz"      -20      No
    2        "asdf"      10      Yes

    This is just example data my files have atleat 15 columns and few r nulls for a row.

    How do i read this data using ADO.net and display in grid and store in database?
    Tuesday, November 6, 2007 5:58 PM

Answers

  • If you need to transfer data from Excel or other file into SQL Server, you could use SQL Server DTS services (in SQL Server 2000) or SQL Server Integration Services (in SQL Server 2005). They allow transferring data between different data sources without loading then into client application, so they are fast.

     

    There are several ways to work with Excel files if you need to query them directly

    1. Use Jet OLEDB Provider. Jet Oledb provider allows to query and update/insert data inside of Excel files without using Excel. But it has some limitations when it does not recognize dates properly in some cases and returns NULL values for some cells if column inside of the spreadsheet has mixed types of data.

    2. Use Microsoft Office Tools for .NET. This way is slower, but gives you more functionality and returns proper data type for each cell. You need to pay attention to releasing all the resources associated with it, because it is COM-based.

    3. Use third party components that could provide desired functionality

     

    Following are some links with the samples

    http://support.microsoft.com/kb/306022/en-us

    http://support.microsoft.com/kb/326548/en-us

    http://support.microsoft.com/kb/307029/en-us

     

     

     

    Friday, November 9, 2007 11:12 AM
    Moderator

All replies

  • What type of database do you want to store this data in?

     

    Thursday, November 8, 2007 12:51 PM
  • sql server 2005 database!!
    Thursday, November 8, 2007 9:12 PM
  • If you need to transfer data from Excel or other file into SQL Server, you could use SQL Server DTS services (in SQL Server 2000) or SQL Server Integration Services (in SQL Server 2005). They allow transferring data between different data sources without loading then into client application, so they are fast.

     

    There are several ways to work with Excel files if you need to query them directly

    1. Use Jet OLEDB Provider. Jet Oledb provider allows to query and update/insert data inside of Excel files without using Excel. But it has some limitations when it does not recognize dates properly in some cases and returns NULL values for some cells if column inside of the spreadsheet has mixed types of data.

    2. Use Microsoft Office Tools for .NET. This way is slower, but gives you more functionality and returns proper data type for each cell. You need to pay attention to releasing all the resources associated with it, because it is COM-based.

    3. Use third party components that could provide desired functionality

     

    Following are some links with the samples

    http://support.microsoft.com/kb/306022/en-us

    http://support.microsoft.com/kb/326548/en-us

    http://support.microsoft.com/kb/307029/en-us

     

     

     

    Friday, November 9, 2007 11:12 AM
    Moderator