Mysql – create a MySQL CSV table directly from an existing CSV file

csvMySQL

MySQL has a CSV storage engine.

I'm wondering if I could create such a table directly from an existing CSV file.

Made up command as example:

CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV FROM FILE /file;

What I'm trying to achieve is an efficient way to load data that doesn't depend on LOAD DATA as that command opens up the possibility of arbitrary command execution.

Best Answer

Here is something a MySQL Daredevil is more than willing to try.

I just did the following in MySQL 5.6.14 right on my Laptop

C:\Windows\system32>mysql -Dtest
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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://localhost/redwards/3306 test :: create table dt (a INT NOT NULL,b CHAR(10) NOT NULL) ENGINE=CSV;
Query OK, 0 rows affected (0.08 sec)

MySQL://localhost/redwards/3306 test :: insert into dt values (1,'rolando'),(2,'pamela'),(3,'dominique'),(4,'diamond');
Query OK, 4 rows affected (0.11 sec)
Records: 4  Duplicates: 0  Warnings: 0

MySQL://localhost/redwards/3306 information_schema :: flush tables;
Query OK, 0 rows affected (0.31 sec)

MySQL://localhost/redwards/3306 test :: exit
Bye

Now, going into the OS:

C:\Windows\system32>cd \MySQL_5.6.14\data\test

C:\MySQL_5.6.14\data\test>dir
 Volume in drive C has no label.
 Volume Serial Number is A06D-D0B3

 Directory of C:\MySQL_5.6.14\data\test

12/18/2013  04:18 PM    <DIR>          .
12/18/2013  04:18 PM    <DIR>          ..
10/24/2013  10:20 AM                65 db.opt
12/18/2013  04:20 PM                35 dt.CSM
12/18/2013  04:19 PM                49 dt.CSV
12/18/2013  04:18 PM             8,578 dt.frm
10/29/2013  06:06 PM                 0 exp1#P#part00.MYD
10/29/2013  06:06 PM             1,024 exp1#P#part00.MYI
10/29/2013  06:06 PM                 0 exp1#P#part01.MYD
10/29/2013  06:06 PM             1,024 exp1#P#part01.MYI
10/29/2013  06:06 PM                 0 exp1#P#part02.MYD
10/29/2013  06:06 PM             1,024 exp1#P#part02.MYI
10/29/2013  06:06 PM               336 exp1#P#part03.MYD
11/15/2013  05:44 PM             2,048 exp1#P#part03.MYI
10/29/2013  06:06 PM                 0 exp1#P#part99.MYD
10/29/2013  06:06 PM             1,024 exp1#P#part99.MYI
10/29/2013  06:06 PM             8,642 exp1.frm
10/29/2013  06:06 PM                60 exp1.par
10/24/2013  10:30 AM             8,682 menus.frm
10/24/2013  10:30 AM                68 menus.MYD
10/28/2013  03:00 PM             1,024 menus.MYI
10/24/2013  10:30 AM             8,584 pages.frm
10/24/2013  10:30 AM                80 pages.MYD
10/28/2013  03:00 PM             2,048 pages.MYI
              22 File(s)         44,395 bytes
               2 Dir(s)  1,869,894,590,464 bytes free

C:\MySQL_5.6.14\data\test>type dt.CSV
1,"rolando"
2,"pamela"
3,"dominique"
4,"diamond"

C:\MySQL_5.6.14\data\test>

As show in the above commands in mysql and in the Windows command line, the CSV file that mysql can handle has the following two characteristics:

  • fields delimited by commas
  • character fields enclosed in double quotes

Since you created the test table as CSV, here is what you should do:

  1. Make sure the existing CSV file has the two characteristics for mysql to work with it
  2. Go to the folder test in your datadir.
  3. Rename test.CSV to test1.CSV
  4. Copy your existing CSV file to test.CSV
  5. Login to mysql and run FLUSH TABLES;

Afterwards, the table shold be fully accessiable.

CAVEAT (Windows)

If Windows has the CSV table locked, run these commands

  1. Make sure the existing CSV file has the two characteristics for mysql to work with it
  2. Go to the folder test in your datadir.
  3. Run net stop mysql
  4. Rename test.CSV to test1.CSV
  5. Copy your existing CSV file to test.CSV
  6. Run net start mysql

Give it a Try !!!

Related Question