How I can Import data from CSV to MySQL?

by

You can specify the columns and mark the unnecessary columns as ‘@dummy’.

LOAD DATA INFILE 'data.csv'
INTO TABLE t1
(column1, @dummy, column2, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy)
FIELDS TERMINATED BY '\t' ENCLOSED BY 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

Replace t1, column1 and column2 as you like. To set other columns which are not in the data file, you can do it like this:

LOAD DATA INFILE 'data.csv'
INTO TABLE t1
(column1, @dummy, column2, @dummy, @val, @dummy, @dummy, @dummy, @dummy, @dummy)
FIELDS TERMINATED BY '\t' ENCLOSED BY 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
SET column3 = "test", column4 = CURRENT_TIMESTAMP, column5 = @val/10;

For further reference, I recommend you to take a look at the MySQL reference.

More Related Contents:

  1. Import data in MySQL from a CSV file using LOAD DATA INFILE
  2. How to import a CSV file into a MySQL table
  3. How can I correct MySQL Load Error
  4. Import CSV to Update only one column in table
  5. Import CSV to Update rows in table
  6. How to import a csv file into MySQL workbench?
  7. SQL split values to multiple rows
  8. How can I output MySQL query results in CSV format?
  9. How do I import CSV file into a MySQL table?
  10. What is the opposite of GROUP_CONCAT in MySQL?
  11. MySQL load NULL values from CSV data
  12. Importing CSV data using PHP/MySQL
  13. How to import data from text file to mysql database
  14. MySQL: Split comma separated list into multiple rows
  15. How to import XML file into MySQL database table using XML_LOAD(); function
  16. Migrate from Oracle to MySQL
  17. Dump all tables in CSV format using ‘mysqldump’
  18. How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?
  19. Import CSV to MySQL
  20. LOAD DATA LOCAL INFILE gives the error The used command is not allowed with this MySQL version
  21. Importing a CSV to MySQL with different date format
  22. mysql pivot query results with GROUP BY
  23. Import SQL file into mysql
  24. How to export data from SQL Server 2005 to MySQL [closed]
  25. Dump a mysql database to a plaintext (CSV) backup from the command line
  26. How to Import Multiple csv files into a MySQL Database
  27. How can I import a database with MySQL from terminal?
  28. ERROR 1148: The used command is not allowed with this MySQL version
  29. How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE
  30. How to join two tables using a comma-separated-list in the join field

Leave a Comment