Tips for importing csv, excel or sql files into MySQL

1. Import csv file

Use the following command:

 1.mysql> load data infile "your csv file path" into table [tablename] fields terminated by ','

The csv file above uses ',' as the delimiter and needs to be enclosed in double quotes or single quotes. If the '\t' tab character is used as the delimiter, there is no need to explicitly specify the field separator.

Another thing to note is that the path of the csv file or text file must use an absolute path, otherwise MySQL will default to looking for it in the directory where the database is stored, and if it cannot find it, it will report the following error:

ERROR 13 (HY000) at line 1: Can't get stat of '/var/lib/mysql/PromotionGroup/attempt_1467708933142_34285938_m_000000_0.1469434545564' (Errcode: 2)

perror 2 to see what the error code means: OS error code 2: No such file or directory. This means that there is no specified file or directory.

If you use the absolute path, the following error will still be reported:

ERROR 13 (HY000) at line 1: Can't get stat of '/fullpath/file.csv' (Errcode: 13)

Run the perror 13 command to view the error type: OS error code 13: Permission denied. According to the error prompt, I naturally checked whether the file had readable permissions. The result was yes. So I searched Baidu but couldn't get the correct answer. I was puzzled and finally chose Google and found the answer I wanted on StackOverflow.

Try to use LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE.

Try using LOAD DATA LOCAL INFILE and it works!

What is the difference between load data local infile and load data infile?

Use the LOCAL keyword to read the file from the client host. Without LOCAL, read the file from the server.

What's strange is that my file is on the server, but if I don't use local, an error will occur. I really don't understand!

2. Import excel file

No delimiter is required. Simply use the following command:

mysql> load data infile "your excel file path" into table [tablename]

Note that when importing files above, you need to create a data table corresponding to each segment in the file in advance. And the file path needs to be enclosed in quotation marks, either double quotes or single quotes.

3. Import sql file

There is no need to create a data table in advance, just use the source command:

source /home/abc/abc.sql;

The sql script does not need to be enclosed in double quotes. Essentially, source is used to execute an external SQL script, which will import the data included in the SQL script into the created data table.

The above are some tips for importing csv, excel or sql files into MySQL that I would like to share with you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!