Forums

Trying to import CSV file to mySQL

I am new to Python and SQL and have been trying to process a csv file into SQL. I have attempted to use python of course but --local-infile=1 argument is not working.

I have also attempted using the BASH command line to use mysqlimport but to no avail.

Surely there has to be an easy way to process a csv file. I would appreciate any feedback on this.

What you need to do is:

  • Go to the "Databases" tab, and note down your MySQL hostname and username, and the name of the database you want to use (including the username and the dollar sign, eg. johnkraehling$default)

  • In a Bash console, start a MySQL session with:

    mysql -h DATABASE_HOSTNAME -u DATABASE_USERNAME 'DATABASE_NAME' -p --local-infile=1
    

...replacing the DATABASE_HOSTNAME, DATABASE_USERNAME, and DATABASE_NAME with the values you noted down from the "Databases" tab. Don't forget the single quotes around the database name, they're important.

  • Inside the MySQL session, load the data with:

    LOAD DATA INFILE "/home/johnkraehling/PATH/TO/FILE" ...
    

    ...replacing PATH/TO/FILE with the path to the file inside your home directory, and adding the extra parameters you need from as per (the MySQL docs)[https://dev.mysql.com/doc/refman/5.6/en/load-data.html] where I've put the "..."

Hi. I do the same, but, it's still saying "access denied for user ...." . i see the variable (show variables;) and it's set to ON

local_infile | ON

What else can I do?

what is the exact command that you are running and where are you running it?

Hi. Thanks for anwering...

First. i write : mysql -h myusername.mysql.pythonanywhere-services.com -u myusername 'myusername$mydb' -p --local-infile=1

then, i i write the command: LOAD DATA INFILE "/home/myusername/file.csv" INTO TABLE mytable COLUMNS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n';

the answer to the last command was: ERROR 1045 (28000): Access denied for user 'myusername'@'%' (using password: YES)

Thanks!

Then you're using the wrong password. You need to use the password that you provided when you activated your MySQL server and not your account password.

Hi !

I got the exact same thing Ricardou! I still don't know how to fix it because I am using the password of MySQL not the one for my pythonanywhere account... :(

Hi,

Try to use "LOCAL" like this:

LOAD DATA LOCAL INFILE "/home/myusername/file.csv" INTO TABLE mytable COLUMNS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n';

@acales you will have to show more detail, such as what command you used, and or which suggestions in this thread you have tried, and what the exact error is etc.

Thank you so much for your answer.

Actually it just worked for me using "LOCAL" in my query. So maybe ricardou should try too :)

Hi. thanks for your answere, but, im using mysql password (not account website password)... and, tying changing password of mysql user, and the result was the same... remember that i was entered to mysql shell but i cant load csv file... its the only command i can't execute...

Are you using the "LOCAL" keyword in your command, like @acales suggested? In your post at April 27, 2018, 1:47 p.m., you don't have it in the command. If you are, could you show us the exact command you're using now?

Ok. Now with LOCAL work.... Thanks very much....

OK -- thanks for confirming.

Hi all. I have followed all the suggestions here, and it's still not working for me.

Here are some details of what I did:

First I wrote mysql -h myusername.mysql.pythonanywhere-services.com -u myusername 'myusername$default' -p --local-infile=1

It prompts me to enter my password, and I enter my Database password.

I then see this returned from the console, so everything appears to be working and my password seems to have been entered correctly and accepted by MySQL.

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 131132612
Server version: 5.6.27-log MySQL Community Server (GPL)

LOAD DATA LOCAL INFILE "/home/foresight/free-zipcode-database.csv" INTO TABLE zipcode_census.zipcode_data
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Note I entered the keyword 'LOCAL' as people have suggested. But, I still receive this following error.

ERROR 1142 (42000): INSERT command denied to user 'foresight'@'10.0.0.141' for table 'zipcode_data'

Any suggestions? It's a very frustrating problem so far.

[edit by admin: formatting]

You're trying to load the data into the table zipcode_census.zipcode_data, where zipcode_census specifies the database name. Your databases all have your username plus a dollar sign at the start of their names, so you should use foresight$zipcode_census.zipcode_data instead, ie.

LOAD DATA LOCAL INFILE "/home/foresight/free-zipcode-database.csv" INTO TABLE foresight$zipcode_census.zipcode_data
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Thanks Giles! That worked perfectly!

Excellent -- thanks for confirming!

I am getting the error ERROR 1064 (42000): 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 '- h topplethepat.mysql.pythonanywhere-services.com -u topplethepat 'topplethepat' at line 1

I used this syntax:

mysql> -h topplethepat.mysql.pythonanywhere-services.com -u topplethepat  'topplethepat$palimpseste' -p --local-infile=1
-> LOAD DATA INFILE "/home/topplethepat/amrita/041018_PCITags_First5K.csv" INTO TABLE mytable COLUMNS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAP
-> ED BY '"' LINES TERMINATED BY '\n';

I also tried:

LOAD DATA INFILE "/home/topplethepat/amrita/041018_PCITags_First5K.csv" INTO TABLE mytable;

What am i doing wrong here? I don't get prompted for a password like people mentioned above.Thanks.

Those are not SQL commands, don't run them in a MySQL console, run them in a Bash console.

Thanks Glenn!

So now I'm running into a different error with this syntax:

mysql>  CREATE TABLE topplethepat$palimpseste.amrita
-> LOAD DATA INFILE "/home/topplethepat/amrita/041018_PCITags_First5K.csv" INTO TABLE amrita;

I'm getting

ERROR 1064 (42000): 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 'LOAD DATA INFILE "/home/topplethepat/amrita/041018_PCITags_First5K.csv" INTO TAB' at line 2

Is the server version Oracle? I tried using LOCAL per the q&a above, got same error. I also tried with TABLE topplethepat$palimpseste.amrita but same error come up. Thanks for your help.

The server is not an Oracle server, it's a MySQL server. If you want to load data from a file into your database, see http://help.pythonanywhere.com/pages/LoadDataInfile/

Thanks for the link, but that part is complete and I don't think it applies. I loaded it in, but have to create the table to load it in to, and that's the part throwing the error.

Then create the table first making sure that your column definitions match your csv.

thanks, got it now :)

Great!

I imported one csv file in and everything was working great. Then, because that one is so large, I decided to make a test one with 13 rows in it and I created a new table for it and loaded it in, getting the message

Query OK, 1 row affected, 6 warnings (0.05 sec)

Records: 1 Deleted: 0 Skipped: 0 Warnings: 6

When I did the command

SELECT * FROM amrita_text;

I only got one row back, and there are 13 rows in it. I went back to look at the file. For some reason it's blue (i.e. it's a link) whereas the other uploaded files are black and not links. I don't know if there's any connection between that and the file not loading properly? Why would it not be loading all of the rows?

I just repeated the LOAD INFILE command now and all the rows are showing up. Not sure what that was about earlier but it's on track now! i'm getting

Query OK, 13 rows affected, 18 warnings (0.02 sec)

Records: 13 Deleted: 0 Skipped: 0 Warnings: 18

How can I access the warnings?

Check out the show warnings command. Very odd that it worked differently the two times you ran it, though!