Data Integration, Import/Export, and Validation
This domain tests moving data in and out cleanly and stopping bad data at entry. Expect questions about delimiters, encoding, type inference during import, refreshable connections, and the limits of data validation.
Importing structured text
- CSV is plain text: fields are separated by a delimiter (comma in many locales, semicolon in others) and optionally wrapped in a text qualifier such as double quotes so delimiters inside a field are safe.
- Delimited files split on a character; fixed-width files split at defined column positions.
- Encoding matters: UTF-8 preserves accented and non-Latin characters, while legacy ANSI code pages can produce garbled text.
- Type inference can be destructive. A ZIP code of 01234 imported as a number loses its leading zero, and ambiguous dates such as 03/04/2025 may swap day and month depending on locale.
Connections and refreshable queries
Importing through a query editor (for example Excel's Get Data) records each transformation as an ordered step. Refreshing re-runs the steps against the source, so the workbook updates without manual re-import. This differs from pasting values, which is a one-time snapshot.
Exporting
CSV export keeps values but drops formulas, number formats, cell colours, multiple sheets, and charts. Only the active sheet is written. Database and PDF exports preserve different subsets, so the format must be chosen for the consumer.
Validation rules
Data Validation restricts what can be typed: a list, a whole-number or decimal range, a date range, or a custom formula. An input message guides the user and an error alert can Stop, Warn, or merely Inform. Validation applies only to new typing in the specified range; pasted values, imported rows, and formula results bypass it.
Worked example
A staff list imported from CSV shows 00123 turned into 123, and phone numbers stripped of leading zeros. Reasoning: the importer inferred a numeric type. The fix is to set that column to Text during import, not to reformat after the fact, because the zeros are already gone.
Traps that cost marks
- Assuming validation blocks pasted data.
- Trusting default import types for codes, IDs, and dates.
- Expecting formatting or formulas to survive a CSV export.