How to Load a MySQL Infile
Contents
Overview
This document is intended to help insert a
CSV file into a Database. The CSV files
that are exported into Excel Spreadsheet can generally have garbage characters
and special characters that should be filtered in Excel, like & , “ ‘
etc. Then there are hidden characters
and International Characters that need to deleted and or renamed.
Setup
Create a directory on your computer like /home/data/jira/DISDB-1000.
Backup the table you will be inserting into,
you will need that later:
root@hostname:~/jira/DISDB-1000$ mysqldump
yourdb execs > execs_dump.sql
root@hostname:~/jira/DISDB-1000$ ls -ltr
total 10336
-rwx------ 1 root root 202 Jan 29 14:14 load_infile.sql
-rw-rw-r-- 1 root root 75174 Jan 29 14:14 execs_29jan2015.csv.orig
-rw-rw-r-- 1 root root 10424303 Jan
29 14:18 execs_dump.sql
-rw-rw-r-- 1 root root 75114 Jan 29 14:40 execs__29jan2015.csv
root@hostname:~/jira/DISDB-1000/29jan2015$
root@hostname:~/jira/DISDB-1000/29jan2015$
cat load_infile.sql
load data local infile '/home/wmayall/jira/DISDB-1000/29jan2015/execs_29jan2015.csv'
into table execs fields terminated by ',' enclosed by '"' lines terminated by '\n'
(co_id,Name,Title,url);
root@hostname:~/jira/DISDB-1000/29jan2015$
root@hostname:~/jira/DISDB-1000/29jan2015$
root@hostname:~/jira/DISDB-1000/29jan2015$
head robert_osgood_29jan2015.csv.orig
Co_id,Name,Title,Executive URL
18777,Robert S,Chief Executive
Officer,http://www.your_site.com/about-us/team
18777,James S,Chief Information
Officer,http://www.your_site.com/about-us/team
18777,Jeffrey W,Chief Commercial
Officer,http://www.your_site.com/about-us/team
root@hostname:~/jira/DISDB-1000/29jan2015$
cat load_infile.sql
root@hostname:~/jira/DISDB-1000/29jan2015$
mysql -A
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 566 Server version:
5.6.16-1+sury.org~precise+1-log (Ubuntu)
Copyright (c) 2000, 2014, Oracle and/or its
affiliates. All rights reserved. Oracle is a registered trademark of Oracle
Corporation and/or its affiliates. Other names may be trademarks
of their respective owners. Type 'help;' or '\h' for help. Type '\c' to
clear the current input statement.
mysql> use your_db;
Database changed
mysql> select count(*) from execs;
+----------+
| count(*) |
+----------+
|
78450 |
+----------+
1 row in set (0.00 sec)
mysql> \! ls -ltr
total 10336
-rwx------ 1 root root 202 Jan 29 14:14 load_infile.sql
-rw-rw-r-- 1 root root 75174 Jan 29 14:14 execs_29jan2015.csv.orig
-rw-rw-r-- 1 root root 75130 Jan 29 14:17 execs_29jan2015.csv
-rw-rw-r-- 1root root 10424303 Jan
29 14:18 execs_dump.sql
mysql> show global variables like
'host%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| host_cache_size | 678 |
| hostname | lxws2 |
+-----------------+-------+
2 rows in set (0.00 sec)
mysql> \. load_infile.sql
Query OK, 654 rows affected, 37 warnings
(0.17 sec)
Records: 665 Deleted: 0
Skipped: 11 Warnings: 37
mysql> show warnings;
+---------+------+----------------------------------------------------------------------+
| Level
| Code | Message
|
+---------+------+----------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value:
'\xA0' for column 'Name' at row 233
|
| Warning | 1366 | Incorrect string value:
'\xA0' for column 'Name' at row 234
|
| Warning | 1366 | Incorrect string value:
'\xA0' for column 'Name' at row 242
|
| Warning | 1366 | Incorrect string value:
'\xA0NBCUn...' for column 'Title' at row 243 |
| Warning | 1366 | Incorrect string value:
'\xA0NBCUn...' for column 'Title' at row 244 |
| Warning | 1366 | Incorrect string value:
'\xA0NBCUn...' for column 'Title' at row 245 |
| Warning | 1366 | Incorrect string value:
'\xA0NBCUn...' for column 'Title' at row 246 |
| Warning | 1366 | Incorrect string value:
'\xA0NBC E...' for column 'Title' at row 247 |
| Warning | 1366 | Incorrect string value:
'\xA0NBCUn...' for column 'Title' at row 248 |
| Warning | 1366 | Incorrect string value:
'\xA0NBCUn...' for column 'Title' at row 249 |
| Warning | 1366 | Incorrect string value:
'\xA0NBCUn...' for column 'Title' at row 250 |
| Warning | 1366 | Incorrect string value:
'\xA0CNBC' for column 'Title' at row 251
|
| Warning | 1366 | Incorrect string value:
'\xA0NBCUn...' for column 'Title' at row 252 |
| Warning | 1366 | Incorrect string value:
'\xA0NBC S...' for column 'Title' at row 253 |
| Warning | 1366 | Incorrect string value:
'\xA0NBCUn...' for column 'Title' at row 254 |
| Warning | 1366 | Incorrect string value:
'\xA0NBCUn...' for column 'Title' at row 255 |
| Warning | 1366 | Incorrect string value:
'\xA0NBCUn...' for column 'Title' at row 256 |
| Warning | 1366 | Incorrect string value:
'\xA0NBCUn...' for column 'Title' at row 257 |
| Warning | 1366 | Incorrect string value:
'\xA0NBCUn...' for column 'Title' at row 258 |
| Warning | 1366 | Incorrect string value:
'\xA0NBCUn...' for column 'Title' at row 259 |
| Warning | 1366 | Incorrect string value:
'\xA0Unive...' for column 'Title' at row 260 |
| Warning | 1366 | Incorrect string value:
'\xA0NBCUn...' for column 'Title' at row 261 |
| Warning | 1366 | Incorrect string value: '\xA0Unive...'
for column 'Title' at row 262 |
| Warning | 1366 | Incorrect string value:
'\xA0NBCUn...' for column 'Title' at row 263 |
| Warning | 1366 | Incorrect string value:
'\x92s bus...' for column 'Title' at row 310 |
| Warning | 1366 | Incorrect string value:
'\xA0' for column 'Title' at row 360
|
| Warning | 1366 | Incorrect string value:
'\xA0' for column 'Title' at row 385
|
| Warning | 1366 | Incorrect string value:
'\xE9 Gonz...' for column 'Name' at row 400
|
| Warning | 1366 | Incorrect string value:
'\xF1a' for column 'Name' at row 411
|
| Warning | 1366 | Incorrect string value:
'\xA0Drill...' for column 'Title' at row 412 |
| Warning | 1366 | Incorrect string value:
'\xA0Drill...' for column 'Title' at row 413 |
| Warning | 1366 | Incorrect string value:
'\xE9s M. ...' for column 'Name' at row 462
|
| Warning | 1366 | Incorrect string value:
'\xA0' for column 'Name' at row 469
|
| Warning | 1366 | Incorrect string value:
'\xA0' for column 'Title' at row 469
|
| Warning | 1366 | Incorrect string value:
'\xA0' for column 'Name' at row 473
|
| Warning | 1366 | Incorrect string value:
'\xA0' for column 'Title' at row 597
|
| Warning | 1366 | Incorrect string value:
'\xA0' for column 'Title' at row 622
|
+---------+------+----------------------------------------------------------------------+
37 rows in set (0.00 sec)
mysql>
Fixing the Warnings
Notice the first execution produced 37
warnings, so if you haven’t figured it out yet, first execute on a non
Production database until you have warnings
fixed. The skipped means there were duplicates.
The warnings
are helpful, but don’t tell you the exact problem. Try to fix a problems to see if you are on
the right track, then restore the execs table with the backup you made. Then execute the load_infile.sql scripts.
Repeat until the warnings are
zero.
Then execute in Production.
mysql> show global variables like
'host%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| host_cache_size | 643 |
| hostname | db01
|
+-----------------+-------+
2 rows in set (0.06 sec)
mysql> use your_db;
Database changed
mysql> \. /home/data/jira/DISDB-1000/29jan2015/load_infile.sql
Query OK, 654 rows affected (0.32 sec)
Records: 665 Deleted: 0
Skipped: 11 Warnings: 0
mysql>
Summary
Using the above procedure should help
getting a CSV imported in to a Database.
Sometimes you might have to delete spaces and entire words, and
sometimes the errors will be on the line above or below the error or very close
to the line where the error in the warnings.
Comments
Post a Comment