Opmaak tekstbestanden voor import
Regelmatig krijg ik gegevens aangeleverd in spreadsheet-formaat, die ik importeer in een MySQL-datawarehouse. Handig om deze import zo consistent mogelijk te doen. Daar gaat dit artikel over:
Export vanuit Calc
Meestal gebruik ik LibreOffice Calc als middleware om de aangeleverde data (meestal Excel-sheets, soms Calc-sheets) te converteren naar csv- of tsv-formaat. De export-instellingen in Calc zijn daarom de crux van dit artikel:
Karaktercodering - Character set: UTF8
Gebruik altijd UTF8 voor karaktercodering - Daarop heb ik gestandaardiseerd
Veldscheidingsteken - Field delimiter: Tab
- Het veldscheidingsteken moet zo uniek mogelijk zijn. Tab scoort wat dat betreft hoog, want dat ik geloof dat ik dat nooit tegenkom in bronbestanden. Daarnaast maakt tabs de data iets leesbaarder
- Mocht het veldscheidingsteken onverhoopt tóch voorkomen in de brondata, dan is er nog geen man overboord: Daarvoor heb je het tekstscheidingsteken: Het veld wordt omsloten met dit teken, zodat het bij de import duidelijk is, dat in dit geval het veldscheidingsteken onderdeel van het veld is - Dit is een robuuste oplossing en je bent zodoende in één keer klaar
- Mocht je om wat voor reden dan ook, per se een uniek veldscheidingsteken willen hebben, dan kan dat: Je kunt namelijk je eigen teken defineren, en dat mag uit meerdere karakters bestaan.
Tekstscheidingsteken - Text delimiter: "
- Zie de uitleg hierboven bij veldscheidingsteken. Ik heb gestandaardiseerd op " - het dubbele aanhalingsteken
- Bij import mbv. LOAD DATA daarom specificeren OPTIONALLY TERMINATED BY '"' .
Save cell content as shown: Nee
De optie Save cell content as shown zorgt ervoor dat gegevens worden weggeschreven zoals weergegeven. Dus inclusief komma of punt als decimaalscheidingsteken zoals weergegeven, en wellicht voor numerieke velden met het aantal significante cijfers zoals weergeven → Uitvinken.
Uitleg Calc:
Enabled by default, data will be saved as displayed, including applied number formats. If this checkbox is not marked, raw data content will be saved, as in older versions of the software. Depending on the number format, saving cell content as shown may write values that during an import cannot be interpreted as numerical values anymore.
Helaas wil dit niet automatisch zeggen dat komma's worden vervangen door punten, in numerieke velden.
Save cell formulas instead of calculated values: Nee
Deze optie heb ik nog nooit nodig gehad.
Quote all text cells: Nee
Niet doen, want dan heb je geen uitweikmogelijkheid meer, indien het veldscheidingsteken onverhoopt toch voorkomt in de content.
Import mbv. LOAD DATA
De instellingen hierboven voor export met Calc, komen overeen met deze instellingen voor import in MySQL (dit is een voorbeeld):
load data local infile "/tmp/tmp-03.csv" into table `201812_flames_imp` character set utf8 fields terminated by '\t' optionally enclosed by '"' lines terminated by '\n' ignore 1 rows; # De eerste regel bevat headings, en kan genegeerd worden - Dit wordt niet verder hier behandeld
Importeren in XML-formaat?
Calc kan gegevens exporteren in een plat XML-formaat en MySQL kan platte XML importeren. Dat klinkt interessant, maar het vereist waarschijnlijk een hoop werk (XML-transformaties) om die data geschikt te maken voor import: Alle opmaak-info wordt namelijk ook in datzelfde XML-bestand gespecificeerd
Importeren in native Calc-formaat?
Als ik overweeg om gegevens uit Calc in XML-formaat te importeren, kan ik 't net zo goed in Calc's native opmaak importeren: Da's ook XML. Het scheelt gedoe met samenvoegen van sheets (indien relevant). Weer: Waarschijnlijk veel werk om dat goed te krijgen.
Casus: Productgegevens in Calc (okt. 2018)
Instellingen LibreOffice Calc
- Character set: UTF8
- Field delimeter: {Tab}
- Text-delimeter: Geen
Opmerkingen
- Extensie wordt vanzelf csv, ook al betreft het eigenlijk een tsv-bestand: Calc plaatst er altijd .csv achter
- Als je wel een text-delimeter gebruikt, en dat karakter komt voor in de bestand (bijvoorbeeld "), dan wordt dat geëscaped ofzo - Vragen om problemen!
- Cijfers met voorloopnullen, worden geïmporteerd als text - Precies wat de bedoeling is (bv. een artikelnummer zoals '0100' moet '0100' blijven, en niet het getal '100' worden)
Import MySQL
Bijbehorende instellingen voor import in MySQL:
load data local infile "/tmp/02.csv" into table import_tmp character set utf8 fields terminated by '\t' enclosed by '' lines terminated by '\n' ignore 1 rows; # Afhankelijk van de inhoud van het bestand, niet de opmaak