• MySQL知识库 :: text, csv files
  • How do I exchange data between applications using CSV Tables?

  • Discussion

    CVS tables use the CVS format. This format is usually used to exchange data between different kind of applications (e.g. spread sheets etc.). Below is an example of this:

    SELECT * FROM  csv_table1;
     
    +------+------------+ 
    | id   | value      | 
    +------+------------+ 
    |    1 | record 1   | 
    |    2 | record 2   | 
    |    3 | record 3   | 
    +------+------------+ 

    If you were to display the results at the filesystem level on a Unix based system, it would look like this:

    $ cat csv_table1.CSV 
    "1","record 1" 
    "2","record 2" 
    "3","record 3"

    The data can be read by any other applications which understand the CVS format (e.g., OpenOffice Calc). To allow the application to access the data, either the application has to be started by the MySQL owner (e.g. mysql), or the permissions of the file have to be changed correctly (e.g., chmod o+r csv_table1.CSV). Writing data is possible under some circumstances. Unfortunately, an application like OpenOffice Calc writes numbers in the wrong format (i.e., missing double quotes):

    $ cat oo_table1.csv 
    1,"record 1" 
    2,"record 2" 
    3,"OO Calc"

    If you palm MySQL, such a file the system will dump with little amount of data or it will start wasting memory until the whole server crashes. Never do such things on a productive system.

    SELECT COUNT(*) FROM csv_table1;
    
    ERROR 2013 (HY000): Lost connection to MySQL server during query 
    Number of processes running now: 0 
    051203 21:58:54  mysqld restarted

    In this case neither REPAIR TABLE nor CHECK TABLE helps. Do either a TRUNCATE TABLE or fix it some other way (e.g., use vi to edit it directly). After fixing the problem with an editor (e.g,. vi), you can easily palm the CSV file to MySQL. The database does not immediately recognise the data but after flushing the table the data will be reread:

    FLUSH TABLE csv_table1;
     
    SELECT * FROM csv_table1;
    
    +------+------------+ 
    | id   | value      | 
    +------+------------+ 
    |    1 | record 1   | 
    |    2 | record 2   | 
    |    3 | OO Calc    | 
    +------+------------+ 

    Only UNIX operating system files are supported. However, you should easily be able to convert the files to the DOS format (using a utility like dos2unix) either using one of your UNIX systems or installing CygWin (with dos2unix) on your Windows box.

    ==> cvs_converter.pl

    To avoid problems with your external application you should better define the character set of your table according to the import/export filters of you application:

    CREATE TABLE csv_table1 (col_0 INT, col_1 CHAR(20)) 
    ENGINE = CSV 
    CHARACTER SET utf8;

    Be careful using correct formats for the data that you are manipulating with your external application. Wrong data or formats can crash the database system (and also the server) or can corrupt the data.

    UPDATE csv_table1 SET c='Shorter' 
    WHERE c ='record 3 is much too long.';
    
    Query OK, 0 rows affected, 1 warning (0.01 sec) 
    Rows matched: 0  Changed: 0  Warnings: 1 
    
    SHOW WARNINGS;
    Warning (Code 1265): Data truncated for column 'c' at row 3