Export data from MySQL to CSV file using SQL query- SQL Hacks

Export data from MySQL to CSV

Sometimes it's required to store the data from your database table to our local machine. To do this operation we can use phpmyadmin export feature too, but what if you want to get the data from the database to your CSV file using MySQL command line. In this blog post, I will explain how to do it just by writing one query.

But before starting that make sure that you have set your global 'local_infile'=on, if not then please visit this link to know about it-https://codingclown.blogspot.com/2020/06/import-data-from-csv-file-to-mysql.html.

Step 1: Select your database where the table is stored from where you want to copy the data.

Step 2: Check whether the table is not empty using the command-

             

             Run the command: Select * from table_name;

Step 3: Type this command to copy the data from your table to your local machine.

           

Run the command:    select * into outfile "C:/ProgramData/MySQL/MySQL Server        8.0/Uploads/Book1.csv" fields terminated by "," optionally enclosed by " ' " lines                terminated by  "\n" from table_name;

Here Book1.csv is my CSV file, make sure there is no file in this location with the same name.

Read More

Import data from CSV file to MySQL Table using LOAD LOCAL- SQL Hacks

Import data from CSV file to MySQL Table

We all would have encountered an error like- 

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides.

Cause

LOAD DATA LOCAL INPATH is disabled by default because it poses a security risk. A malicious server or proxy could send a fake "local infile request" packet to the client and read any file that the client has permission to open.

How to Fix 

There are a few easy steps to resolve this problem but before starting that I assume that you would have your CSV file ready.  If not then create an excel sheet and add data like as shown in the image below save it with .csv extension.

                 

                

STEP 1: Check if the local file is enabled or not.

                Run the command: SHOW VARIABLES LIKE  'local_infile';

                

STEP 2: Set local_infile=on.

                Run the command: SET GLOBAL local_infile=true; 

STEP 3: Check again if the local infile is turned on with the second step or not.

                Run the command: SHOW VARIABLES LIKE  'local_infile';

                

STEP 4:  Check the variable 'secure_file_priv'.

                Run the command: SHOW VARIABLES LIKE  'secure_file_priv';

                

STEP 5: Copy the file to be uploaded to this location- 

               C:\ProgramData\MySQL\MySQL Server 8.0\Uploads

                

        Doing this step is very important as without it we won't be able to upload our file to MySQL table.

STEP 6:  Follow the given syntax to upload the file.        

Read More