Importing a csv into mysql via command line

user_828824 0
Viewed 214721

I'm trying to import a very large .csv file (~4gb) into mysql. I was considering using phpmyadmin, but then you have a max upload size of 2mb. Someone told me that I have to use the command line.

I was going to use these directions to import it: http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html#c5680

What would be the command to set the first row in the .csv table as the column names in the mysql table? This option is available through phpmyadmin, so their must be a mysql command line version too, right?. Please help me. Thank you.

-Raj

load-data-infile csv mysql
8 Answers

try this: 

mysql -uusername -ppassword --local-infile scrapping -e "LOAD DATA LOCAL INFILE 'CSVname.csv'  INTO TABLE table_name  FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'"
user_60978820

You can simply import by

mysqlimport --ignore-lines=1 --lines-terminated-by='\n' --fields-terminated-by=',' --fields-enclosed-by='"' --verbose --local -uroot -proot db_name csv_import.csv

Note: Csv File name and Table name should be same

user_37829230

Another option is to use the csvsql command from the csvkit library.

Example usage directly on command line:

csvsql --db mysql:///test --tables yourtable --insert yourfile.csv

This can be executed directly on the command line, or built into a python or shell script for automation if you need to do this for a number of files.

csvsql allows you to create database tables on the fly based on the structure of your csv, so it is a lite-code way of getting the first row of your csv to automagically be cast as the MySQL table header.

Full documentation and further examples here: https://csvkit.readthedocs.io/en/1.0.3/scripts/csvsql.html

user_17258720

I know this says command line, but just a tidbit of something quick to try that might work, if you've got MySQL workbench and the csv isn't too large, you can simply

  • SELECT * FROM table
  • Copy entire CSV
  • Paste csv into the query results section of Workbench
  • Hope for the best

I say hope for the best because this is MySQL Workbench. You never know when it's going to explode

If you want to do this on a remote server, you would do

mysql -h<server|ip> -u<username> -p --local-infile bark -e "LOAD DATA LOCAL INFILE '<filename.csv>'  INTO TABLE <table>  FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'"

Note, I didn't put a password after -p as putting one on the command line is considered bad practice

user_19255740

Most answers missing an important point like if you have created csv file exported from Microsoft Excel on windows and importing the same in linux environment, you will get unexpected result.

the correct syntax would be

load data local infile 'file.csv' into table table fields terminated by ',' enclosed by '"' lines terminated by '\r\n'

here the difference is '\r\n' as against simply '\n

user_11249930
Related