Loading Excel files from emails

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.
  
• 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
  
  
  

  
• 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
  
• 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.
  
  
• 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
  
• 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.
  
  
• 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.
  
  
• 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.