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:

Gebruikelijke instellingen export vanuit Calc

Character set: UTF8

Gebruik altijd UTF8 voor karaktercodering - Daarop heb ik gestandaardiseerd

Field delimiter: Tab

Ik kies ervoor om altijd een uniek veldscheidingsteken te hebben. Standaard gebruik ik Tab, want dat kom ik nooit tegen in content, in tegenstelling tot de andere gebruikelijke scheidingstekens (spatie, komma, puntkomma). Mochten al deze scheidingstekens ongeschikt zijn: In Calc kun je je eigen scheidingsteken defineren, en dat mag ook uit meerdere tekens bestaan.

Mocht het veldscheidingsteken voorkomen in de content, is er nog een andere oplossing: Met behulp van quoting van de betreffende velden, maar ik gebruik liever unieke veldscheidingstekens.

Text delimiter: "

• Het kan gebeuren dat een tekstveld het veldscheidingsteken bevat (Tab in mijn geval). In dat geval wordt een veld omhuld door het tekstscheidingsteken
• Ik streef naar een uniek scheidingsteken. Desalniettemin kan het gebeuren, dat dat scheidingsteken tóch voorkomt in de content. Dan wordt het betreffende veld omhuld door het tekstscheidingsteken - En da's heel handig
• 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.

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;

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)

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. Alle gegeveens middels Paste special... gekopiëerd naar een nieuw rekenblad, om opmaak & formules te elimineren. Vervolgens met deze parameters geëxporteerd als CSV-bestand

Zie ook

• LOAD DATA (MySQL)