3

I don't think that this is a programming question so I am going to ask it here -

Reading the book high performance mysql, I read about the CSV engine. The paragraph says:

The CSV engine can treat comma-separated values (CSV) files as table, but it does not support indexes on them. This engine lets you copy files in and out of the database while the server is running. If you export a CSV file from a spreadsheet and save it in the MySQL server's data directory, the server can read it immediately. Similary, if you write data to a CSV table, an external program can read it right away. CSV tables are especially useful as a data interchange format and for certain kinds of logging.

What I get from this paragraph is that I can copy a .CSV file into the data directory of database, and it should show as a table that is able to be read from. However, whenever I copy a test .csv file into the directory, it does not appear as a table. I can't access it.

I am using MySQL 5.5 also

Does anyone know why this is not working, or what I am doing wrong?

1 Answer 1

5

I opted to try this out on my MySQL server to see what the problem was. It appears that you need to create the table in MySQL (with engine=csv specified) so that the table will appropriately be added to MySQL and it will keep track of the table field metadata. In my testing, I was unable to make MySQL learn of the csv file without first using "create table."

Example:

  1. Create new csv named foo in this example a column named "i" for an integer, and "c" for a character field: CREATE TABLE foo (i int not null, c char(10) not null) engine=csv;
  2. foo.frm, foo.CSM, foo.CSV are created in /var/lib/mysql/data/dbname, you will note that foo.CSV is zero-byte.
  3. Copy your existing csv (that matches the format defined for the table) into foo.CSV:

    1,"FOOBAR"
    2,"FOOBAZ"
    3,"BARBAZ"
    
  4. In mysql, select * from foo should produce the following result:

    mysql> select * from foo;
    +---+--------+
    | i | c      |
    +---+--------+
    | 1 | FOOBAR |
    | 2 | FOOBAZ |
    | 3 | BARBAZ |
    +---+--------+
    3 rows in set (0.00 sec)
    
2
  • So for example, you created a csv file with test data called 'test.csv', then CREATE TABLE test ENGINE=csv; and it was able to read the data from the test.csv like a normal table?
    – Jeff
    Nov 22, 2011 at 16:13
  • What I did was create table, which created a csv file in the data directory. I then copied over my csv into that filename and was able to do queries. Nov 22, 2011 at 16:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .