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 XML-formaat. 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)

Deze instellingen werken goed. Er wordt geen gebruik gemaakt van text delimiters, omdat datatypes expliciet worden gedefineerd in het import-script

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

Zie ook

LOAD DATA (MySQL)