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.        

                LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv'
                INTO TABLE table_name
                FIELDS TERMINATED BY  ','
                LINES TERMINATED BY  '\n'
                IGNORE 1 LINES;

• The field of the file is terminated by a comma indicated by  FIELD TERMINATED BY ','.

• Each line of the CSV file is terminated by a newline character indicated by  LINES TERMINATED BY '\n'.

• Because the file has the first line that contains the column headings, which should not be imported into the table, therefore we ignore it by specifying  IGNORE 1 ROWS option.

Executing the last step will store the current data from your CSV file to the table which you have mentioned in your query. I hope this blog would have helped you in resolving this issue.

Please mention your comments, will be happy to reply you back :)