MySQL 5.5 – Unable to Import CSV File

csvmysql-5.5

Gone through the below link also but i am unable to import CSV file in mysql. Even i had tried by using > LOAD DATA LOCAL INFILE but same error persisting.
Please help me in this. queries i tried had appended below

MySQL import csv only gets half the lines

mysql> LOAD DATA INFILE 'home/karthick.g.s/Desktop/karthick.csv'
    -> INTO TABLE test_1
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '^' 
    -> LINES TERMINATED BY '\r\n';
ERROR 13 (HY000): Can't get stat of '/var/lib/mysql/home/karthick.g.s/Desktop/karthick.csv' (Errcode: 2)
mysql> LOAD DATA LOCAL INFILE 'home/karthick.g.s/Desktop/karthick.csv'
    -> INTO TABLE test_1
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '^'
    -> LINES TERMINATED BY '\r\n';
ERROR 1148 (42000): The used command is not allowed with this MySQL version

I am using the version 5.5.37-0ubuntu0.12.04.1

Best Answer

From the manual here, you could try experimenting with your my.cnf settings - I know that I had this problem in the past and I eventually got around this by using [client] setting.

# added from http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html
# to allow for abrowse to load data!
loose-local-infile  =   1

local-infile    = 1

Now, it was either local-infile or loose-local-infile which worked eventually. I believe that loose-local-infile is the more permissive setting, so you could try that first - and if you've no security concerns on your server, you could leave it at that.

Related Solutions

MySQL Import CSV File Error 13 (HY000) – Troubleshooting Guide

I hope you are using LOAD DATA INFILE.

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

Other issue might be this, please visit the following links : MySQL LOAD DATA.

When you login in MySQL do like below,

abdul@xmpp3:~/Desktop/Jiva$ mysql -uroot -p --local-infile
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 257
Server version: 5.5.29-0ubuntu0.12.04.1-log (Ubuntu)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mydb;


mysql> LOAD DATA LOCAL INFILE '/home/abdul/Desktop/STATISTIC_T.csv' INTO TABLE STATISTIC_T  FIELDS TERMINATED BY '|' ENCLOSED BY '"' LINES TERMINATED BY '\n'  IGNORE 1 LINES;

Now load your CSV using LOAD DATA LOCAL INFILE ,we need to use --local-infile before we load CSV in in new MySQL vesrsions, due to security reasons.

MySQL 5.6 – How to Import CSV into Table via Command Prompt

Since this is Windows we are dealing with, either use the double backslash

LOAD DATA LOCAL INFILE 'C:\\Users\\userName\\Downloads\\tableName.csv' 
INTO TABLE tableName 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'

or the forward slash

LOAD DATA LOCAL INFILE 'C:/Users/userName/Downloads/tableName.csv' 
INTO TABLE tableName 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'

It says so in the MySQL Documentation on LOAD DATA INFILE

The file name must be given as a literal string. On Windows, specify backslashes in path names as forward slashes or doubled backslashes. The character_set_filesystem system variable controls the interpretation of the file name.

Best Answer

Related Solutions

MySQL Import CSV File Error 13 (HY000) – Troubleshooting Guide

MySQL 5.6 – How to Import CSV into Table via Command Prompt

Related Question