Excel & OpenRowset

• RLSQL

  Say Hey Kid

  Points: 713

  More actions

  May 19, 2006 at 2:32 pm

  #113799

  Hi,

  In Test.xls, I’ve created a single sheet (Sheet1) that contains this data:

  Chris	Bob	Andrew
  1	2	3

  I run this query:

   

  Select

    *

  From

    OpenRowset (

        'Microsoft.Jet.OLEDB.4.0'

      , 'Excel 8.0;

         Database=C:\Code\ToolsAndUtilities\Conversions\Test.xls;

         HDR=YES;IMEX=1'

      , Sheet1$

    )

   

  The result of the query is: 

  Andrew	Bob	Chris
  3.0	2.0	1.0

  I have the following questions:

  1. Where can I find documentation on the parameters (HDR, IMEX, etc) that I can use with OpenRowset. It would seem these are specific to the OLEDB driver I’m using – where can I find them?
  2. How can I prevent OpenRowset from returning ‘float’ types for these columns?
  3. How can I prevent OpenRowset from re-arranging the columns according to alphabetic order?

  Thanks for any pointers,

   

  Richard

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  May 22, 2006 at 12:34 am

  #639160

  Connection to Excel using Jet Engine

  To connect to Excel, one can use OleDb objects that will treat Excel as a database, and then the required information can be easily fetched by using SQL queries. The important steps that have to be considered while connecting to Excel are as follows:

  • Connection String:

    The connection string should be set to the OleDbConnection object. This is very critical as Jet Engine might not give a proper error message if the appropriate details are not given.

    Syntax: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<Full Path of Excel File>; Extended Properties="Excel 8.0; HDR=No; IMEX=1".

  • Definition of Extended Properties:

    • Excel = <No>

      One should specify the version of Excel Sheet here. For Excel 2000 and above, it is set it to Excel 8.0 and for all others, it is Excel 5.0.

    • HDR= <Yes/No>

      This property will be used to specify the definition of header for each column. If the value is ‘Yes’, the first row will be treated as heading. Otherwise, the heading will be generated by the system like F1, F2 and so on.

    • IMEX= <0/1/2>

      IMEX refers to IMport EXport mode. This can take three possible values.

    • IMEX=0 and IMEX=2 will result in ImportMixedTypes being ignored and the default value of ‘Majority Types’ is used. In this case, it will take the first 8 rows and then the data type for each column will be decided.
    • IMEX=1 is the only way to set the value of ImportMixedTypes as Text. Here, everything will be treated as text.

   

  ---

  N 56°04'39.16"
  E 12°55'05.25"