ODBC

  • Both Visual Importer ETL and Advanced ETL Processor are 32-bit applications so they use 32-bit ODBC drivers

    Our software uses the highest driver installed for example If both SQL server 2005 and 2008 are installed it will use SQL Server 2008 Driver.

    To Check Version of SQL Server ODBC Driver do the following

    • Click  Maintain
    • Click ODBC Manager

    donate

    Here are the links to the latest version of drivers:

    Feature Pack for Microsoft SQL Server 2005

    Download Driver

    Download Instructions
    1. Hit the Download button.
    2. Checkbox one of the installation packages (msi). Make sure you choose the right version:
    3. sqlncli.msi for 32-bit
    4. Click Next.
    5. Download starts.

    Feature Pack for Microsoft SQL Server 2008

    Download Driver

    Download Instructions
    1. Hit the Download button.
    2. Checkbox one of the "sqlncli.msi" installation packages. Make sure you choose the right version:
    3. x86 for 32-bit
    4. Click Next.
    5. Download starts.

    Feature Pack for Microsoft SQL Server 2008 R2

    Download Driver

    Download Instructions
    1. Do NOT hit the Download button
    2. Expand the "Install Instructions" section
    3. Under the "Microsoft SQL Server 2012 Native Client" heading, click one of the "sqlncli.msi" installation package links. Make sure you choose the right version:
    4. X86 Package for 32-bit
    5. Download starts.

    Feature Pack for Microsoft SQL Server 2012

    Download Driver

    Download Instructions
    1. Hit the Download button.
    2. Checkbox one of the "sqlncli.msi" installation packages. Make sure you choose the right version:
    3. sqlncli_x86.msi for 32-bit
    4. Click Next.
    5. Download starts.

    Use SQL Server 2012 Drivers for SQL Server 2014

    Note

    Most of the time existing drivers work fine with all versions of SQL Server all you need to do is to make sure that you have latest driver installed, so if you have SQL server 2008 driver installed and work with SQL server 2012 make sure that you have the latest version of SQL server 2008 driver installed

  • There are several ways to connect Excel Files and run SQL against it:

    Data can extracted via ODBC, OLE DB(Jet) or .Net providers

    Odbc connection strings:

    For Excel 2.0-2003

    DRIVER={Microsoft Excel Driver (*.xls)};IMEX=1;MaxScanRows=16;DBQ=C:\Invoice.xls;
     

    For Excel 2007+

    DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};IMEX=1;MaxScanRows=16;DBQ=C:\Invoice.xls;

    Both Advanced ETL Processor and Visual Import ETL can use odbc to connect to Excel

    OLE DB connection strings:

    For Excel 2.0-2003

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

    For Excel 2007+

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

    "HDR=Yes;" indicates that the first row contains column names, not data. "HDR=No;" it does not

    Very useful feature for the Excel file below when you want to retrieve Invoice Number:

    Invoice Excel File

    Running SQL against Excel file

    SQL syntax for ODBC and Ole DB is slightly different

    • For ODBC: SELECT "Column Name" FROM "Sheet One$". I.e. excel worksheet name followed by a "$" and wrapped in double quotes.
    • For Ole DB: SELECT [Column Name] FROM [Sheet One$]. I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.
    Extract data from a single Excel worksheet
    SELECT * FROM [Sheet1$]
    To specify a named range of cells as your record source, simply use the defined name. For example:
    SELECT * FROM MyRange
     

    Visual   Importer - Data source properties dialog

    Unnamed Range

    To specify an unnamed range of cells as your record source, append standard Excel row/column notation to the end of the sheet name in the square brackets. For example:

    SELECT * FROM [Sheet1$A1:B10]

    Visual Importer - Extracting data from Excel

    Common Excel Import problems

    ADO/ODBC must determine the data type for each column in your Excel worksheet or range  (This is not affected by Excel cell formatting settings.) This is done by scanning number of rows defined by the registry setting TypeGuessRows (default value is 8). Quite often there are numeric values mixed with text values in the same column,

    For example, sorted financial coding structures often have numbers at the beginning of the list 001-999 than text AAA-XXX

    Both the Jet and the ODBC Provider return the data of the majority type but return NULL (empty) values for the minority data type. If the two types are equally mixed in the column, the provider chooses numeric over text.

    Plus when first rows are less than 255 characters long it will truncate all data to 255 characters even if cell values below are longer

    One of the ways of avoiding this problem is using Import Mode (IMEX=1). This forces mixed data to be converted to text. However, it only works when the first TypeGuessRows Rows have mixed values.
    If all values are numeric than setting IMEX=1 will not convert the default datatype to Text, it will remain numeric.  

    The best combination to avoid problems is TypeGuessRows=0 + IMEX=1.
    Setting TypeGuessRows=0 forces driver to read all data to determine field type.
    Unfortunately, our own experience shows that quite often it does not work.
    And when it does work it slows everything down

    So the only solution is not to use mixed values and be prepared for the data being truncated to 255 characters

    As you can see there is plenty of room for improvement for Microsoft. The first version of Microsoft Office was released very long time ago and it is still not possible to read excel file correctly. Why do not they just add another setting to ODBC driver EverhingIsText=1? This so simple and will solve all the problems.

    All our products work correctly with Excel because we do not use OleDb or ODBC

    See it yourself, Download Advanced ETL Processor Enterprise now!

    VIDEO TUTORIAL

  • The new version of Advanced ETL Processor is available for download

    This version addresses well-known problems related to working with Excel files

    • Works directly with all versions of Excel
    • No ODBC, OleDB or MS Jet Required
    • Works correctly with mixed data types
    • Works correctly with cells with more than 255 characters
    • No need for IMEX=1, HDR=Yes or Registry hacks (TypeGuessRows)
    • Works with ranges
     
    • Loads data correctly all the time + no need to edit Excel file
    • Can create Excel files in Excel 3.0-2016 format
    • Can insert data starting from specific cell
    • Can clear area before adding data into Excel
    • Can add headers

    Loading Data from Excel File

    List of Microsoft Excel ODBC driver bugs you do not need to deal with anymore

    1. PRB: Excel Values Returned as NULL Using DAO OpenRecordset
    2. Data truncated to 255 characters with Excel ODBC driver
    3. Excel ODBC Driver May Determine Wrong Data Type
    4. BUG: Excel ODBC Driver Disregards the FirstRowHasNames or Header Setting
    5. How To Use ADO with Excel Data from Visual Basic or VBA
    The sad story is: what ever you use: Excel ODBC, MS Jet, Ole DB, .NET sooner or later you will hit the brick wall.
    You can not load the data correctly without modifying excel file. We receive thousands of excel files every day.
    I mean do you seriously expect us to modify them manually every day? What kind of automation is that?
    We tried DTS it did not work tried SSIS it did work either, so we wrote vb macros to extract the data.
    We do not do it any more since we have discovered Advanced ETL processor It saved us thousands of hours.
    John Spoon.

    Also Data grids modifications:

    • Grouping panel was added
    • Advanced filter dialogue was introduced
    • Record number in the grid
    Try it yourself now

    VIDEO TUTORIAL

    Download Free Trial Learn More About Our ETL Software
  • Solving SSIS problems with loading data from Excel

    We have the following situation:

    Imagine that you are working for a large finance organization and would like to load the year-end balances into SQL Server database. The excel file looks like this.

    Excel File

    So we create a simple SSIS package using Excel connector and Ole DB destination, before loading the data we hit preview button.

    SSIS Preview Result

    For some reason last 3 lines have null values, This is because our connection string has not got IMEX=1

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DEMO\Excel\mixed_data_types.xls;Extended Properties="EXCEL 8.0;HDR=YES";

    We correct it

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DEMO\Excel\mixed_data_types.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

    SSIS Preview Result

    That looks much better so we load the data. Later the same day, the finance manager says that the balance is wrong you look at the data and there are some null values again.

    Why DO we have nullS in the database?

We have 184 guests and one member online