Import to MySql from dump files

Sometimes we need insert some data in database, and sometimes this data comes in strange format (different separators, termination characters, etc). In this case tools like phpmyadmin isn't very useful.

Happily, MySql cli has all we need (and much more).

Lets look at this simple dump file for example:

|-name-|-age-|-result-|-city-|
|Alice|23|true|Sacramento
|Bob|31|false|Austin
|Eve|25|true|Dallas
|John|23|false|undefined
|Steve|34|true|DC

At the top we have description for column names, below - data separated by "|", besides, all lines also begins with "|" and have a newline character at the end.
Check this:

user@mysql:/tmp$ cat user_import.txt | od -c  
0000000   |   -   n   a   m   e   -   |   -   a   g   e   -   |   -   r  
0000020   e   s   u   l   t   -   |   -   c   i   t   y   -   |  \n   |  
0000040   A   l   i   c   e   |   2   3   |   t   r   u   e   |   S   a  
0000060   c   r   a   m   e   n   t   o  \n   |   B   o   b   |   3   1  
0000100   |   f   a   l   s   e   |   A   u   s   t   i   n  \n   |   E  
0000120   v   e   |   2   5   |   t   r   u   e   |   D   a   l   l   a  
0000140   s  \n   |   J   o   h   n   |   2   3   |   f   a   l   s   e  
0000160   |   u   n   d   e   f   i   n   e   d  \n   |   S   t   e   v  
0000200   e   |   3   4   |   t   r   u   e   |   D   C  \n  
0000215

This files has Unix-like LF newlines ("\n "), we will need this to specify lines terminators.
Let's import it.
Create new table

mysql> create table temp_users (name text, age int, result text, city text, id int auto_increment primary key) character set utf8 collate utf8_bin;  

and use load data infile to insert dump into table (sure, dump file must be accessible from mysql server):

mysql> load data infile "/tmp/user_import.txt" into table temp_users  
    -> fields terminated by "|"  
    -> lines starting by "|"  
    -> terminated by "\n"  
    -> ignore 1 lines; 

ignore 1 lines - just for omit first line with columns description. Check it:

mysql> select * from temp_users;  
+-------+------+--------+------------+----+
| name  | age  | result | city       | id |
+-------+------+--------+------------+----+
| Alice |   23 | true   | Sacramento | 29 |
| Bob   |   31 | false  | Austin     | 30 |
| Eve   |   25 | true   | Dallas     | 31 |
| John  |   23 | false  | undefined  | 32 |
| Steve |   34 | true   | DC         | 33 |
+-------+------+--------+------------+----+
5 rows in set (0.00 sec)  

Pretty simple, but very powerful.
Sure, we can create dump file with outfile too :)

mysql> select * from temp_users into outfile "/tmp/temp_users.csv"  
    -> fields terminated by "," 
    -> enclosed by '"' 
    -> lines terminated by "\n";

And get valid csv:

user@mysql:/tmp$ cat temp_users.csv  
"Alice","23","true","Sacramento","29"  
"Bob","31","false","Austin","30"  
"Eve","25","true","Dallas","31"  
"John","23","false","undefined","32"  
"Steve","34","true","DC","33"