Tuesday, September 29, 2020

LOAD DATA for MySQL

Load Data can be used to send bulk records from a CSV file to a MySQL table.

The first step is to dump the database records to a utf-8 *.CSV file.

sql - exporting text file with utf-8 encoding in ms access - Stack Overflow

Then use MySQL Load Data. . .

What is LOAD DATA and how does it work | MySQL Server Blog

For Load Data Errors see:

MySQL LOAD DATA LOCAL INFILE on WINDOWS not WORKING - Stack Overflow

Useful MySQL Workbench Query Statements













SET GLOBAL local_infile = 1;
show variables like '%secure_file_priv%';
show variables like 'local_infile';

Pipes are safer than commas or semi-colons

Remote Server
LOAD DATA INFILE 'file'
IGNORE INTO TABLE table
CHARACTER SET UTF8
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Local Server

LOAD DATA LOCAL INFILE "file" INTO TABLE tblName CHARACTER SET UTF8 COLUMNS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"';

Other options not shown above include:  Ignore duplicates.

That's it !

No comments: