How to import a CSV file into a MySQL table

Question

How can I import a CSV file into a MySQL table? I would like for the first row of data be used as the column names.

I read How do I import CSV file into a MySQL table?, but the only answer was to use a GUI and not a shell?

Answer 1

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) 

Answer 2

Here's a simple PHP command line script that will do what you need:

<?php  $host = 'localhost'; $user = 'root'; $pass = ''; $database = 'database';  $db = mysql_connect($host, $user, $pass); mysql_query("use $database", $db);  /********************************************************************************/ // Parameters: filename.csv table_name  $argv = $_SERVER[argv];  if($argv[1]) { $file = $argv[1]; } else {     echo "Please provide a file name\n"; exit;  } if($argv[2]) { $table = $argv[2]; } else {     $table = pathinfo($file);     $table = $table['filename']; }  /********************************************************************************/ // Get the first row to create the column headings  $fp = fopen($file, 'r'); $frow = fgetcsv($fp);  foreach($frow as $column) {     if($columns) $columns .= ', ';     $columns .= "`$column` varchar(250)"; }  $create = "create table if not exists $table ($columns);"; mysql_query($create, $db);  /********************************************************************************/ // Import the data into the newly created table.  $file = $_SERVER['PWD'].'/'.$file; $q = "load data infile '$file' into table $table fields terminated by ',' ignore 1 lines"; mysql_query($q, $db);  ?> 

It will create a table based on the first row and import the remaining rows into it. Here is the command line syntax:

php csv_import.php csv_file.csv table_name