IMEX

  • In a lot of organizations, Excel is everywhere and Excel is everything. It is extremely important for modern ETL tool to work with Excel correctly.

    Of all Microsoft Office applications, Excel is one of the most important ones for all kind of businesses. This is because it provides an enormous capacity to perform everything from simple day to day operations to complex statistical analysis. It comes packed with lots of useful formulas, charts and other tools that simplify the process of recording, analyzing and performing calculations of data. This is the reason why it is used daily in millions of workplaces all over the world. Almost everyone who deals with data has used the Excel interface at some point. 

    Our software can process and create Excel files automatically, the possibilities are limitless

    Download Advanced ETL Processor Enterprise now!


    Loading Data from excel file

    We worked with a lot of finance and accounting companies and the first question they always ask: How do we export the data to Excel?

    How our ETLsoftware work WITH excel as data-source

    To harness the full power of Excel, you need to have an ETL tool that is capable of handling input from Excel files. Owing to the many ways you can use Excel to achieve your desired results, you need an ETL tool that is flexible enough to understand the different ways data can be processed. This is where our Advanced ETL Processor really shines. It is able to work with Excel as a data source and take inputs from Excel files depending on your specific preferences. Here are some of the things you can do with this ETL tool:

    • Use a mask to load data from multiple Excel files
    • Use a mask to load data from multiple Excel sheets
    • Use a mask to load data from multiple excel ranges
    • Skip the header and footer in the Excel file
    • Read a single cell from an Excel file
    • Accurately read data from Excel 100% of the time
    • No need to install additional drivers to read Excel data

    This ETL software is built by people experienced in managing data warehouses hence you can be sure that it comes packed with features that you will actually use in your day to day operations. Whatever you need to get data from Excel files, you will find it in our Advanced ETL Processor.

    Using Excel as a data target

    In addition to allowing you to get data from Excel, our Advanced ETL Processor enables you to export data to Excel files. Once again, this tool is designed to give you full functionality when you need to Export data to Excel. Whether you are looking to create a new Excel file for your data or you want to add the data to an existing file, the Advanced ETL Processor is all you need. Here are some of the things you can do with it when using Excel as a data target:

    • Create a new Excel file with your data
    • Insert the data into an existing Excel file
    •  Clear an Excel sheet or range
    •  Append data to an Excel file
    •  Choose the cell where you will start adding data on an existing Excel sheet
    •  Replace data in an excel sheet with new data

    Generating excel file

    Workflow operations

    In addition to the above data operations, you can also perform a number of workflow operations on Excel sheets. Here are some of the operations that you can perform:

    • Check if the sheet is present within excel file
    • Delete a sheet on an Excel file
    • Rename a sheet in an Excel file
    • Insert an empty sheet
    • Check an entire Excel file

    Whatever data operations you want to do on Excel files, our Advanced ETL Processor will help you do it easily and effortlessly. Click the button above to download it for free and try out some of the amazing features.
    The Advanced ETL Processor is the ultimate ETL tool when it comes to working with Excel files.

    Excel operation

    GREAT PERFORMANCE - THOUSANDS OF RECORDS PER SECOND

    Loading Data from excel file Thousands of Records Per Second

    TRANSFORM EXCEL FILES - ANY WAY YOU WANT

    Advanced ETL Processor has more than 500 transformation and validation functions. if something is missing we will add for you. Data can be sorted grouped and filtered, the possibilities are limitless. 


    Transform Excel files anyway you want

     COMPLETE EXCEL FILES PROCESSING AUTOMATION

    Advanced ETL Processor offers varions automation actions such as: file operations, email processing, FTP operations, compression and even encription. 

    Automate Excel Attachments Processing

    Contact us now!

    Related Articles

    VIDEO TUTORIAL

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

    Common problem:trying to load the data from Excel file half of the data is coming as nulls, or columns with more than 255 characters are truncated

    The logic behind Excel mixed data types

    As partially explained here

    http://support.microsoft.com/kb/257819

    ODBC/MS Jet scans first TypeGuessRowsto determine field type

    Here how Excel ODBC/MS Jet works

    (TypeGuessRows=8 IMEX=1)

    In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.
    In your eight (8) scanned rows, if the column contains three (3) numeric values and five (5) text values, the provider returns three (3) null values and five (5) text values.
    In your eight (8) scanned rows, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4) numbers and four (4) null values.
    In your eight (8) scanned rows all of them less than 255 characters the provider will truncate all data to 255 characters
    In your eight (8) scanned rows, if the column contains five (5) values with more length than 255 the provider will return more than 255 characters

    NOTE:

    Setting IMEX=1 tells the driver to use the Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight-row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.

         Nobody wants to load half of the data, everybody wants to load data as it is

    The only way to make import from Excel work is

  • 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 277 guests and no members online