Opmaak tekstbestanden voor import

Uit De Vliegende Brigade
Ga naar: navigatie, zoeken
Zo dus

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:

Nog een keer - Zo dus

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.

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

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