Preparing the file without corrupting it

A spreadsheet application will reformat your dates, drop leading zeros and mangle long numbers, and it will do all three silently, on open, before you have touched anything. Leading zeros go first. A supplier number 000451 becomes 451, and the load either fails to match anything or creates a duplicate. Long numbers become scientific notation — a sixteen-digit bank account rendered as 1.23457E+15, which loads as a wrong account number and is not obviously wrong on screen. Dates are reinterpreted by locale: 03/04 is March the fourth or the third of April depending on the machine, and both are valid dates, so nothing errors. WHAT TO CHANGE BEFORE YOU OPEN THE FILE. Import the CSV as text rather than opening it — the import path lets you set every column to text and the open path does not ask. Where a column must stay text, say so in the load…

In this module: Module 7 · FBDI: the file-based loader end to end

1. 1The FBDI sequence, and why order mattersFree preview
2. 2Finding and reading the right template
3. 3Mandatory columns, and the ones that only look optional
4. 4Preparing the file without corrupting it
5. 5Loading the interface tables
6. 6Running the import and reading the output
7. 7Errors: finding the row that failed
8. 8Correcting and reloading safely
9. 9Master data versus transactional loads
10. 10Volume: loading a hundred thousand rows
11. 11Proving the load landed
12. 12Rollback, and why there usually is not one
13. 13What breaks: six FBDI failures
14. 14Lab briefing · A supplier and invoice load