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?

Measurable

And even the GUI solution does not take the column names from the csv... you need to create the entire table prior to importing- – Senter

The question already has an answer here #3635666 – Communal

the answer accepted on the question you are linking to was using a GUI. The answer you are references was provided yesterday while this question (answer) is from 2012. – Measurable

or use mysqlimport mentioned in the origin question. – Customary

This is a duplicate of #3635666, even if you don't like the GUI. You can post a bounty asking for a better question. In this case, a non-GUI based answer was provided approximately 1 year after you asked this question anyway. Am out of votes for the day, but will return tomorrow. – Viridi

SQL has a LOAD DATA INFILE that can be used to read and format CSV files. It is very powerful, but not very well-documented. This article does a good job of explaining how to use it: blog.terresquall.com/2021/11/… – Iqbal

Does this answer your question? How do I import CSV file into a MySQL table? – Viridi

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)

Fbi

Instead of removing the first line you can add IGNORE 1 LINES to the query – Kiersten

Do you know if there is a way to set file path to csv file? – Nigeria

How to debug this command when its failing? i am trying to load a file with this command but its doing nothing. – Hampstead

how about if I would like to ignore a column in csv? – Eiffel

how to give permission for my csv local file to be accessed by mysql server running on aws (rds) – Brut

@Brut what error do you get? Are you using LOAD DATA LOCAL INFILE? or just LOAD DATA INFILE? – Fbi

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 = mysqli_connect($host, $user, $pass) or die ("could not connect to mysql");
mysqli_select_db($db, $database) or die ("no database");
/********************************************************************************/
// 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);";
mysqli_query($db, $create) or die(mysqli_error($db));

/********************************************************************************/
// Import the data into the newly created table.

$file = $_SERVER['PWD'].'/'.$file;
$q = "LOAD DATA INFILE '$file' INTO TABLE $table FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' ignore 1 lines;";
mysqli_query($db, $q) or die(mysqli_error($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

Donelson

Awesome script. For those with double quoted CSV files (read most people) add ` ENCASED IN '\"'` to fields terminated by ',' ...it even works with partially double quoted CSVs. – Quintilla

I think you mean ENCLOSED BY '\"'... also, lots of people will need LINES TERMINATED BY '\r\n' if using a CSV from Windows. And finally, escaping the field names with backticks is wise in case there are spaces: $columns .= "`$column` varchar(250)"; – Marva

This answer is much better than the accepted answer. In particular it allows what the OP asked for, and I also want: "the first row of data be used as the column names". (I would prefer a script in Python, so I don't have to install PHP, but it shouldn't be hard to port it.) – Radmilla

@YumYumYum Can you elaborate more on the problem you're having? – Donelson

Can I buy you a beer? – Homeomorphism

This is a link only answer. Put the relevant code into the post itself, otherwise the answer is useless when the link dies. – Pentylenetetrazol

Answer 3

if you have the ability to install phpadmin there is a import section where you can import csv files to your database there is even a checkbox to set the header to the first line of the file contains the table column names (if this is unchecked, the first line will become part of the data

Whitewood

I am really surprised that you have to use an add-on like phpadmin to get this functionality, Thanks for your answer – Tarantula

THis just made my day – Cicada

Answer 4

First create a table in the database with same numbers of columns that are in the csv file.

Then use following query

LOAD DATA INFILE 'D:/Projects/testImport.csv' INTO TABLE cardinfo
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'

Pestilent

how about if I would like to ignore a column in csv? – Eiffel

Answer 5

If you start mysql as "mysql -u -p --local-infile ", it will work fine

Hassan

Answer 6

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  

Deerdre

Answer 7

I wrote some code to do this, i'll put in a few snippets:

$dir = getcwd(); // Get current working directory where this .php script lives
$fileList = scandir($dir); // scan the directory where this .php lives and make array of file names

Then get the CSV headers so you can tell mysql how to import (note: make sure your mysql columns exactly match the csv columns):

//extract headers from .csv for use in import command
$headers = str_replace("\"", "`", array_shift(file($path)));
$headers = str_replace("\n", "", $headers);

Then send your query to the mysql server:

mysqli_query($cons, '
        LOAD DATA LOCAL INFILE "'.$path.'"
            INTO TABLE '.$dbTable.'  
            FIELDS TERMINATED by \',\' ENCLOSED BY \'"\'
            LINES TERMINATED BY \'\n\'
            IGNORE 1 LINES
            ('.$headers.')
            ;
        ')or die(mysql_error());

Inclinometer

Answer 8

LOAD DATA INFILE statement allows loading data from a file directly into a table:

LOAD DATA INFILE 'path_to_csv_file'
INTO TABLE your_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

An alternative approach involves utilizing the dbForge Studio IDE for MySQL, which facilitates seamless data export and import. The Data Export and Data Import Wizards make the process easy, with step-by-step instructions. You can export specific rows or a range of rows, and you can import data into a new table or an existing one. You can export MySQL data to a variety of formats, including HTML, TXT, XLS, XLSX, MDB, RTF, PDF, JSON, XML, CSV, ODBC, DBF, SQL, and Google Sheets.

Outstand

Answer 9

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.

Anasarca

Answer 10

Here's how I did it in Python using csv and the MySQL Connector:

import csv
import mysql.connector

credentials = dict(user='...', password='...', database='...', host='...')
connection = mysql.connector.connect(**credentials)
cursor = connection.cursor(prepared=True)
stream = open('filename.csv', 'rb')
csv_file = csv.DictReader(stream, skipinitialspace=True)

query = 'CREATE TABLE t ('
query += ','.join('`{}` VARCHAR(255)'.format(column) for column in csv_file.fieldnames)
query += ')'
cursor.execute(query)
for row in csv_file:
    query = 'INSERT INTO t SET '
    query += ','.join('`{}` = ?'.format(column) for column in row.keys())
    cursor.execute(query, row.values())

stream.close()
cursor.close()
connection.close()

Key points

• Use prepared statements for the INSERT
• Open the file.csv in 'rb' binary
• Some CSV files may need tweaking, such as the skipinitialspace option.
• If 255 isn't wide enough you'll get errors on INSERT and have to start over.
• Adjust column types, e.g. ALTER TABLE t MODIFY `Amount` DECIMAL(11,2);
• Add a primary key, e.g. ALTER TABLE t ADD `id` INT PRIMARY KEY AUTO_INCREMENT;

Undertone

Answer 11

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

Laura

Answer 12

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

1) Copy/paste the header row of the CSV file into a txt file and edit it with Emacs. Add a comma and CR between each field to get each on its own line.
2) Save that file as FieldList.txt.
3) Edit the file to include definitions 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 the mysql client with input from the Createtable.sql file to create the table.
5) Start the mysql client, copy/paste in most of the 'LOAD DATA INFILE' command substituting 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.

It sounds like a lot of work, but it's easy with Emacs...

Produce

Answer 13

Use TablePlus application: Right-Click on the table name from the right panel Choose Import... > From CSV Choose CSV file Review column matching and hit Import All done!

Stadler

Answer 14

So I attempted to use the script give by Hawkee but some of the commands are outdated. Using mysql_X is depreciated and needs to be replaced by mysqli_x. After doing some troubleshooting I wrote the following script and it is working nicely.

Please note: the following code assumes that you are entering floats. I used this script to import percentiles from the WHO for stats related to growth.

use -drop (before the file name) if you want to drop the table

<?php
//This script is for importing the percentile values.
//Written by Daniel Pflieger @ GrowlingFlea Software

$host = 'localhost';
$user = 'root';
$pass = '';
$database = '';

//options.  This is what we need so the user can specify whether or not to drop the table
$short_options = "d::";
$options = getopt($short_options);

//check if the flag "-drop" is entered by the end user.  
if (!empty($options) &&  $options['d'] != "rop"){
    echo "The only available argument is -drop \n";
    exit;
} else if (!empty($options)){
    $dropTable = true;

} else {
    $dropTable = false;

}

//we use mysqli_* since this is required with newer versions of php
$db = mysqli_connect($host, $user, $pass, $database);

// argv changes if the drop flag is used. here we read in the name of the .csv file we want to import
if (isset($argv[1]) && empty($options) ) {
    $file = $argv[1];
} else  if (isset($argv[2]) && $options[1] = "rop" ) {
    $file = $argv[2];
}

//we call the table name the name of the file.  Since this script was used to import who growth chart info
//I appended the '_birth_to_5yrs' to the string.  You probably want to remove this and add something that
//makes sense to you
$table = pathinfo($file);
$table = "who_" . $table['filename'] . "_birth_to_5yrs";
$table = str_replace('-', '_', $table);

// We read the first line of the .csv file.  It is assumed that these are the headers.
$fp = fopen($file, 'r');
$frow = fgetcsv($fp);
$columns = '';

//we get the header names and for this purpose we make every value 'float'.  If you are unsure of
//the datatype you can probably use varchar(250).
foreach($frow as $column) {
    $columns .= "`" .$column . "` float,";

}

//drop the table to prevent data issues, if that is what the end user selects
if ($dropTable) {
    mysqli_query($db, "drop table if exists $table");

}

// here we form the create statement and we create the table.
// we use the mysqli_real_escape_string to make sure we dont damage the DB
$create = "create table if not exists $table ($columns);";
$create = str_replace(',)', ')', $create);
$create = mysqli_real_escape_string($db, $create);
mysqli_query($db, $create);

// We read the values line-by-line in the .csv file and insert them into the table until we are done.
while ($frow = fgetcsv($fp)){
    $insert = implode(", ", $frow);
    $insert = "Insert into $table VALUES ( $insert )";
    $insert = mysqli_real_escape_string($db, $insert);
    $insert = mysqli_query($db, $insert);
}
   

An example of how to run the script:

php ../git/growlingflea-dev-tools/importCSV.php -drop wfh-female-percentiles-expanded-tables.csv

Glioma

Answer 15

I have google search many ways to import csv to mysql, include " load data infile ", use mysql workbench, etc.

when I use mysql workbench import button, first you need to create the empty table on your own, set each column type on your own. Note: you have to add ID column at the end as primary key and not null and auto_increment, otherwise, the import button will not visible at later. However, when I start load CSV file, nothing loaded, seems like a bug. I give up.

Lucky, the best easy way so far I found is to use Oracle's mysql for excel. you can download it from here mysql for excel

This is what you are going to do: open csv file in excel, at Data tab, find mysql for excel button

select all data, click export to mysql. Note to set a ID column as primary key.

when finished, go to mysql workbench to alter the table, such as currency type should be decimal(19,4) for large amount decimal(10,2) for regular use. other field type may be set to varchar(255).

Hedwighedwiga