MySQL Export file to CSV

To use a ResultSet across applications or to share a ResultSet between different developers/users, MySQL Export to CSV is the preferred way. By using this option, we can write the ResultSet to a CSV file which is easy to edit, parse and import from if needed.

MySQL Output to CSV – Example

Consider we have a table “Student”:

Student Table

Let’s start from the simplest. Export all rows from the “Student” table to “export1.csv”:

MySQL Select to CSV – “Student” table to “export1.csv”

ResultSet:

export1.csv

Do note that the .csv file does not have column names. MySQL export does not write column names by default. So, we have to select the column names along with the ResultSet to write in the exported file.

MySQL Select to CSV – with column names

ResultSet:

export2.csv

MySQL Export to CSV with FIELDS TERMINATED BY

In the exported file, the separation between columns is marked with the TAB character by default. We can change this using “FIELDS TERMINATED BY” clause as shown below:

MySQL Export to CSV with FIELDS TERMINATED BY

ResultSet:

export3.csv

Since the separation between fields is marked by “,”, any comma(“,”) inside the values are escaped with a prefix “\”.

MySQL Export to CSV with ENCLOSED BY

“ENCLOSED BY” is another fruitful option which enables enclosing the whole value with a mentioned character.

MySQL Export to CSV with ENCLOSED BY

ResultSet:

export4.csv

Since the column values are enclosed now, there is no need to prefix escape character before “,” (comma). But, if the enclosing character itself is present within the values, then that should be escaped.

Similarly, the escaping character itself can be changed by using “ESCAPED BY” clause. The termination of each row by default is marked with “\n”, which can also be changed using “LINES TERMINATED BY” clause.MySQL Workbench – Querychat

MySQL Export to CSV – Example with all options

Let’s look at an example where all the discussed options are utilized:

MySQL Export to CSV with all options

ResultSet:

export5.csv

MySQL Database Import And Export Operations

Take Backup With Data

Open Command Prompt (cmd)
# mysqldump -u dbusername -p db_name > /tmp/db_fullbackup.sql

Take Only Metadata Backup


# mysqldump -u dbusername -p –no-data db_name > /tmp/db_backupmetadata.sql
or
# mysqldump -u dbusername -p -d db_name > /tmp/db_backupmetadata.sql

Restore Database Metadata From Backup


# mysql -u dbusername -p db_name < /tmp/db_backupmetadata.sql

Restore Database From Backup


# mysql -u dbusername -p db_name < /tmp/db_fullbackup.sql

MySql export schema without data

I'm using a MySql database with a PHP program, now I want to give the program to somebody else.

How to export the MySql database structure without the data in it, just the structure?

 Answers

You can do with the --no-data option with mysqldump command

mysqldump -u root -p --no-data dbname > schema.sql

---

you can also extract an individual table with the --no-data option

mysqldump -u user -h localhost --no-data -p database tablename > table.sql

---

You Can Use MYSQL Administrator Tool its free http://dev.mysql.com/downloads/gui-tools/5.0.html

you'll find many options to export ur MYSQL DataBase

---

Dumping without using output.

mysqldump --no-data <database name> --result-file=schema.sql

---

You can take using the following method

mysqldump -d <database name> > <filename.sql> // -d : without data

Hope it will helps you

MySQL: How to export and import a .sql file from command line?

I want to export and import a .sql file to and from a MySQL database from command line.

Is there any command to export .sql file in MySQL? Then how do I import it?

When doing the import, there may be constraints like enable/disable foreign key check or export only table structure.

Can we set those options with mysqldump?

 Answers

To export

If it's an entire DB, then:

$ mysqldump -u [uname] -p[pass] db_name > db_backup.sql

If it's all DBs, then:

$ mysqldump -u [uname] -p[pass] --all-databases > all_db_backup.sql

If it's specific tables within a DB, then:

$ mysqldump -u [uname] -p[pass] db_name table1 table2 > table_backup.sql

You can even go as far as auto-compressing the output using gzip (if your DB is very big):

$ mysqldump -u [uname] -p[pass] db_name | gzip > db_backup.sql.gz

If you want to do this remotely and you have the access to the server in question, then the following would work (presuming the MySQL server is on port 3306):

$ mysqldump -P 3306 -h [ip_address] -u [uname] -p[pass] db_name > db_backup.sql

To import

Type the following command to import sql data file:

$ mysql -u username -p -h localhost DATA-BASE-NAME < data.sql

In this example, import 'data.sql' file into 'blog' database using sat as username:

$ mysql -u sat -p -h localhost blog < data.sql

If you have a dedicated database server, replace localhost hostname with with actual server name or IP address as follows:

$ mysql -u username -p -h 202.54.1.10 databasename < data.sql

OR use hostname such as mysql.cyberciti.biz

$ mysql -u username -p -h mysql.cyberciti.biz database-name < data.sql

If you do not know the database name or database name is included in sql dump you can try out something as follows:

$ mysql -u username -p -h 202.54.1.10 < data.sql

Refer: http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

If you want a GUI tool then you could probably use SQLyog

---

If you're already running the SQL shell, you can use the source command to import data:

use databasename;
source data.sql;

---

mysqldump will not dump database events, triggers and routines unless explicitly stated when dumping individual databases;

mysqldump -uuser -p db_name --events --triggers --routines > db_name.sql

---

Well you can use below command to export,

mysqldump --database --user=root --password your_db_name > export_into_db.sql

and the generated file will be available in the same directory where you had ran this command.

Now login to mysql using command,

mysql -u[username] -p

then use "source" command with the file path.

---

You can use this script to export or import any database from terminal given at this link: https://github.com/Ridhwanluthra/mysql_import_export_script/blob/master/mysql_import_export_script.sh

echo -e "Welcome to the import/export database utility\n"
echo -e "the default location of mysqldump file is: /opt/lampp/bin/mysqldump\n"
echo -e "the default location of mysql file is: /opt/lampp/bin/mysql\n"
read -p 'Would like you like to change the default location [y/n]: ' location_change
read -p "Please enter your username: " u_name
read -p 'Would you like to import or export a database: [import/export]: ' action
echo

mysqldump_location=/opt/lampp/bin/mysqldump
mysql_location=/opt/lampp/bin/mysql

if [ "$action" == "export" ]; then
    if [ "$location_change" == "y" ]; then
        read -p 'Give the location of mysqldump that you want to use: ' mysqldump_location
        echo
    else
        echo -e "Using default location of mysqldump\n"
    fi
    read -p 'Give the name of database in which you would like to export: ' db_name
    read -p 'Give the complete path of the .sql file in which you would like to export the database: ' sql_file
    $mysqldump_location -u $u_name -p $db_name > $sql_file
elif [ "$action" == "import" ]; then
    if [ "$location_change" == "y" ]; then
        read -p 'Give the location of mysql that you want to use: ' mysql_location
        echo
    else
        echo -e "Using default location of mysql\n"
    fi
    read -p 'Give the complete path of the .sql file you would like to import: ' sql_file
    read -p 'Give the name of database in which to import this file: ' db_name
    $mysql_location -u $u_name -p $db_name < $sql_file
else
    echo "please select a valid command"
fi

Mysql: Exporting table from Amazon RDS into a csv file

I have a mysql database running in Amazon RDS, and I want to know how to export an entire table to csv format. I currently use mysql server on Windows to query the Amazon database, but when I try to run an export I get an error, probably because there's no dedicated file server for amazon RDS. Is there any solution to this?

 Answers

Presumably you are trying to export from an Amazon RDS database via a SELECT ... INTO OUTFILE query, which yields this indeed commonly encountered issue, see e.g. export database to CSV. The respective AWS team response confirms your assumption of lacking server access preventing an export like so, and suggests an alternative approach as well via exporting your data in CSV format by selecting the data in the mysql command line client and piping the output to reformat the data as CSV, like so:

mysql -u username -p --database=dbname --host=rdshostname --port=rdsport --batch 
  -e "select * from yourtable" 
  | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > yourlocalfilename

User fpalero provides an alternative and supposedly simpler approach, if you know and specify the fields upfront:

mysql -uroot -ppassword --database=dbtest 
  -e "select concat(field1,',',field2,',',field3) FROM tabletest" > tabletest.csv

Export data to CSV from MySQL

MySQL has a couple of options for exporting data: using the command line tool mysqldump (read my using mysqldump to save data to CSV files post for more details) and using a "SELECT ... INTO OUTFILE" SQL query. This post looks at the latter to export data from MySQL into a CSV file.

To dump all the records from a table called "products" into the file /tmp/products.csv as a CSV file, use the following SQL query:

SELECT *
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products

Note that the directory must be writable by the MySQL database server. If it's not, you'll get an error message like this:

#1 - Can't create/write to file '/tmp/products.csv' (Errcode: 13)

Also note that it will not overwrite the file if it already exists, instead showing this error message:

#1086 - File '/tmp/products.csv' already exists

If you don't need quotes around all fields (e.g. numeric fields) then change "ENCLOSED BY" to "OPTIONALLY ENCLOSED BY" and MySQL will only put quotes around the fields that need them. Some systems require all fields in a CSV file to have quotes around them so you may need to export the data with quotes around them all depending on your requirements.

To only export a selected set of fields or data, change "SELECT *" to "SELECT field1, field2, etc" and add a WHERE clause after the FROM clause.

Exporting data from mysql to ms excel

I have the following code which I am using to export data from mysql database to microsoft excel

    $result = $this->db->query($sql);
$num_fields = mysql_num_fields($result);
    $header = "";
    for($i = 0; $i < $num_fields; $i++ )
    {
      $header .= mysql_field_name($result,$i)."\t";
    }
    $data = "";
    while($row = mysql_fetch_row($result))
   {
    $line = '';
    foreach($row as $value)
    {
        if((!isset($value)) || ($value == ""))
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}
/*if($code=="M"||$code=='m'){
    $value="\n Total \t $total \t";
    $data .=trim($value)."\n";
}*/

$data = str_replace("\r" , "" , $data);

if ($data == "")
{
    $data = "\n No Record Found!n";
}
header("Cache-Control: ");
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"$label.xls\"");
header("Pragma: ");
header("Expires: 0");
print "$header\n$data";

where $result is a mysql resource.

When I run the program, before opening, excel issues a warning "the file you are trying to open, filename.xls, is in different format than specified by the extension, verify that the file is not corrupted and is from a trusted source before opening. Do you want to open the file now?"

I am stranded because after accepting to open the file, I see the data that I require, what I want is a way of making the format of data sent match the xls extension format. What should I do?. I am using ms office 2007

---

If you want to produce a real Excel file, use PHPExcel.