{"id":787,"date":"2015-02-18T13:14:30","date_gmt":"2015-02-18T11:14:30","guid":{"rendered":"http:\/\/bos-info.com\/?p=787"},"modified":"2015-02-18T13:16:30","modified_gmt":"2015-02-18T11:16:30","slug":"csvtomysql","status":"publish","type":"post","link":"https:\/\/bos-info.com\/?p=787","title":{"rendered":"CSVtoMySQL"},"content":{"rendered":"<p>\u0412 \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0435 \u0440\u0430\u0431\u043e\u0442\u044b, \u043d\u0443\u0436\u043d\u043e \u0431\u044b\u043b\u043e \u043f\u0435\u0440\u0435\u043d\u0435\u0441\u0442\u0438 \u0434\u0430\u043d\u043d\u044b\u0435 \u0438\u0437 \u0442\u043e\u0447\u043a\u0438 \u0410 \u0432 \u0431\u0430\u0437\u0443 \u0434\u0430\u043d\u043d\u044b\u0445, \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e LibreOffice \u0431\u044b\u043b \u0441\u0444\u043e\u0440\u043c\u0438\u0440\u043e\u0432\u0430\u043d csv \u0444\u0430\u0439\u043b \u0441 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u044b\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u043c\u0438, \u0432 \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0435\u043c \u0444\u043e\u0440\u043c\u0430\u0442\u0435: \u0440\u0430\u0437\u0434\u0435\u043b\u0438\u0442\u0435\u043b\u044c \u0437\u0430\u043f\u044f\u0442\u0430\u044f, \u0442\u0435\u043a\u0441\u0442  \u0437\u0430\u043a\u043b\u044e\u0447\u0430\u0435\u043c \u0432 \u0434\u0432\u043e\u0439\u043d\u044b\u0435 \u043a\u0430\u0432\u044b\u0447\u043a\u0438 &#8220;&#8221;, \u043f\u0435\u0440\u0435\u0445\u043e\u0434 \u043d\u0430 \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0443\u044e \u0441\u0442\u0440\u043e\u043a\u0443 \\n.<br \/>\n\u041a\u043e\u043b\u043e\u043d\u043a\u0438 \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0435 \u043a\u0430\u043a\u0438\u0435 \u043d\u0443\u0436\u043d\u043e \u0437\u0430\u043d\u0435\u0441\u0442\u0438 \u0432 \u0431\u0430\u0437\u0443:<br \/>\nnumber, stationport, stationside,lineside<br \/>\n\u0421\u043e\u0431\u0441\u0442\u0432\u0435\u043d\u043d\u043e \u044d\u0442\u043e \u0436\u0435 \u043d\u0430\u043c \u043d\u0443\u0436\u043d\u043e \u0431\u0443\u0434\u0435\u0442 \u0440\u0430\u0441\u0441\u043a\u0430\u0437\u0430\u0442\u044c \u0438 MySQL.<br \/>\n<!--more--><br \/>\n\u0412\u0441\u0435 \u0434\u0435\u0439\u0441\u0442\u0432\u0438\u044f \u0432\u044b\u043f\u043e\u043b\u043d\u044f\u0435\u043c \u0432 \u043a\u043e\u043d\u0441\u043e\u043b\u044c\u043a\u0435, \u0432\u0441\u0435 \u0444\u0430\u0439\u043b\u044b \u0443\u0436\u0435 \u0437\u0430\u0433\u0440\u0443\u0436\u0435\u043d\u044b \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440 \u0411\u0414.<br \/>\n\u041f\u0440\u043e\u0431\u0443\u0435\u043c \u0442\u0430\u043a\u0443\u044e \u043a\u043e\u043c\u0430\u043d\u0434\u0443:<br \/>\n<code>LOAD DATA LOCAL INFILE '\/home\/user\/krosss.csv'<br \/>\nINTO TABLE kross<br \/>\nFIELDS TERMINATED BY ','<br \/>\n    ENCLOSED BY '\"'<br \/>\nLINES TERMINATED BY '\\n'<br \/>\n(number, stationport, stationside,lineside)<\/code><br \/>\n\u041f\u043e\u043b\u0443\u0447\u0430\u0435\u043c \u043e\u0442\u0432\u0435\u0442:<br \/>\n<code>ERROR 1148 (42000): The used command is not allowed with this MySQL version<\/code><br \/>\n\u041f\u0440\u0438\u0447\u0438\u043d\u0430: \u041d\u0435 \u043d\u0440\u0430\u0432\u0438\u0442\u0441\u044f MySQL \u0441\u043b\u043e\u0432\u043e LOCAL<br \/>\n\u041f\u0440\u043e\u0431\u0443\u0435\u043c \u0442\u0430\u043a<br \/>\n<code>LOAD DATA INFILE '\/home\/user\/krosss.csv'<br \/>\nINTO TABLE kross<br \/>\nFIELDS TERMINATED BY ','<br \/>\n    ENCLOSED BY '\"'<br \/>\nLINES TERMINATED BY '\\n'<br \/>\n(number, stationport, stationside,lineside)<\/code><br \/>\n\u041f\u043e\u043b\u0443\u0447\u0430\u0435\u043c \u043e\u0442\u0432\u0435\u0442:<br \/>\n<code>ERROR 1045 (28000): Access denied for user 'mysqluser'@'localhost' (using password: YES)<\/code><br \/>\n\u0414\u0430 \u043a\u0430\u043a\u043e\u0433\u043e \u0436\u0435&#8230;<br \/>\nC\u043c\u043e\u0442\u0440\u0438\u043c \u043f\u0440\u0438\u0432\u0438\u043b\u0435\u0433\u0438\u0438:<br \/>\n<code>show grants for 'mysqluser'@'localhost'<br \/>\n....<br \/>\nGRANT ALL PRIVILEGES ON `somedb`.* TO 'mysqluser'@'localhost'<br \/>\n<\/code><br \/>\n\u041c\u043e\u0436\u043d\u043e \u0434\u0430\u0442\u044c \u043f\u0440\u0430\u0432\u0430 GRANT FILE  \u043a\u043e\u043d\u043a\u0440\u0435\u0442\u043d\u043e\u043c\u0443 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044e, \u043d\u043e \u0438\u0434\u0435\u044f \u043c\u0435\u043d\u044f \u043f\u043e\u0447\u0435\u043c\u0443-\u0442\u043e \u0441\u043c\u0443\u0442\u0438\u043b\u0430, \u0438 \u044f \u0440\u0435\u0448\u0438\u043b \u0441\u0434\u0435\u043b\u0430\u0442\u044c \u043f\u043e \u0434\u0440\u0443\u0433\u043e\u043c\u0443<br \/>\n<code>mysql -umysqluser -psomepass somedb --local-infile<br \/>\nLOAD DATA LOCAL INFILE '\/home\/user\/krosss.csv'<br \/>\nINTO TABLE kross<br \/>\nFIELDS TERMINATED BY ','<br \/>\n    ENCLOSED BY '\"'<br \/>\nLINES TERMINATED BY '\\n'<br \/>\n(number, stationport, stationside,lineside)<br \/>\nQuery OK, 580 rows affected, 2 warnings (0.08 sec)<br \/>\nRecords: 580  Deleted: 0  Skipped: 0  Warnings: 2<br \/>\n<\/code><br \/>\n\u0411\u0438\u043d\u0433\u043e\u043e\u043e!<\/p>\n<p>\u041b\u0438\u0442\u0435\u0440\u0430\u0442\u0443\u0440\u0430:<br \/>\nhttp:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/loading-tables.html<br \/>\nhttp:\/\/stackoverflow.com\/questions\/16011143\/insert-csv-to-mysql-using-ubuntu-terminal-via-shell-script<br \/>\nhttp:\/\/stackoverflow.com\/questions\/13552206\/grant-file-on-just-one-database<br \/>\nhttp:\/\/stackoverflow.com\/questions\/1014724\/mysql-permission-errors-with-load-data<br \/>\nhttp:\/\/stackoverflow.com\/questions\/11077801\/import-csv-to-mysql-table<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u0412 \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0435 \u0440\u0430\u0431\u043e\u0442\u044b, \u043d\u0443\u0436\u043d\u043e \u0431\u044b\u043b\u043e \u043f\u0435\u0440\u0435\u043d\u0435\u0441\u0442\u0438 \u0434\u0430\u043d\u043d\u044b\u0435 \u0438\u0437 \u0442\u043e\u0447\u043a\u0438 \u0410 \u0432 \u0431\u0430\u0437\u0443 \u0434\u0430\u043d\u043d\u044b\u0445, \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e LibreOffice \u0431\u044b\u043b \u0441\u0444\u043e\u0440\u043c\u0438\u0440\u043e\u0432\u0430\u043d csv \u0444\u0430\u0439\u043b \u0441 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u044b\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u043c\u0438, \u0432 \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0435\u043c \u0444\u043e\u0440\u043c\u0430\u0442\u0435: \u0440\u0430\u0437\u0434\u0435\u043b\u0438\u0442\u0435\u043b\u044c \u0437\u0430\u043f\u044f\u0442\u0430\u044f, \u0442\u0435\u043a\u0441\u0442 \u0437\u0430\u043a\u043b\u044e\u0447\u0430\u0435\u043c \u0432 \u0434\u0432\u043e\u0439\u043d\u044b\u0435 \u043a\u0430\u0432\u044b\u0447\u043a\u0438 &#8220;&#8221;, \u043f\u0435\u0440\u0435\u0445\u043e\u0434 \u043d\u0430 \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0443\u044e \u0441\u0442\u0440\u043e\u043a\u0443 \\n. \u041a\u043e\u043b\u043e\u043d\u043a\u0438 &hellip; <a href=\"https:\/\/bos-info.com\/?p=787\">\u041f\u0440\u043e\u0434\u043e\u0432\u0436\u0438\u0442\u0438 \u0447\u0438\u0442\u0430\u043d\u043d\u044f <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[57],"tags":[54,26],"_links":{"self":[{"href":"https:\/\/bos-info.com\/index.php?rest_route=\/wp\/v2\/posts\/787"}],"collection":[{"href":"https:\/\/bos-info.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bos-info.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bos-info.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bos-info.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=787"}],"version-history":[{"count":3,"href":"https:\/\/bos-info.com\/index.php?rest_route=\/wp\/v2\/posts\/787\/revisions"}],"predecessor-version":[{"id":790,"href":"https:\/\/bos-info.com\/index.php?rest_route=\/wp\/v2\/posts\/787\/revisions\/790"}],"wp:attachment":[{"href":"https:\/\/bos-info.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=787"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bos-info.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=787"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bos-info.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=787"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}