How to import a CSV file into a MySQL table

by

Instead of writing a script to pull in information from a CSV file, you can link MYSQL directly to it and upload the information using the following SQL syntax.

To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty data that Excel may have put at the end of the CSV file.

You can then import it into a MySQL table by running:

load data local infile 'uniq.csv' into table tblUniq fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
    (uniqName, uniqCity, uniqComments)

as read on: Import CSV file directly into MySQL

EDIT

For your case, you’ll need to write an interpreter first, for finding the first row, and assigning them as column names.

EDIT-2

From MySQL docs on LOAD DATA syntax:

The IGNORE number LINES option can be used to ignore lines at the
start of the file. For example, you can use IGNORE 1 LINES to skip
over an initial header line containing column names:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

Therefore, you can use the following statement:

LOAD DATA LOCAL INFILE 'uniq.csv'
INTO TABLE tblUniq
FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(uniqName, uniqCity, uniqComments)

More Related Contents:

  1. Import data in MySQL from a CSV file using LOAD DATA INFILE
  2. How do I import CSV file into a MySQL table?
  3. MySQL load NULL values from CSV data
  4. Importing a csv into mysql via command line
  5. How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?
  6. Import CSV to MySQL
  7. LOAD DATA LOCAL INFILE gives the error The used command is not allowed with this MySQL version
  8. How can I correct MySQL Load Error
  9. Import CSV to Update only one column in table
  10. How I can Import data from CSV to MySQL?
  11. Import CSV to Update rows in table
  12. How to import a csv file into MySQL workbench?
  13. How do I import an SQL file using the command line in MySQL?
  14. Create table variable in MySQL
  15. Mysql: Select rows from a table that are not in another
  16. Maximum number of records in a MySQL database table
  17. Importing CSV data using PHP/MySQL
  18. MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE
  19. MySQL: Split comma separated list into multiple rows
  20. Import CSV file directly into MySQL
  21. Mysql string split [duplicate]
  22. Dump all tables in CSV format using ‘mysqldump’
  23. Importing a CSV to MySQL with different date format
  24. mysql pivot query results with GROUP BY
  25. Import SQL file into mysql
  26. Is there any reason to worry about the column order in a table?
  27. How to export data from SQL Server 2005 to MySQL [closed]
  28. Dump a mysql database to a plaintext (CSV) backup from the command line
  29. How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE
  30. MySQL load data infile – acceleration?

Leave a Comment