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:
Algemeen
- Meestal gebruik ik LibreOffice Calc als middleware om de aangeleverde data (meestal Excel-sheets, soms Calc-sheets) te converteren naar csv- of tsv-formaat
- Gebruik altijd UTF-8 voor karaktercodering
- Import in MySQL gaat middels SQL-scripts - Het LOAD DATA-commando kan niet uitgevoerd worden vanuit sprocs. Wellicht dat Python of LOAD_DATA interessante alternatieven zijn
- Gebruik geen text delimeter, omdat veldtypes expliciet worden gedefineerd in het SQL-importscript
- TAB lijkt het meest universeel geschikte veldscheidingsteken te zijn: Ik kom waarschijnlijk nooit TAB tegen in content die ik importeer.
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
Casus: Productgegevens in Excel voor toewijzing EAN-codes (dec. 2018)
- Bestand geopend in LibreOffice Calc. Ik kopiëer alle gegevens
- In een nieuw rekenblad plak ik de gegevens middels Paste special... om opmaak en formules te elimineren