Databestand importeren (MySQL)

Uit De Vliegende Brigade
Ga naar: navigatie, zoeken

PMA

PHPMyAdmin heeft een hele aardige import-functie. Wat daar zo aardig aan is: Er wordt dynamisch een tabel aangemaakt van (meestal) passende dimensies.

Maar zoals nix in deze wereld perfect is, is ook dit dat niet:

  • PMA is niet altijd beschikbaar
  • Time out-storingen
  • Onduidelijke foutmeldingen.

In zulke gevallen is 't waarschijnlijk handiger om rechtstreeks mbv. SQL te importeren.

SQL

De standaardmanier om rekenbladen te importeren, is door deze in CSV-formaat weg te schrijven en te laden met behulp van het commando LOAD DATA INFILE. Vantevoren moet je de doeltabel defineren, inclusief velden met passende dimensies [1]

Eenvoudig voorbeeld

drop table if exists ean_code;
CREATE TABLE `webwinkels`.`ean_code` (
  `ean_id` VARCHAR(13) NOT NULL,
  `sku` VARCHAR(45) NULL,
  PRIMARY KEY (`ean_id`));

load data local infile "/var/mysql-export/ean01.txt"
into table ean_code

Voorbeeld - Amazon export-bestand importeren

drop table if exists import_tmp;

create table import_tmp
(
	item_sku varchar(45) null,
	external_product_id varchar(45) null,
	external_product_id_type varchar(45) null,
	item_name varchar(1000) null,
	bullet_point1 varchar(500) null,
	bullet_point2 varchar(500) null,
	bullet_point3 varchar(500) null,
	bullet_point4 varchar(500) null,
	bullet_point5 varchar(500) null,
	generic_keywords1 varchar(1010) null,
	generic_keywords2 varchar(1010) null,
	generic_keywords3 varchar(1010) null,
	generic_keywords4 varchar(1010) null,
	generic_keywords5 varchar(1010) null,
	description varchar(1000) null,
	recommended_browse_nodes varchar(45) null,
	manufacturer varchar(45) null,
	feed_product_type varchar(45) null,
	brand_name varchar(45) null,
	update_delete varchar(45) null,
	standard_price varchar(45) null,
	quantity varchar(45) null,
	fulfillment_latency varchar(45) null,
	condition_type varchar(45) null,
	number_of_items varchar(45) null,
	main_image_url varchar(100) null,
	other_image_url1 varchar(100) null,
	other_image_url2 varchar(100) null,
	other_image_url3 varchar(100) null,
	other_image_url4 varchar(100) null,
	other_image_url5 varchar(100) null,
	website_shipping_weight varchar(45) null,
	website_shipping_weight_unit_of_measure varchar(45) null,
	item_length varchar(45) null,
	item_width varchar(45) null,
	item_height varchar(45) null,
	item_dimensions_unit_of_measure varchar(45) null,
	item_weight varchar(45) null,
	item_weight_unit_of_measure varchar(45) null,
	target_audience_keywords varchar(45) null,
	package_height varchar(45) null,
	package_height_unit_of_measure varchar(45) null,
	package_width varchar(45) null,
	package_width_unit_of_measure varchar(45) null,
	package_length varchar(45) null,
	package_length_unit_of_measure varchar(45) null,
	package_weight varchar(45) null,
	package_weight_unit_of_measure varchar(45) null,
	color_name varchar(45) null,
	color_map varchar(45) null,
	material_type varchar(45) null,
	specific_uses_for_product varchar(45) null,
	internal_sort_order varchar(5) null
);

load data local infile "/var/export/eanbosch.csv"
into table import_tmp
fields terminated by '\t'	-- Tab als veldscheidingsteken
enclosed by '"'			-- Alle velden zijn omsloten met dubbele aanhalingstekens
lines terminated by "\n"	-- Alle records zijn afgesloten met een \n
ignore 1 rows;			-- Regel 1 bevat veldnamen en worden overgeslagen (gebruiken bij testen!)

Niet importeren vanuit een sproc of met een naam-in-een-variabele

Helaas: Als je LOAD DATA in een sproc gebruikt, krijg je een foutmelding: ERROR 1314: LOAD DATA is not allowed in stored procedures. Iets vergelijkbaar gebeurt er, als je de naam in een variable opslaat met zoies als load data local infile @input_bestand into table import_tmp;

Importeren in een sproc schijnt niet te mogen vanwege security-issues [2]. Inladen mbv. van een variabele schijnt niet te kunnen vanwege server-sided vs. locale executie Deze pagina.

Oplossingen

  • Als statische sql-code uitvoeren
  • Via een externe omgeving (cron, shell, php, Pyton, ...). Hierbij genereer je de betreffende SQL-code. Die schrijf je naar een bestand, en daarna executeer je dat bestand. Voorbeeld.

Lezen & schrijven van bestanden

Je kunt niet zomaar een willekeurig bestand inlezen in MySQL: Er zijn oa. beperkingen welke mappen MySQL kan benaderen

Excel-bestanden importeren

Add-ons & losse routines

Overig

  • MySQL Workbench:
  • LOAD INTO: De gebruikelijke manier is door de data weg te schrijven in CSV-formaat en daarna te laden. Zie elders voor details
  • INSERT INTO: Open het CSV-bestand en voeg 'INSERT INTO'-commando's in. Veel werk, maar bij kleine hoeveelheden data soms een prima oplossing.

Indirect

  • OpenRefine: Veelbelovend, maar geeft bij mij storingen. Het idee zou zijn: Laden in OpenRefine » Extraction » Wegschrijven als tsv-bestand
  • LibreOffice Basic: Bestand opslaan als ods-bestand » Voorbereidend werk doen mbv. macro's (ihb.: Tabbladen samenvoegen) » Wegschrijven als tsv-bestand » Laden met een eigen LOAD INTO-routine in MySQL

Deze laatste optie lijkt nogal ver gezocht, maar is (okt. 2016) de manier waarop ik dit daadwerkelijk doe.

Tabbladen in bronsbestand samenvoegen

Zie LibreOffice Basic.

Bronnen

Merge Calc sheets

Automate in Calc

Import MySQL

Beperkingen stored procedures