How do I import a CSV file into MySQL

January 22, 2016 7:48 PM

I am using mysql for the first time and I need to create a table using syntax to population a table with approximately 6000 rows. I am having trouble figuring out Bulk Import and Import From and am finding the explanations on the internet to be over my head. Can anyone explain this using language and steps suitable for a beginner?
posted by DorothySmith to Computers & Internet (12 answers total)

Do you have to do this more than once? I would use MySql Workbench - you can use the import wizard to import the data from a CSV file.
posted by nightwood at 8:10 PM on January 22, 2016 [1 favorite]

Response by poster: nightwood, thanks! What I'm actually trying to do is write the program myself and write the syntax. Any advice?
posted by DorothySmith at 8:12 PM on January 22, 2016

Are you trying to do it within MySQL or an external program (and if so, what language?). Is your table already created? Or do you just want a program that will take a CSV file, create the table and load the data?
posted by nightwood at 8:27 PM on January 22, 2016

Response by poster: The table's created and it's super small (5 variables). Both options would be good. I'm trying to use SQL language.
posted by DorothySmith at 8:37 PM on January 22, 2016

So, if your table is called 'myTable' and your file is called 'myFile.csv' and the CSV file has 1 line that has the column names in it, you would do something like this in mysql:

LOAD DATA INFILE "myFile.csv"
INTO TABLE myTable
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;


See mysql load data command reference.

There is also warns you that if you made your CSV on windows or in wordpad, you may have to change the second to last like to either:
LINES TERMINATED BY '\r\n'
or
LINES TERMINATED BY '\r'

feel free to memail me if you need help.
posted by nightwood at 8:53 PM on January 22, 2016

Response by poster: Thanks again! Whenever I try to do the load data infile I get the error message "The mysql server is running with the --secure-file-priv option so it cannot execute this statement. Do you know how to get around this?
posted by DorothySmith at 9:00 PM on January 22, 2016

See this question and answer. You may be able to move your file to a location that allows loading.
posted by nightwood at 9:10 PM on January 22, 2016

Response by poster: Thank you, it's truthfully just above my head :S
posted by DorothySmith at 9:27 PM on January 22, 2016

If you type:
SHOW VARIABLES LIKE "secure_file_priv";

in mysql, what does it say?
posted by nightwood at 9:33 PM on January 22, 2016

Response by poster: Thank you so much for the help. I'm going to do it the long way because I can't seem to get it. Thank you anyway! I hope you have a nice evening.
posted by DorothySmith at 9:48 PM on January 22, 2016

Does your server have phpmyadmin installed? That is fairly standard with most webhosting.

If so, you can import using phpmyadmin - typical instructions here.
posted by flug at 12:05 AM on January 23, 2016 [1 favorite]

Unless you hafta hafta use mysql, and are doing this to learn SQL, it's a lot simpler to start with database-in-a-file systems like SQLite. No admin issues, you database is a portable file you can use anywhere. Its CSV Import is very simple, and will define and populate table columns automatically if you wish.

An even simpler tool for doing SQL on CSV is q. It's very impressive indeed.
posted by scruss at 5:18 AM on January 23, 2016 [1 favorite]