Microsoft Jet OLE DB 4.0 connection strings

Connects to

Paradox

Problems connecting?
Get answer in the Paradox Q & A forum

Excel 97

  • Standard (Excel)

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;
    Extended Properties
    ="Excel 8.0;HDR=Yes;IMEX=1";
  • Standard alternative

    Try this one if the one above is not working. Some reports that Excel 2003 need the exta OLEDB; section in the beginning of the string.

    OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;
    Extended Properties
    ="Excel 8.0;HDR=Yes;IMEX=1";

    "HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

    "IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

    SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

    "SELECT * FROM [Sheet1$a5:d]", start picking the data as of row 5 and up to column D.

    Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

    If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."

    A workaround for the "could not decrypt file" problem

Excel 2000

  • Standard (Excel)

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;
    Extended Properties
    ="Excel 8.0;HDR=Yes;IMEX=1";
  • Standard alternative

    Try this one if the one above is not working. Some reports that Excel 2003 need the exta OLEDB; section in the beginning of the string.

    OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;
    Extended Properties
    ="Excel 8.0;HDR=Yes;IMEX=1";

    "HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

    "IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

    SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

    "SELECT * FROM [Sheet1$a5:d]", start picking the data as of row 5 and up to column D.

    Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

    If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."

    A workaround for the "could not decrypt file" problem

Excel 2002

  • Standard (Excel)

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;
    Extended Properties
    ="Excel 8.0;HDR=Yes;IMEX=1";
  • Standard alternative

    Try this one if the one above is not working. Some reports that Excel 2003 need the exta OLEDB; section in the beginning of the string.

    OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;
    Extended Properties
    ="Excel 8.0;HDR=Yes;IMEX=1";

    "HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

    "IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

    SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

    "SELECT * FROM [Sheet1$a5:d]", start picking the data as of row 5 and up to column D.

    Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

    If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."

    A workaround for the "could not decrypt file" problem

Excel 2003

  • Standard (Excel)

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;
    Extended Properties
    ="Excel 8.0;HDR=Yes;IMEX=1";
  • Standard alternative

    Try this one if the one above is not working. Some reports that Excel 2003 need the exta OLEDB; section in the beginning of the string.

    OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;
    Extended Properties
    ="Excel 8.0;HDR=Yes;IMEX=1";

    "HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

    "IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

    SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

    "SELECT * FROM [Sheet1$a5:d]", start picking the data as of row 5 and up to column D.

    Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

    If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."

    A workaround for the "could not decrypt file" problem

Textfile

  • Delimited columns

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;
    Extended Properties
    ="text;HDR=Yes;FMT=Delimited";

    The delimiter can be specified in the registry at the following location:
    HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Text
    "Format" = "TabDelimited"
    or
    "Format" = "Delimited(;)"

    How to Use JET in 64 bit environments

  • Fixed length columns

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;
    Extended Properties
    ="text;HDR=Yes;FMT=Fixed";

    "HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

    To specify each columns length use the Schema.ini file. See description below.

    Important note!
    The quota " in the string needs to be escaped using your language specific escape syntax.
    c#, c++   \"
    VB6, VBScript   ""
    xml (web.config etc)   "
    or maybe use a single quota '.

    More info on using the schema.ini file

Access 97

Access 2000

Access 2002

Access 2003

DBF / FoxPro

HTML Table

  • Standard (HTML table)

    Yes, it is possible to query a html table using OLE DB!

    Provider=Microsoft.Jet.OLEDB.4.0;
    Data Source
    =http://www.websitewithhtmltable.com/tablepage.html;
    Extended Properties
    ="HTML Import;HDR=YES;IMEX=1";

    Use the following syntax: "Select * from [page title]" to query the html table at specified location.

    How to Use JET in 64 bit environments

OLE DB ProviderProvider=Microsoft.Jet.OLEDB.4.0Info and provider downloads

Articlesread all »

Providers

Connect

Articlesread all »

Didn't find your connection string?

Start over from the connection string reference index - or try a search!

In the Q&A forums you can ask your own question and let somebody help you.

The knowledge articles contains solutions and guides.