none
Problem While Reading Excel Sheet RRS feed

  • Question

  •  

    I made a program that read excel file and store excel data in a data set.
    I wrote code like :

    dim ds1 as DataSet
    sheet = txtsname.Text
    Dim ExcelFilePath
    ExcelFilePath = path;  //Excel file path

    MyConnection = New System.Data.OleDb.OleDbConnection( _
    "provider=Microsoft.Jet.OLEDB.4.0; " & _
    "data source=" & ExcelFilePath & "; " & _
    "Extended Properties=Excel 8.0")
    Dim query = "select * from [" + sheet + "$]"
    MyAdapter = New System.Data.OleDb.OleDbDataAdapter(query, MyConnection)

    ds1 = New System.Data.DataSet
    MyAdapter.Fill(ds1)

    This code store data in a data set but some problem with this code
    Problem is that suppose in my excel file in first row there is 200 character and another next rows has 300 or more than first row but it cut some character form excel file and store it in data set.

    If in excel file in first row has more character that another rows then it dosen't cut any data from excel file. and it store write data.

    help me.
    Tuesday, September 30, 2008 6:40 AM

Answers

  • Jet provider tries to guess types of columns and maximum length based on scanning first N number of rows. Default value is 8 rows. In addition it puts default limit of 255 characters. You would need to change TypeGuessRows registry value to specify how many rows should be scanned to identify type of column. You could find this value at

     

    HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows

     

    Setting TypeGuessRows to 0 will force scanning of all the rows inside of spreadsheet. You could also try .NET reader for Excel from my website. It allows to read data from Excel without using Jet or Excel application.

     

     

    Monday, October 6, 2008 7:38 PM
    Moderator

All replies

  • Jet provider tries to guess types of columns and maximum length based on scanning first N number of rows. Default value is 8 rows. In addition it puts default limit of 255 characters. You would need to change TypeGuessRows registry value to specify how many rows should be scanned to identify type of column. You could find this value at

     

    HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows

     

    Setting TypeGuessRows to 0 will force scanning of all the rows inside of spreadsheet. You could also try .NET reader for Excel from my website. It allows to read data from Excel without using Jet or Excel application.

     

     

    Monday, October 6, 2008 7:38 PM
    Moderator
  • This is really a managed Provider issue, moving to this forum so that other people can find the answer there.

     

    Thanks

    Chris Robinson

    Program Manager - DataSet

     

    Monday, October 6, 2008 7:47 PM