Dear Sir/Madam
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:
1. 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?
2. Can it take data from different worksheets within a single Excel file and join to load into MySQL tables?
3. 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?
4. 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
5. 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)
6. 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)
7. 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)
8. 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?
9. 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?
10. 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.
11. 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?
12. Is any modification/preparation of the Excel file required before your product can process it?
Many thanks for your assistance.
best regards
George
