Import CSV to mysql table

Question

What is the best/fastest way to upload a csv file into a mysql table? I would like for the first row of data be used as the column names.

Found this:

How to import CSV file to MySQL table

But the only answer was to use a GUI and not shell?

Answer 1

To load data from text file or csv file the command is

load data local infile 'file-name.csv'
into table table-name
fields terminated by '' enclosed by '' lines terminated by '\n' (column-name);

In above command, in my case there is only one column to be loaded so there is no "terminated by" and "enclosed by" so I kept it empty else programmer can enter the separating character . for e.g . ,(comma) or " or ; or any thing.

**for people who are using mysql version 5 and above **

Before loading the file into mysql must ensure that below tow line are added in side etc/mysql/my.cnf

to edit my.cnf command is

sudo vi /etc/mysql/my.cnf

[mysqld]  
local-infile

[mysql]  
local-infile  

Answer 2

As others have mentioned, the load data local infile works just fine. I tried the php script that Hawkee posted, but didnt work for me. Rather than debug it, here's what i did:

1) copy/paste the header row of the CSV file into a txt file and edit with emacs. add a comma and CR between each field to get each on on it's own line.
2) Save that file as FieldList.txt
3) edit the file to include defns for each field (most were varchar, but quite a few were int(x). Add create table tablename ( to the beginning of the file and ) to the end of the file. Save it as CreateTable.sql
4) start mysql client with input from the Createtable.sql file to create the table
5) start mysql client, copy/paste in most of the 'LOAD DATA INFILE' command subsituting my table name and csv file name. Paste in the FieldList.txt file. Be sure to include the 'IGNORE 1 LINES' before pasting in the field list

Sounds like a lot of work, but easy with emacs.....

Answer 3

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 4

I wrestled with this for some time. The problem lies not in how to load the data, but how to construct the table to hold it. You must generate a DDL statement to build the table before importing the data.

Particularly difficult if the table has a large number of columns.

Here's a python script that (almost) does the job:

#!/usr/bin/python    
import sys
import csv

# get file name (and hence table name) from command line
# exit with usage if no suitable argument   
if len(sys.argv) < 2:
   sys.exit('Usage: ' + sys.argv[0] + ': input CSV filename')
ifile = sys.argv[1]

# emit the standard invocation
print 'create table ' + ifile + ' ('

with open(ifile + '.csv') as inputfile:
   reader = csv.DictReader(inputfile)
   for row in reader:
      k = row.keys()
      for item in k:
         print '`' + item + '` TEXT,'
      break
   print ')\n'

The problem it leaves to solve is that the final field name and data type declaration is terminated with a comma, and the mySQL parser won't tolerate that.

Of course it also has the problem that it uses the TEXT data type for every field. If the table has several hundred columns, then VARCHAR(64) will make the table too large.

This also seems to break at the maximum column count for mySQL. That's when it's time to move to Hive or HBase if you are able.

Answer 5

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

Answer 6

Import CSV Files into mysql table

LOAD DATA LOCAL INFILE 'd:\\Site.csv' INTO TABLE `siteurl` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Character   Escape Sequence
\0      An ASCII NUL (0x00) character
\b      A backspace character
\n      A newline (linefeed) character
\r      A carriage return character
\t      A tab character.
\Z      ASCII 26 (Control+Z)
\N      NULL

visits : http://www.webslessons.com/2014/02/import-csv-files-using-php-and-mysql.html