Excel

  • Introduction to Advanced ETL Processor

    Learn how to load data from an Excel file into the database data in 5 minutes.

    All our ETL tools have no limitations, that means you can load your data today and solve all the data quality problems right now.

    Our WIKI has more detailed information if you are stuck post your question on our support forum and we will do our best to assist you

  • Introduction: Loading Data from EXCEL file into MySQL Database

    In this tutorial, you will learn how to use Advanced ETL Processor Pro workflow designer to create a simple data transformation package. The package will take the data from an Excel file, validates the data, and then inserts the data into a MySQL fact table. In the following lessons, the package will be expanded to demonstrate connections, package configurations, logging and error flow.

    This tutorial is intended for users familiar with fundamental database operations, but who have limited exposure to the new features available in Advanced ETL Processor.

    All our ETL tools have no limitations, that means you can load your data today and solve all the data quality problems right now.

    Our WIKI has more detailed information if you are stuck post your question on our support forum and we will do our best to assist you

  • 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
  • Transforming Excel data can be a complex task this article describes how the Advanced ETL Processor works with Excel data.

    Reading EXCEL data

    Data can be extracted from the entire Sheet or just from the data range.
    Advanced ETL Processor assumes that "Sheet name" and "Range name" a delimited by a dot.

    Users can still call Sheets as a "Great.Victory"

    The logic as follows:

    Advanced ETL Processor will check if Great.Victory sheet exits than it will read it
    If it does not exist it will check if Great sheet exits
    If it does exists it will check if Victory range exists if it does It will read data from just Victory range
    If it does not it will read the data from Sheet "Great"

    Please make sure that the range names are not duplicated within the file

    Writing EXCEL data

    Advanced ETL Processor gives the user various options to generate Excel files.

    Transforming Excel data

  • Quite often it is necessary to get the content of an Excel cell and load it into the database. For example, when processing invoices it would be necessary to load an invoice date.

    In the latest version of Advanced ETL Processor,we have introduced Get Excel Cell Value transformation function

    GetExcelCellValue

    There is also the dynamic version of this function:

    Get Excel Cell Value Dynamic

    See it yourself, Download Advanced ETL Processor Enterprise Now!
  • Get EXCEL Cell Value in three simple steps

      • Download and install the software, here are the links to the latest version

    Advanced ETL Processor:

    Download Buy It
    • Watch the following online tutorial to understand the data transformation process:
      Advanced ETL Processor - performing validation and transformation
       
      In this tutorial, you will learn how to transform text file using Advanced ETL Processor
    • Transform the data

    Get EXCEL Cell Value Data Transformation Function function properties

    Get Excel Cell Value Transformation function

    NameValue
    Description: Returns Excel cell value from the specific file

    Properties:

    Get Excel Cell Value Transformation function properties

    Note: One of the benefits of using Date Format function that it supports multiple input formats

    Advanced ETL Processor has a robust data transformation process built in.

    The types and nature of the transformations taking place can be tweaked and configured by the user. A full range of data transformation functions is included. Transformations can be performed on the basis of data type, lookups and regular expressions, which can be individually changed according to the requirements.

    Additional Information:

    Visit Support Forum Learn More Download 32 Bit Download 64 Bit
  • 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

  • Advanced ETL Processor Reader Object is capable of extracting data from MS Excel files and MS Acess databases. In order to work with MS Office 2007 files please download Office 2007 Data Access Components

    ETL Data reader source type

    See it yourself, Download Advanced ETL Processor Enterprise now!

    Related WIKI Pages

    VIDEO TUTORIAL

    Download Free Trial Learn More About Our ETL Software
  • I've been looking at your Advanced ETL Processor for taking data from Excel sheets into a MySQL database. We receive regular Excel sheets from about 20-25 suppliers quoting rates for services; each supplier has a different format (although some commonalities between them).

    Some questions:

    • Can loading definitions be set up to run automatically / semi-automatically? i.e. when a particular supplier provides their new Excel sheet can the previous loading definition set up for this supplier be accessed and used to load data via some schedule?

      Answer
      : Yes, it is possible. You can filter incoming emails using sender email or subject and execute appropriate data transformation script.
      Business Automation Package
    • Can it take data from different worksheets within a single Excel file and join to load into MySQL tables?

      Answer:
      Yes, it is possible. Every Excel sheet, print area or a filter is treated as a separate table and so can write an SQL statement to join the tables
      Excell File
      Data Reader
    • Can it be configured to ignore a defined number of header lines? Or look for a specified text to identify the next line to start importing data from?

      Answer:
      Yes, it is possible. You can use a validator to filter records header/footer records out

    • Can it handle logic to identify records that shouldn't be imported? e.g. if one column contains a numeric currency value and it finds "NA" can it ignore these records? i.e. not attempt to load them

      Answer:
      Yes, it is possible. Again you can use a validator to filter records out
      Validation EditorIs Equal Properties
    • Some Excel sheets contain a record where a cell contains a list of values (generally comma separated)... can it break this up and store multiple records? i.e. cell A may contain "John", cell B "$0.015" and cell C "A, X, 56, Z".... can this be imported to store four records:
      (John, $0.015, A) (John, $0.015, X) (John, $0.015, 56) (John, $0.015, Z)

      Answer:
      Yes, it can be done using an UnPivot object.
      UnPivot PropertiesUnPivot Results
    • Sometimes have a variation on the above, e.g. cell A may contain "Susan", cell B "$0.017" and cell C may be empty this time.... can this be imported to store one record:
      (Susan, $0.017, NULL)

      Answer:
      Yes, it is possible. You can use a combination of unpivot and validator to filter null records out
    • Another variation on the above #5 is that the multi-cell content may exist on another worksheet, e.g. on worksheet X, cell A may contain "Simon", cell B "$0.03" and then on worksheet Y, cell A would contain "Simon" and cell B "T, U, P".... can this be imported to store three record:
      (Simon, $0.03, T) (Simon, $0.03, U) (Simon, $0.03, P)

      Answer:
      Yes, it can be done using separate transformations
      Data Transformation
    • Excel sheets can also contain footnotes; i.e. records at the bottom of the data requiring loaded that should be ignored. Can a footer definition be set up to be ignored? e.g. text to specify the start of the footer or a certain number of blank rows in the worksheet to define a point at which import stops?

      Answer:
      Sure, use the validator to achieve that.
    • Can a timestamp be added by the import tool and stored with all records as an extra field into MySQL table? This timestamp may either be sysdate or taken from a specified fixed cell in the Excel sheet - can both of these options be supported?

      Answer:
      Yes, it can be done by using a transformation object.
    • As a variation to the above #9, can the timestamp come from different locations depending upon the value of a field within a row being imported? e.g. if cell C was "Increase" on the row currently being imported then obtain timestamp from cell A3 otherwise take from cell A4. Then as each row is imported, the timestamp value being stored comes from either A3 or A4 depending upon content.

      Answer:
      Yes, it is possible. It can be done in several ways, for example, you can validate timestamp format and if there is something wrong with it, you can use current date and time or you can write your own calculation transformation and use any logic you wish.
      Is Date Properties
      calculation properties
    • Can it read in content within a single cell such as "18:00-07:59" and break into two fields as "18:00" and "07:59" at times?

      Answer:
      Yes, you can achieve this by using a splitter object.
      Splitter Properties
      Transformation Editor
    • Is any modification/preparation of the Excel file required before your product can process it?

      Answer: In some cases, it might be necessary. It is important to keep the format the same, so no future modifications would be required.

    Additional information:

    It is very interesting what you are trying to achieve and we would be happy to assist you in difficult cases. If some functionality is missing we will add for you.

    Download Free Trial Learn More About Our ETL Software
  • Based on customer feedbackin the latest version we have added new package object ExcelOperation Action

    It allows to:

    • Check if Excel Sheet exists
    • Delete Sheet
    • Add Sheet
    • Rename Sheet

    Download Free Trial Learn More About Our ETL Software
  • 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?

  • This Article provides examples of complex and simple data. We recommend using  Advanced ETL Processor for working with complex data structures and Visual Importer ETL with simple; If in doubt please contact us and we will help you to choose.

    Simple data example: Excel file.

    Data has a stable structure and there is only one value in the cell. Most database tables and delimited files can be considered as simple data. except when a single field has multiple values or holds XML data.

    Simple excel fileSimple XML file:

    XML tags are in the same order, the number of tags is always the same and there are no attributes, This XML does not require any additional transformation and can be easily loaded into the database.

    Simple XML

    Complex XML message:

    The structure is not stable, the number of tags is different for different customer record, plus it uses attributes. This XML may need some additional transformations.

    Complex XML

    Complex Text file:

    Data is split into different rows, some empty rows should be ignored, some data must be taken from the header

    Complex TEXT

    SQL insert script:

    Data can be easily extracted or generated using  Advanced ETL Processor

    Insert Statement

    mt_gallery:Advanced ETL Processor Enterprise Screenshots
    Watch Online Tutorials Download 32 Bit Download 64 Bit

We have 1229 guests and no members online