LOAD DATA (MySQL)
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
. Vantevoren moet je de doeltabel defineren, inclusief velden met passende dimensies [1].
LOAD DATA
werkt niet vanuit een sproc; alleen vanuit een gewoon SQL-bestand - Heel onhandig. LOAD_FILE
zou wél werken vanuit sprocs, maar heb ik nog niet aan de praat gekregen. Zie Databestand importeren (MySQL) voor details.
Meestal zoek ik zoiets
Dit bestreft een SQL-script, niet een sproc. Meestal een paar keer doorlopen om de dimensies goed te krijgen.
############################################################################################################### # Import ############################################################################################################### # # Drop existing tmp table ######################### # drop table if exists tmp; # Create new tmp table ######################### # create table tmp ( entity varchar(255), category varchar(40), description text ); # Import table ######################## # load data local infile '/tmp/tmp.csv' into table tmp character set utf8 fields terminated by '\t' optionally enclosed by '"' # Belangrijk! lines terminated by '\n' ignore 1 rows; # Check ######################## # select * from tmp;
Niet vanuit een sproc
Als je LOAD DATA in een sproc gebruikt, krijg je foutmelding: ERROR 1314: LOAD DATA is not allowed in stored procedures
. Iets vergelijkbaars gebeurt er, als je de naam in een variable opslaat met zoiets 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
- Uitvoeren als gewone SQL-code vanuit een script
- Via een externe omgeving (cron, shell, php, Pyton, ...) SQL-code genereren. Die schrijf je naar een bestand, en daarna executeer je dat bestand. Voorbeeld
- Mbv. commando
load_file
[3] (nieuw!)
Beperking tav. mappen
Je kunt niet zomaar een willekeurig bestand inlezen in MySQL: Er zijn oa. beperkingen welke mappen MySQL kan benaderen
Syntaxis
Je kunt veel specificeren [4], [5]. Met bijna elke optie op een aparte regel, krijg je bv. zoiets:
LOAD DATA LOCAL INFILE 'file' IGNORE INTO TABLE 'table' CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' [OPTIONALLY] ENCLOSED BY '"' ESCAPED BY '\\' LINES STARTING BY '' # Alleen regels die met deze tekenreeks beginnen, worden geïmporteerd TERMINATED BY '\n' IGNORE 'number' [{LINES | ROWS}] [(col_name_or_user_var # Kun je hiermee on-the-fly de doeltabel defineren? [, col_name_or_user_var] ...)] [SET col_name={expr | DEFAULT}, [, col_name={expr | DEFAULT}] ...]
Eenvoudiger voorbeeld:
load data local infile "/var/export/import_file.csv" into table import_tmp fields terminated by '\t' -- Tab als veldscheidingsteken optionally enclosed by '"' lines terminated by "\n" -- Alle records zijn afgesloten met een \n ignore 1 rows; -- Regel 1 bevat veldnamen en worden overgeslagen (gebruiken bij testen!)
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 optionally enclosed by '"' 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 - 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' optionally enclosed by '"' lines terminated by "\n" ignore 1 rows;
CR/LF
Zie ook
- Casussen (tekencodering)
- Databestand importeren (MySQL) - Inclusief
LOAD_FILE
- Opmaak tekstbestanden
- Practitioner-plugin (WordPress)
- Upload-rapportages importeren (Amazon)
- Veldnamen bij import
Bronnen
- http://www.mysqltutorial.org/import-csv-file-mysql-table/
- http://stackoverflow.com/questions/1310166/how-to-import-an-excel-file-in-to-a-mysql-database
- https://dev.mysql.com/doc/refman/5.7/en/load-data.html
- https://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html
- https://www.morpheusdata.com/blog/2014-12-21-the-fastest-way-to-import-text-xml-and-csv-files-into-mysql-tables