使用 Ubuntu 终端通过 shell 脚本将 CSV 插入 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16011143/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert CSV to MySQL using Ubuntu Terminal via shell script
提问by Maksym Polshcha
Is it possible to insert a CSV file into MySQL using a shell script in Ubuntu?
是否可以在 Ubuntu 中使用 shell 脚本将 CSV 文件插入 MySQL？
Here's what I tried :
这是我尝试过的：
mysql -uroot -proot mysfdb < /home/sf/data.csv
But I am given an error
但我得到了一个错误
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
Here's a sample content from the CSV file:
以下是 CSV 文件中的示例内容：
showinventory_SST312V8N4615041313_1366009574txt_,800-200002.A0,00007985
Any ideas?
有任何想法吗？
回答by
Maksym Polshcha's answer is correct but is only missing a few things. Apparently, since it's a local file, I have to declare it as a local file in the mysql command. The final command should be something like this:
Maksym Polshcha 的回答是正确的，但只遗漏了一些东西。显然，由于它是本地文件，我必须在 mysql 命令中将其声明为本地文件。最后的命令应该是这样的：
mysql -uroot -proot --local_infile=1 3parsfdb -e "LOAD DATA LOCAL INFILE '/logfiles/Bat_res.csv' INTO TABLE Bat_res FIELDS TERMINATED BY ','"
Also I made sure that the /logfilesdirectory and the Bat_res.csvare world readable.
我还确保/logfiles目录和目录Bat_res.csv是世界可读的。
Thank you for the great answers.
谢谢你的好答案。
回答by Maksym Polshcha
Try this:
尝试这个：
mysql -uroot -proot mysfdb -e "LOAD DATA INFILE '/home/sf/data.csv' INTO TABLE mytable"
where mytableis your table for the data. If you have non-standard field/line separators in your CSV file use FIELDS TERMINATED BYand LINES TERMINATED BY
其中mytable是您的数据表。如果您的 CSV 文件中有非标准的字段/行分隔符，请使用FIELDS TERMINATED BY和LINES TERMINATED BY
回答by Ashish kumar Pandey
I used this and it worked.
我使用了这个并且它起作用了。
Login in mysql using `mysql -uroot -ppassword --local-infile`
Then in terminal:
然后在终端：
LOAD DATA LOCAL INFILE '.csv path' INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
回答by Sanaulla
Open Ubuntu Terminal and just run the following command
打开 Ubuntu 终端并运行以下命令
# mysql -u admin -p --local_infile=1 DATABASE_NAME -e "LOAD DATA LOCAL INFILE 'students.csv' INTO TABLE TABLE_NAME FIELDS TERMINATED BY ',' enclosed by '\"'"
Here,
这里，
- DATABASE_NAME = The name of your database
- students.csv = The CSV file directory, that you want to upload in the database
- TABLE_NAME = in which table you want to upload your data
- admin = Database user name
- DATABASE_NAME = 数据库名称
- student.csv = 要上传到数据库中的 CSV 文件目录
- TABLE_NAME = 要上传数据的表
- admin = 数据库用户名
After run this command system asked for the password of the admin user.
运行此命令后，系统要求输入管理员用户的密码。
Write the password and Enjoy.
写下密码并享受。
