none
Problem with OPENROWSET

    Question

  • Hi!

    I'm using OPENROWSET to import data from an excel file into a MS SQL table.I got it to work.....Now,my problem is that not all the data from the SQL table is imported and some values are different than the ones in the excel file.

    For example,in the excel file,I have the value:87987845.In the SQL table,the value is:8.79878e+007.Any idea on what causes this? 

    Tuesday, August 21, 2007 8:12 AM

All replies

  •  

    This is a limitation of Excel ISAM driver. The determined/sampled datatype is
    based on the majority. Once this is selected, the rest will be returned as NULL.
    One way to circumvent this is to force an ImportMixedTypes to text.

     

    e.g.
    select * from
    OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test.xls;IMEX=1',Sheet1$)

    Tuesday, August 21, 2007 9:05 AM
  • I added imex=1 and the situation is the same....Any idea on what else could I do?

    Tuesday, August 21, 2007 9:55 AM
  • What do you have for these two keys?

     

     HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel/ImportMixedType­s
     HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows

     

    You want to set ImportMixedTypes to "Text" and TypeGuessRows to "0".

     

    Additional info can be found here.

    http://support.microsoft.com/?id=194124

     

     

    Tuesday, August 21, 2007 10:05 AM
  • The value I'm getting in the sql table is the exponential value of the number,right?In this case,are those modifications to the keys still necessary?

     

    Thanks!

    Wednesday, August 22, 2007 7:49 AM
  •  

    Yes. The format is based on the sampling (first 8 rows is the default). You'd want to change the two keys to control the import.

     

     

    Wednesday, August 22, 2007 8:41 AM
  • Thank you for your answer.Also,I have a question:where are keys supposed to be changed?I have a Database server.Are those keys supposed to be set on the database server?

    Wednesday, August 22, 2007 9:01 AM
  •  

    Yes. You change the values on the database server.

     

    HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel/ImportMixedType­s
     HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows

     

    You want to set ImportMixedTypes to "Text" and TypeGuessRows to "0".

    Wednesday, August 22, 2007 9:15 AM
  • I am having the same problem. But the above changes could not help me to solve this issue.

    Is  there a way that we can solve this without changing registry entry?
    • Edited by micJohn Thursday, June 04, 2009 7:36 PM
    Thursday, June 04, 2009 7:24 PM