Importing a csv into mysql via command line

Question

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

User · Answer

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 · Answer

For importing csv with a header row using mysqlimport  just add  --ignore-lines N    ignores the first N lines of the data file   This option is described in the page you ve linked

User · Answer

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   quot   lines terminated by   r n  here the difference is   r n  as against simply   n

User · Answer

Try this command   load data local infile  file csv  into table table  fields terminated by      enclosed by      lines terminated by   n    column1  column2  column3        The fields here are the actual  table fields that the data needs to sit in  The enclosed by and lines terminated by are optional and can help if you have columns enclosed with double-quotes such as Excel exports  etc   For further details check the manual   For setting the first row as the table column names  just ignore the row from being read and add the values in the command

User · Answer

You can put it in the following way   LOAD DATA LOCAL INFILE  C  Users userName Downloads tableName csv   INTO TABLE tableName  FIELDS TERMINATED BY      LINES TERMINATED BY   n

User · Answer

You could do a   mysqlimport --columns  head -n 1  yourfile  --ignore-lines 1 dbname  yourfile    That is  if your file is comma separated and is not semi-colon separated  Else you might need to sed through it too

User · Answer

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 lt server ip gt  -u lt username gt  -p --local-infile bark -e  LOAD DATA LOCAL INFILE   lt filename csv gt    INTO TABLE  lt table gt   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 · Answer

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 · Answer

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

[mysql] Importing a csv into mysql via command line

Examples related to mysql

Examples related to csv

Examples related to load-data-infile