none
Problem with OPENROWSET

    Pregunta

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

    martes, 21 de agosto de 2007 8:12

Todas las respuestas

  •  

    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$)

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

    martes, 21 de agosto de 2007 9:55
  • 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

     

     

    martes, 21 de agosto de 2007 10:05
  • 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!

    miércoles, 22 de agosto de 2007 7:49
  •  

    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.

     

     

    miércoles, 22 de agosto de 2007 8:41
  • 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?

    miércoles, 22 de agosto de 2007 9:01
  •  

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

    miércoles, 22 de agosto de 2007 9:15
  • 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?
    • Editado micJohn jueves, 04 de junio de 2009 19:36
    jueves, 04 de junio de 2009 19:24