How to Add Multiple Lines of .csv File Into Database

[tradingjamie]

Hey guys,

Once again I have encountered a slight problem I cannot seem to overcome - Id be really grateful if you could help me out (again!, this forum is great - I promise Ill contribute when I get enough knowledge).

Basically, I have a csv file - here's an example of the data in it:

id,title,category,sub,description,tag,price,imgs,imgl,link,source,sourceimg,status
9,Regulated Linear Single Output DC Power supply 5v 3Amp,"Cables, Parts & Power Supplies",43-Power Supplies,"Linear Power Supply designed to meet EN60950. Equivalent to EMS Ref D500. Input 100-240Vac so as compatible with power supplies thoughout the world. Remote sense facility to compensate for voltage drop across load leads. Output connections via fast on terminals with 0.25inch and 0.11inch connectors. Adjustable output voltage with low ripple. Over voltage protection on 5v output. Cover kit available, please ask if required.",R3 Technology,49.7,,http://images.productserve.com/preview/239/571256.jpg,http://www.awin1.com/pclick.php?p=571256&a=72979&m=239,Budget Batteries,,1
10,Regulated Linear Single Output DC Power supply 12-15v 6.8A,"Cables, Parts & Power Supplies",43-Power Supplies,"Linear Power Supply designed to meet EN60950. EMS Ref D505 Input 100-240Vac so as compatible with power supplies thoughout the world. Remote sense facility to compensate for voltage drop across load leads. Output connections via fast on terminals with 0.25inch and 0.11inch connectors. Adjustable output voltage with low ripple. Cover kit available, please ask if required.",R3 Technology,115.82,,http://images.productserve.com/preview/239/571257.jpg,http://www.awin1.com/pclick.php?p=571257&a=72979&m=239,Budget Batteries,,1
11,Regulated Linear Single Output DC Power supply 24-28v 1.2A,"Cables, Parts & Power Supplies",43-Power Supplies,"Linear Power Supply designed to meet EN60950. EMS Ref D506 Input 100-240Vac so as compatible with power supplies thoughout the world. Remote sense facility to compensate for voltage drop across load leads. Output connections via fast on terminals with 0.25inch and 0.11inch connectors. Adjustable output voltage with low ripple. Cover kit available, please ask if required.",R3 Technologies,47.15,,http://images.productserve.com/preview/239/571258.jpg,http://www.awin1.com/pclick.php?p=571258&a=72979&m=239,Budget Batteries,,1

I renamed it from the .csv to .txt and uploaded it to my server

Anyway, I have this code to try and add it into my database:

	$query = "LOAD DATA LOCAL INFILE 'the_feed.txt' INTO TABLE tbprofo FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'";
	$result1 = mysql_query($query, $profoconnection) or die(mysql_error());

The only problem is that it only adds one line of my .txt file - the first line:

id,title,category,sub,description,tag,price,imgs,imgl,link,source,sourceimg,status

The irony is that this first line is the heading, and not actually any proper data.

So my question - does anyone know how to edit my code so that it adds all the data in my csv file?

Thanks guys!

[BlaineSch]

Id make your own script to do it. Open it, break it by line then do something like:

<?php
$file = explode("\n", file_get_contents('file.txt'));
$headers = array_shift($file); //headers
foreach($file as $line) {
//query
    $query = mysql_query("INSERT INTO example ($headers) VALUES ($line)");
}
?>

Obviously wont work out of the box but I am sure you get a good idea for it.

[tradingjamie]

thank you blainesch

[BlaineSch]

Your welcome, let us know if anything does not work correctly.

+Rep appreciated.

[tradingjamie]

In an effort to attempt to help others in my situation, here is the code in order to import csv data into mysql database:

<?php

$datanames = file_get_contents("datanames.csv");
$txt = preg_replace('/\r\n|\r/', "\n", $datanames);
$file = explode("\n", $txt);

$numlines = count($file);

for($i=0; $i<$numlines; $i++)
{
echo "Line ".$i.": ".$file[$i]."<br />";
}



for($l=0; $l<$numlines; $l++)
{
$data = explode(",", $file[$l]);
$numcells = count($data);

//set all the data for $madata
for($i=0; $i<$numcells; $i++)
{
$madata[$l][$i] = $data[$i];
echo $madata[$l][$i]."<br />";
}
}
//now add the data in $madata into the db
echo "now add the data in $madata into the db<br />";
for($x=0; $x<(count($file)); $x++)
{
for($y=0; $y<count($data); $y++)
{
$query = "INSERT INTO sp_table (name, telephone, birthday) VALUES ('".$madata[$x][$y]."', '".$madata[$x][($y+1)]."', '".$madata[$x][($y+2)]."')";
echo $query."<br />";
if(!mysql_query($query, $scrappad_connection))
{
die('error: '.mysql_error());
}
break;
}
}

?>

this code works perfectly for me, hopefully you can make use of it to