Shulou(Shulou.com)05/31 Report--

How to import csv and excel in MySQL? Many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

1. Import csv Fil

Use the following command:

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

The above csv file is delimited by', 'commas and needs to be enclosed in double or single quotation marks. If you use the'\ t 'tab as the delimiter, you don't have to display the specifier field separator.

It is also important to note that the path of the csv file or text file should use an absolute path, otherwise mysql will find it from the directory stored in the database by default, and the following error will be reported if it cannot be found:

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 check what the error code means: OS error code 2: No such file or directory. It means that there is no specified file or directory.

If you use an absolute path, you will still report the following error:

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

Use the command perror 13 to view the error type: OS error code 13: Permission denied. According to the wrong prompt, let nature take its course to check whether the file has readable access, the result is yes, so all kinds of Baidu did not get the correct answer, puzzled, finally chose google in stackoverflow to find the answer I want.

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

Give it a try with LOAD DATA LOCAL INFILE, and it really 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. Read files from the server without using LOCAL.

Strangely enough, my files are on the server, but if I don't use local, I will make a mistake. I don't understand!

two。 Import excel Fil

You do not need to specify a separator. Use the following command directly:

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

Note that when importing a file above, you need to establish a data table corresponding to each section of the file in advance. And the path to the file needs to be enclosed in quotation marks, both double and single quotation marks.

3. Import sql Fil

Instead of building the data table in advance, use the source command directly:

Is it helpful for source / home/abc/abc.sql; to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.