Extract-transform-load (ETL) moves data from a source into a usable destination. Extraction reads and parses data, transformation cleans or reorganizes it, and loading stores the result in another file or database. section 21.1
An ETL workflow typically involves:
- Extracting data by opening files, reading their contents, and parsing records; very large files may require line-by-line or segmented processing. section 21.2
- Transforming data by cleaning, normalizing, combining, splitting, sorting, or reorganizing records into a consistent format. section 21.1 section 21.7
- Loading the transformed data by writing a specified file or storing it in a database for people, applications, or analysis. section 21.5
Cleaning a delimited data file
A Python workflow can read a text or CSV file, address currency symbols, punctuation, null characters, encoding issues, and inconsistent values, then sort the cleaned records and write them in a format required by another application. section 21.7
Text files vary widely: records may use commas, tabs, pipes, or other delimiters, and datasets may be incomplete, dirty, or spread across many files. UTF-8 is a common choice for handling a wide range of characters, while CSV files are best handled with Python’s csv module. section 21.0 section 21.7
