How to upload the CSV file into MySQL table? 2 6

Last updated on Oct 31, 2020 08:09 AM in Academics Team
Posted ByDataTrained

1: Check if local files are enabled or not.

Command: SHOW VARIABLES LIKE 'local_infile';


2: set local_infile = on.

Command: SET GLOBAL local_infile = on;


3: Check again if the local_infile is turned on with step 1.

4: Check the variable 'secure_file_priv'

5: copy the file to be uploaded in the given folder.

Explanation: Due to security, you cannot upload the data from anywhere but from a secure folder, The folder location is given by the following command.

SHOW VARIABLES LIKE 'secure_file_priv' ;

 

Now, you can upload the file from 'secure_file_priv' with local_infile.


6: Follow the given syntax to upload the file.


LOAD DATA INFILE  'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/test_file_name.csv'

INTO TABLE test_table_name

FIELDS TERMINATED BY  ','

LINES TERMINATED BY  '\n'

IGNORE 1 LINES;

Note: Be careful about the syntax used above commands.

 

** The time is base on America/New_York timezone