import data from CSV into mysql table using php

Question

I'm trying to import a csv file in my mysql table. This is my code:

<?

error_reporting(0);
mysql_connect("localhost","root","");
mysql_select_db("gerov");


$filename='test.csv';

$handle = fopen("$filename", "r");
while (($data = fgetcsv($handle, ",")) !== FALSE)
//foreach(fgetcsv($handle,",") as $data)
{
$import="INSERT into castigatori (id,cod,nume,oras,medalie) values('','$data[0]','$data[1]','$data[2]','$data[3]')";
mysql_query($import) or die("mysql_error()");
print $import."<br>";
}
fclose($handle);
print "Import done";

?>

The import it's done ok but only if I put a comma between each word in the csv file. So if I want to insert a,b,c,d it will only import if I put those commas between them. I want to write the words on a separate cell like this: a b c d without having to put the comma and making a tab delimited between the words. If I write the words with tab delimited, in my mysql table will show up like this : a ;b ;c ;d. Can anyone tell me what to do in order to insert them correctly?

Ondra Žižka · Accepted Answer · 2012-07-04 14:09:24Z

2

MySQL offers LOAD DATA INFILE.
Check http://dev.mysql.com/doc/refman/5.1/en/load-data.html .
Especially:

    [{FIELDS | COLUMNS}
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']

answered Jul 4, 2012 at 14:09

Ondra Žižka

47.2k49 gold badges228 silver badges298 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ruel · Accepted Answer · 2012-07-04 14:35:04Z

0

EDIT:

If you want the delimiter to be a space, then:

fgetcsv($handle, 0, " ")

edited Jul 4, 2012 at 14:35

answered Jul 4, 2012 at 14:07

Ruel

15.8k7 gold badges41 silver badges49 bronze badges

3 Comments

user1421688 Over a year ago

I changed this: while (($data = fgetcsv($handle, ",")) !== FALSE) to this: while (($data = fgetcsv($handle)) !== FALSE) but not in my table the words are inserted like this: a; b; c; d;

user1421688 Over a year ago

a, b, c, d this is a line in my csv file. This way the words are inserted ok my table each word with his column but I want without those commas. What do I need to change.

Ruel Over a year ago

My bad, the delimiter is the 3rd parameter. Try it now.

Collectives™ on Stack Overflow

import data from CSV into mysql table using php

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related