MySQL

Saving query results to .ccsv

You can save the results of a query in MySQL to a .ccsv file with little trouble. For example …

select field1, field2, field3 
from mytable 
into outfile '/var/lib/mysql-files/test.ccsv'
fields terminated by X'1F'
lines terminated by X'1E'
;

This will put the data into a file on your server. You may have to make some changes to your server properties to grant you the proper rights to save to a file.

The file created will just have the data. No column headers will be included. If you want column titles, you can use an approach similar to the following …

select 'label1', 'label2', 'label3'
union all
select field1, field2, field3 
from mytable 
into outfile '/var/lib/mysql-files/test.ccsv'
fields terminated by X'1F'
lines terminated by X'1E'
;

Importing a .ccsv file

Loading data from a .ccsv file works much the same as saving data to a .ccsv.

load data infile '/var/lib/mysql-files/test.ccsv'
    into table mytable
fields terminated by X'1F'
lines terminated by X'1E'
ignore 1 lines;

If the file has column labels, use the ignore 1 lines option.