Awesome
TLE
TLE is a helper for data extract-transform-load (ETL)
Why This
TLE lets you programmatically transform spreadsheet data (or MySQL database) by remapping columns and preparing data (eg changing case-formatting) for consumption by downstream systems.
Originally developed pro bono for syncing ipcLink system used by Singapore non-profits to MYOB accounting system. For open-source ETL tools that are based on GUI, consider using Pentaho Data Integration or Talend Open Studio.
Set Up
Create a transformation mapping file with 2 rows, for eg
| Name | User ID | |
|---|---|---|
| B | LOWER(C) | UPPER(F) |
- first row labelling the header fields desired in the output
- second row specifying which column in input to grab data
- use UPPER, LOWER, TITLE, SENTENCE for formatting case
For database input, provide input file in below format for script to read from database
| TLE_COMMENTS | DB_SERVER | DB_USER | DB_PASSWORD | DB_NAME | DB_TABLE |
|---|---|---|---|---|---|
| user comments | servername | username | password | database | tablename |
To Use
To transform input.csv using mapping.csv logic into output.csv (supported data formats - csv xls xlsx html)
php etl_start.php input.csv mapping.csv output.csv
To call TLE within a PHP script, simply assign the variables accordingly and include etl_start.php
$argv[1] = "input.csv"; $argv[2] = "mapping.csv"; $argv[3] = "output.csv"; include('etl_start.php');
License
TLE is open-source software released under the MIT license