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!

Hi there, So I've followed everything suggested but I'm coming up with another problem. Here's what I've done: I put that command through the bash console, got prompted for my password and then got put through to the mysql session. When first I tried to import my file:

LOAD DATA INFILE '/home/tchan78/mysite/park_locations.csv' 
INTO TABLE park_locations FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(park_name, house_number, street_address, post_code, longitude, latitude);

it came up with this error: ERROR 1045 (28000): Access denied for user 'tchan78'@'%' (using password: YES)

I also importing my file exactly the same as above but adding the keyword 'LOCAL' and it came up with this error: ERROR 2 (HY000): File '/home/tchan78/mysite/park_locations.csv' not found (Errcode: 2 - No such file or directory)

I've double checked that my file directory is correct. I've made sure the csv file has the same amount of columns as the table i want to import it into... Can anyone pleasee help me... Thank you!

The LOCAL keyword is necessary. Run this in a Bash console and send us the result:

cat /home/tchan78/mysite/park_locations.csv

I'm getting the same ERROR 1045 (28000): Access denied for user 'myusername'@'%' (using password: YES) error as deleted-user-3762704 | 4 posts | April 27, 2018 but it never asked me for a password.

Is that happening when you're trying to start a MySQL console? Or at some other time?

I'm trying to load data from a csv file like above. I seem to have got passed that error by leaving out the database name when I start the sql session. But I have a new error Here is what I am doing: mysql -h username.mysql.pythonanywhere-services.com -u username -p --local-infile=1

Then when the sql session starts I do: USE databasename

LOAD DATA LOCAL INFILE "path to csv file" INTO TABLE tablename COLUMNS TERMINATED BY '\r\n' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n';

And I get all nulls entered instead of the data

Make sure that you are using the full path to the csv and not a relative path and also check that you actually have the data you expect in the csv that you are importing and that the column names match up with your table's column names.

I have checked all that and it is fine. This is a snippet of the csv file (is it the correct format, the table has 2 columns ID is autoincrement and the second column is a varchar called Institutions):

Institutions

National University of Ireland, Galway

National College of Art and Design

St Angela's College of Home Economics

University College Cork

Dublin City University

Dublin Institute of Technology

Limerick IT

University of Limerick

This is the start of the output I get (It already had 4 rows):

+----+----------------------------------------+

| id | Institutions |

+----+----------------------------------------+

| 1 | National University of Ireland, Galway |

| 2 | ATU |

| 31 | Pci |

| 32 | Xx |

| 33 | NULL |

| 34 | NULL |

| 35 | NULL |

| 36 | NULL |

| 37 | NULL |

Thanks for the help.

If you try to load a file in the MySQL console it would not work as the file is not on the server where MySQL is running. If I understand correctly your situation, you need to open that file in the python script and write it ti he database over MySQL connection.