Databestanden importeren (MySQL)

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.

Vanuit Excel

Vaak krijg ik data aangeleverd in Excel-formaat. Je weet wel, data nare semi-gesloten format. Hoe daar een mouw aan te passen?

Meestal volg ik de laatste methode hieronder: Ik open het bestand in LibreOffice Calc, en schrijf het weg in ods-formaat. Voilà: Geen Excel meer in the picture.

Add-ons & losse programma'tjes

• MySQL for Excel-addon: Er bestaat een add-on voor Excel om te exporteren naar MySQL [1]. Daar kan ik weinig mee
• Excel2MySQL: Een andere (gratis?) standalone routine, vermoedelijk bedoelt voor eindgebruikers: http://excel2mysql.net/ - Sla ik over
• RazorSQL MySQL Import Tool: http://www.razorsql.com/features/mysql_import.html - Ziet er aardig uit
• SQLyog: Nog een import add-on.

Indirect

• OpenRefine: Veelbelovend, maar geeft bij mij storingen. Het idee zou zijn: Laden in OpenRefine » Extraction » Wegschrijven als tsv-bestand
• CSV: Wegschrijven in CSV-formaat, en dan op een andere manier importeren
• 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

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 [2]

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!)

Voorbeeld (2) - Amazon inventarisbestand

###############################################################
# Create tmp input table
###############################################################
#
drop table if exists import_tmp;

CREATE TABLE `dwh`.`import_tmp` (
  `sku` VARCHAR(255) NOT NULL,
  `asin` VARCHAR(255) NULL,
  `name` VARCHAR(500) NULL,
  `description` VARCHAR(2000) NULL,
  PRIMARY KEY (`sku`))
  default character set = utf8;

###############################################################
# Import table
###############################################################
#
load data local infile "/var/export/01.csv"
into table import_tmp
CHARACTER SET UTF8			# Belangrijk!
fields terminated by '\t'
# enclosed by '"'		
lines terminated by "\n"
ignore 1 rows;

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 [3]. 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
• Mbv. commando load_file [4] (nieuw!)

Lezen & schrijven van bestanden

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

Tabbladen in bronsbestand samenvoegen

Zie LibreOffice Basic.

Bronnen

• http://www.mysqltutorial.org/import-csv-file-mysql-table/
• https://dev.mysql.com/doc/mysql-for-excel/en/mysql-for-excel-export.html
• http://stackoverflow.com/questions/1310166/how-to-import-an-excel-file-in-to-a-mysql-database
• http://forums.mysql.com/read.php?32,216343,216344#msg-216344 - MySQL Forums :: Data Warehouse :: import excel file into mysql table
• http://excel2mysql.net/
• https://www.quora.com/How-can-I-import-Excel-data-into-MySQL - Behandelt verschillende methodes

Merge Calc sheets

• https://ask.libreoffice.org/en/question/38010/merge-multiple-spreadsheets-files-into-one/
• http://superuser.com/questions/304899/how-can-i-merge-hundreds-of-excel-spreadsheet-files - Voorbeeld VBA-code in Excel
• https://ask.libreoffice.org/en/question/31401/calc-how-to-combine-multiple-tables-in-different-sheets-into-a-new-one/ → Aardige oplossing
• https://ask.libreoffice.org/en/question/16604/how-do-i-access-the-current-sheet-name-in-formula-to-use-in-indirect/ - Read name current sheet

Automate in Calc

• http://www.debugpoint.com/2014/09/libreoffice-workbook-worksheet-and-cell-processing-using-macro/

Import MySQL

• https://dev.mysql.com/doc/refman/5.7/en/load-data.html

Beperkingen stored procedures

• https://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html