Importing CSV Into MySQL
Posted by Nessa | Posted in mysql | Posted on December 8, 2007
11
The newer versions of phpMyAdmin for some reason no longer include the option to import CSV files. If you are trying to import an Excel or other delimited file, first make sure that it’s in CSV format (with Excel you can export it as CSV). Then log into phpMyAdmin or your MySQL prompt and run this command:
load data local infile ‘/path/to/file.csv‘ into table yourtablename
fields terminated by ‘,’
enclosed by ‘”‘
lines terminated by ‘\n’;
With phpMyAdmin this is done within the ‘SQL’ section, indicated by the ‘SQL’ tab.
Related posts:
This is very very easy to do in SQLYog – some great software for interacting with MySQL dbs. Right click on the table you want to import the data into and the command is right there.
The problem with SQLYog is that you have to compile and install it (which only the root user can do), which can be a royal pain in the ass. It’s nice software, but I wouldn’t go through that much trouble to import a CSV file when a few lines of code will do the trick.
you can use that SQL query in a small PHP file with an HTML form if you do this often, to speed up the process a bit. Something like:
damnit, it didn’t post any of that. I’m sad now.
if (array_key_exists(‘file’, $_POST)
{
//upload the file in-line
$url = curl_init(“http://domain.com/upload.php”);
curl_setopt($url, CURLOPT_POST, $_POST['file']);
$message = curl_exec($url)
//process SQL import
$mysqli = mysqli_connect(“127.0.0.1″, “blah”, “blah”, “blah”);
$query = “LOAD DATA LOCAL INFILE `/path/to/uploaded/file.csv` INTO TABLE `blah` FIELDS TERMINATED BY `,` ENCLOSED BY `”` LINES TERMINATED BY ‘\r\n’;”;
if ($mysqli_query($query, $mysqli))
{
$message = “Import successful”;
} else {
//best error reporting ever
$message = “Error!”
}
} else {
$message = “”;
}
the second and third $message = should be $message.=
//edited for spelling — geez, do you post drunk or something? :b
First day with my new fingers :$
You don’t have to have SQLyog installed on your MySQL server. Just install it on your local box and connect to your MySQL server from there.
SQLyog is a lot easier to use (and has tons of other applications).
sir i am trying to upload my *.csv file into mysql. it showing file not found. can u able to explain me how can i solve this error
that means that the path to your csv file is incorrect…try using the absolute path i.e /home/user/file.cav
FYI…dude, don’t ‘sir’ me. I’m a chick, at least I have been ever since my penis was removed.
[...] 8th 2007 9:53pm [-] From: v-nessa.net [...]