Excel

  • 0004 How to read a single excel cell value

    This Example Demonstrates how to read a single excel cell value and email it
    • Download and install Advanced ETL Processor [Link]
    • Download and Unzip example [Link]
    • Create a new package and open .wfp file

     How to rename file using current date as part of file name

    • Double click on Excel Operation Action Properties and change the file name, sheet and cell address 

    How to rename file using current date as part of file name

    •  Check cell variable (note variable #TOTAL_SALES#  name)

    How to rename file using current date as part of file name

    •  Select existing or create a new SMTP connection.

    How to rename file using current date as part of file name

    •  Update email text (note #TOTAL_SALES# variable)

    How to rename file using current date as part of file name

    •  Run the package by pressing the green arrow.

    More Information

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 0017 Delete sheet from excel files in directory

    This example shows how to delete a sheet from all excel files in a directory
    • Download and install Advanced ETL Processor [Link]
    • Download and Unzip example [Link]
    • Create a new package and open .wfp file

     Delete sheet from excel files in directory

    • Double click on "Set variables action" and change the path

    How to rename file using current date as part of file name

    •  Change file extension if necessary

    File Operation

    How to rename file using current date as part of file name

    Delete excel sheet

    Delete file

    •  Run the package by pressing the green arrow.

    More Information

    Please contact us if you need help with transforming the data

    Visit ETL Tools Forum
  • 46 Loading Data from Excel file into MySQL Database

    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, which 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

  • Advanced ETL Processor - 6.3.7.14

    Advanced ETL Processor - 6.3.7.14 is ready for the users.

    A new version of Advanced ETL Processor is out with a much more anticipated new feature: the ability to work with multiple excel sheets within a single transformation. This was not possible before. the only way was is to create a separate transformation for every single sheet and execute them sequentially.

     Excel ETL Package

    All you need to do is point writers to the same file. In the example below, "Writer C1" adds data to the C1 excel sheet and "Writer C2" adds data to the C2 excel sheet. This not only makes the life of our ETL software users easier but also improves performance. 

    Excel transformation

    Another useful feature is "Cache values" option of "Get Excel Cell Value Dynamic" transformation function. This increase performance multiple times.

    get excel cell value dynamic transformation function properties

    Other changes are:

    • Added: Extract Attachments option to IMAP4 and POP3 reader
    • Added: Email footer message
    • Added: Cache option of "Get Excel Cell Value Dynamic" transformation function
    • Improved: It is now possible to use a single transformation to write data into multiple excel sheets of the same file
    • Improved: Main screen log messages handling
    • Improved: transformation designer response time while working with very large files
    • Improved: removed excess data refreshes within transformation designer this resulted in overall screen performance improvement
    • Improved: underscore character can be seen in preview mode now
    • Improved: the software will make three attempts to publish tableau files before failing
    • Improved: Updated hyper API to the latest version
    • Fixed: Various tableau related bugs
    • Fixed: Pause package action issues
    • Fixed: Directory monitor screen bugs
    • Fixed: Fixed issue with <MessageID> package variable
    Direct link, no registration required.
  • Combining multiple data sources into a single excel file

    A new version of Advanced ETL Processor is out with a much more anticipated new feature: the ability to work with multiple excel sheets within a single transformation. This was not possible before. the only way was is to create a separate transformation for every single sheet and execute them sequentially.

     Excel ETL Package

    All you need to do is point writers to the same file. In the example below, "Writer C1" adds data to the C1 excel sheet and "Writer C2" adds data to the C2 excel sheet. This not only makes the life of our ETL software users easier but also improves performance. 

    Excel transformation

    Writer properties

    Excel writer properties

    Important notes:

    1. Point all writers to the same file
    2. Make sure that Create a new file/Add Data Into Existing File is the same for all writers 
    3. If one of the writers has selected "Recalculate Excel File" the file will be recalculated

    We would like to thank Vattikonda from https://www.cepres.com/ for providing us with useful feedback

    Direct link, no registration required.
  • Excel ETL transformation

    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, create and update Excel files automatically, the possibilities are limitless
    Download Free Trial Of Advanced ETL Processor


    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 works 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
    • Combine multiple data sources into a single excel file 
    • 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
    • Use custom format
    • Create dynamic Excel formulas

    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 the 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 various automation actions such as file operations, email processing, FTP operations, compression and even encryption. 

    Automate Excel Attachments Processing

    Contact us now!
    Related Articles
    Direct link, no registration required.
  • Generating Excel files

    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 then it will read it
    If it does not exist it will check if the 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

  • Get Excel Cell Value

    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

    Direct link, no registration required.
  • Get Excel Cell Value Data Transformation Function

    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.

    More transformation functions
  • IMEX=1

    Solving problems with loading data from Excel files into databases

    Common problem:trying to load the data from an 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

    Initializing the Microsoft Excel driver

    ODBC/MS Jet scans first TypeGuessRowsto determine field type

    Here is 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

  • Loading Data from Excel and Access Files

    Advanced ETL Processor Reader Object is capable of extracting data from MS Excel files and MS Acess databases. 
    ETL Data reader source type

    See it yourself, Download Advanced ETL Processor Enterprise now!

    Related WIKI Pages

     

     

    Direct link, no registration required.
  • Loading Excel files from emails - questions from the customer

    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 the sender email or subject and execute the 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 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 records:
      (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 the 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 a 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 the 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 it for you.

    Direct link, no registration required.
  • Rename Excel Sheets

    Based on customer feedbackin the latest version we have added a new package object ExcelOperation Action

    It allows to:

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

    Direct link, no registration required.
  • Running SQL against Excel file

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

    Data can be 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.16.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

    A very useful feature for the Excel file below when you want to retrieve the 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

    Direct link, no registration required.
    Video Tutorial
  • Say No to TypeGuessRows=0 IMEX=1 and Excel ODBC bugs

    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-2017 format
    • Can insert data starting from specific cell
    • Can clear area before adding data into Excel
    • Can add headers

    Loading Data from Excel File

    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:

    • A grouping panel was added
    • Advanced filter dialogue was introduced
    • Record number in the grid

     

     

    Direct link, no registration required.
  • SSIS Excel Problem

    Solving SSIS problems with loading data from Excel

    We have the following situation:

    Imagine that you are working for a large financial organization and would like to load the year-end balances into the 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 the 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.ACE.OLEDB.16.0;Data Source=C:\DEMO\Excel\mixed_data_types.xls;Extended Properties="EXCEL 8.0;HDR=YES";

    This can be easily corrected by adding IMEX=1

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

    Note:

    In this example, we are using Microsoft Access Engine 2016, the same applies to the older versions

    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 so you look at the data and there are some null values again.

    Why do we have nulls in the database?
  • What is complex data

    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 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 a 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

    Direct link, no registration required.
 

This site uses cookies. By continuing to browse the site, you are agreeing to our use of cookies