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 setup to run automatically / semi-automatically? i.e. when a particular supplier provides their new Excel sheet can the previous loading definition setup for this supplier be accessed and used to load data via some scheduling?

    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 are 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 validator to filter records header/footer records out

    Is Equal Properties
  • 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 validator to filter records out
    Validation Editor
  • 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 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 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 setup to be ignored? e.g. text to specify start of footer or certain number of blank rows in worksheet to define point at which import stops?

    Answer:
    Sure, use 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 transformation object.
  • As a variation to the above #9, can the timestamp come from different locations depending upon a 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 time stamp 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" as times?

    Answer:
    Yes, you can achieve this by using 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 achivie and we would be happy to assist you in difficult cases. If some fuctionality is missing we will add for you.

Visit Support Forum Learn More Download Buy It

I am continually Amazed by the power and versatility of the ETL tool and the Support Team.

Thank You!

http://www.dbsoftlab.com/advanced-etl-processor/2475-de-duplication-of-addresses.html

Our customers

BP

BBC

HSBC

Orange

CAT

SEPA
emc
NHS
Australian Rugby Union
xerox
Databases we work with