MySQL Workbench and LOAD DATA can be used to quickly load a large number of records from a *CSV file to a MySQL database hosted on a Local or Remote Server.
Important !
The CSV file data structure must match the number of fields in the table and the type of data in each field. The data in each column must be in exactly the same order as the fields.
If the file and table structures are not matched data will be placed in the wrong fields.
Using the Parts Frontend application to Load Data
Parts includes options to delete and replace all records in the destination database or to append new records and update records with matching primary keys.
The Parts Frontend will only Load Data to MySQL if the Parts Table and *.CSV file structures are compatible.
Parts will create a Heading.txt file that can be used to compare the headings (fields) in the source data and destination table. The first column is the headings (fields) in the source data (*.csv file) and the second column is the destination table's fields.
A side by side comparison of the Imported and Destination fields can be made by selecting Display Unsorted Fields (Parameters) in the Import Dialog.
Tip: Drag and Drop the Headings.txt file in to an open session of Excel. Add a third column and fill the column with a relative cell formula =IF(A1=B1,"Match","Mismatch") to find the differences between the data structure in the file and the destination table.
=IF(A1=B1,"Match","Mismatch")
Use MySQL Workbench to enable Loading Data from a Local *.CSV file.
SHOW VARIABLES LIKE "local_infile";
Example:
SET GLOBAL local_infile = 1;
SET GLOBAL local_infile = 1;
After setting local_infile variable you can close the MySQL Workbench and use Parts > Tools > Load Data MySQL or Import Excel to quickly Load Data into MySQL.
Or you can use MySQL Workbench to Load Data . . .
Using the Parts Frontend to LOAD DATA is simpler if GLOBAL local_infile = 1
C:\Program Files\MySQL\MySQL Workbench 8.0 CE\MySQLWorkbench.exe
To LOAD DATA using the MySQL Workbench, edit the connection (click the wrench icon by the MySQL Connections, or right-click on a particular connection and choose Edit Connection...). On the Advanced tab, in the "Others:" box, add the line OPT_LOCAL_INFILE=1
Use a full path to the *.csv file that is stored on a Local machine.
Use forward slashes ( / ) as shown in the examples below.
Tip: A good way to remember the difference between a backslash and a forward slash is that a backslash leans backwards ( \ ), while a forward slash leans forward ( / )
Example for loading the parts table.
TRUNCATE parts.parts
LOAD DATA LOCAL INFILE "C:/temp/parts.csv" INTO TABLE parts
FIELDS TERMINATED BY '|'
FIELDS TERMINATED BY '|'
ENCLOSED BY '"'
ESCAPED BY ''
IGNORE 1 LINES;
IGNORE 1 LINES;
Example for loading the mfr_links table.
TRUNCATE mfr_links.mfr_links
LOAD DATA LOCAL INFILE "C:/temp/mfr_links .csv" INTO TABLE mfr_links
FIELDS TERMINATED BY '|'
FIELDS TERMINATED BY '|'
ENCLOSED BY '"'
ESCAPED BY ''
IGNORE 1 LINES;
IGNORE 1 LINES;
Example for loading the supplier_links table.
TRUNCATE supplier_links .supplier_links
LOAD DATA LOCAL INFILE "C:/temp/supplier_links .csv" INTO TABLE supplier_links
FIELDS TERMINATED BY '|'
FIELDS TERMINATED BY '|'
ENCLOSED BY '"'
ESCAPED BY ''
IGNORE 1 LINES;
IGNORE 1 LINES;
Tips:
ESCAPE BY '' uses two single quote characters.
ENCLOSED BY ' " ' uses a single, a double and a single quote character.
To permit the Workbench access to Local Files, edit the connection (click the wrench icon by the MySQL Connections, or right-click on a particular connection and choose Edit Connection...). On the Advanced tab, in the "Others:" box, add the line OPT_LOCAL_INFILE=1
Source:
Useful Links:
LOAD DATA Statement - MySQL 8.0 Reference Manual
Importing data into a MySQL DB instance - Amazon AWS
MySQL database log files - Amazon AWS
Connect-with-local-infile-option-in-mysql-workbench - Stack OverflowThat's it !
No comments:
Post a Comment