Parse a csv file and then store it in db

Codewalkers Forums Sponsor:

October 13th, 2009, 03:22 PM

Naughty

Contributing User

Join Date: Jul 2009

Posts: 83

Time spent in forums: 13 h 53 m 8 sec
Reputation Power: 1

Parse a csv file and then store it in db

Hey,

I'm uploading a csv file into a page called(naughty.php) where I have a text box with option select the file and browse button and finally the import button.

Now How do I parse the csv file which is loaded in the text box and then store the contents in a DB using php on clicking the submit button ( where value= import )

P.S: I want to parse only the csv file at time when it's uploaded in the text box.

I've already worked with parse of csv file with static location, but this is something I need desperate help!

Help me!

October 13th, 2009, 05:00 PM

MatthewJ

Contributing User

Join Date: May 2007

Location: Davenport, Iowa

Posts: 564

Time spent in forums: 1 Week 21 h 23 m 29 sec
Reputation Power: 3

1. Upload the file to the server
2. Read the file into an array with file()
3. loop over the array and use explode() to get the values within each line
4. insert exploded values to the db.

October 14th, 2009, 10:02 AM

Naughty

Contributing User

Join Date: Jul 2009

Posts: 83

Time spent in forums: 13 h 53 m 8 sec
Reputation Power: 1

Hey Matthew,

Thanks for the reply.
Is the below code right?

PHP Code:


		
			
<html>

<body>

<form method="post" action="msg.html">

<br/>

<br/>

<p>

Select a file:<input type="file" name="dataf" size="40">

<br/></p>

<p>

<input type="submit" value="Import"></p>

</body>

</html>





<?php



$target_path = "uploads/";



/*Add the original filelname to our target path*/

$target_path = $target_path.basename($_FILES[B]['dataf']['name']);[/B]



if(move_uploaded_files($_FILES[B]['dataf']['tmp_name'], $target_path)){[/B]

}



/* read the file contents into the array*/

[B]

$lines = file("");[/B]



foreach($lines as $data){

$num = explode(",", $data);

}



//infodb is dbname and StoreTable is table name.



//to insert the values of php form into database with Unique ID 

$scourse = $_POST['selectcourse'];



mysql_connect("localhost", "infodb", "password") or die("Unable to connect");

mysql_select_db("info");



$query="INSERT INTO StoreTable(ID, selectcourse) VALUES ('NULL','".$scourse."')";



 ?>

The places where I had highlighted as bold text is where I have a doubt. For name and tmp_name what should I give.

Also in the $line var where I need to place my csv file, what should I give there too. Hope the db part is right.

Help me!

October 14th, 2009, 03:02 PM

MatthewJ

Contributing User

Join Date: May 2007

Location: Davenport, Iowa

Posts: 564

Time spent in forums: 1 Week 21 h 23 m 29 sec
Reputation Power: 3

Okay, I changed the fields etc. to one of my test tables so make sure you change them back to the values you need.

I commented the file pretty well, so it should make sense. One thing, if your "ID" field in the database is an autoincrement (which it usually should be) field, you don't have to pass it in the query, you just pass the datafields and mysql knows to increment the ID.

PHP Code:


		
			
<?php



//Create connection to database

$conn = mysql_connect("localhost", "root", "") or die ("Unable to connect");

$db = mysql_select_db("test", $conn);



//Set the target path for the uploaded file

//Make sure this folder exists on the remote server

$target_path = "uploads/";



//Add the original filelname to our target path



$target_path = $target_path.basename($_FILES['datafile']['name']);



//Move the uploaded file from the temp folder to it's destination

if(move_uploaded_file($_FILES['datafile']['tmp_name'], $target_path)) {

    //read the file contents into the array

    $lines = file($target_path);

    //Explode each row into an array called $fields

    $count = 1; // Just used to display a count of lines inserted to database

    foreach($lines as $data) {

        $fields = explode(",", $data);

        $query="INSERT INTO test (field1, field2, field3) VALUES ('".$fields[0]."', '".$fields[1]."', '".$fields[2]."')";

        mysql_query($query, $conn) or die ("Unable to insert data<br /><br />".mysql_error()); //Display error if insert fails

        $count++;

    }

    echo $count." lines inserted to database";

}



?>

<html>

<body>

<form method="post" action="" enctype="multipart/form-data">

<br/>

<br/>

<p>

Select a file:<input type="file" name="datafile" size="40">

<br/></p>

<p>

<input type="submit" value="Import"></p>

</form>

</body>

</html>

the sample file I used simply contained three fields

Code:

Data for field 1, some data for field 2, and a bit of data for field 3
Just some test stuff, Mary had a little lamb, roses are red
violets are blue, blah blah blah, and final field

Last edited by MatthewJ : October 14th, 2009 at 03:05 PM.

October 19th, 2009, 02:59 PM

Naughty

Contributing User

Join Date: Jul 2009

Posts: 83

Time spent in forums: 13 h 53 m 8 sec
Reputation Power: 1

Hey Mathew,

I tried the below code, but the output is blank, but when I disable the PHP part, I can see the HTML output.

But with PHP and HTML part together, I'm unable to see the HTML part

I saved the file as sample.php ( C:/Apache2.2/htdocs )

Why is that, how to fix this?

October 19th, 2009, 03:27 PM

MatthewJ

Contributing User

Join Date: May 2007

Location: Davenport, Iowa

Posts: 564

Time spent in forums: 1 Week 21 h 23 m 29 sec
Reputation Power: 3

PHP Code:


		
			
<?php



if(isset($_POST['datafile'])) {

//Create connection to database

$conn = mysql_connect("localhost", "root", "") or die ("Unable to connect");

$db = mysql_select_db("test", $conn);



//Set the target path for the uploaded file

//Make sure this folder exists on the remote server

$target_path = "uploads/";



//Add the original filelname to our target path



$target_path = $target_path.basename($_FILES['datafile']['name']);



//Move the uploaded file from the temp folder to it's destination

if(move_uploaded_file($_FILES['datafile']['tmp_name'], $target_path)) {

    //read the file contents into the array

    $lines = file($target_path);

    //Explode each row into an array called $fields

    $count = 1; // Just used to display a count of lines inserted to database

    foreach($lines as $data) {

        $fields = explode(",", $data);

        $query="INSERT INTO test (field1, field2, field3) VALUES ('".$fields[0]."', '".$fields[1]."', '".$fields[2]."')";

        mysql_query($query, $conn) or die ("Unable to insert data<br /><br />".mysql_error()); //Display error if insert fails

        $count++;

    }

    echo $count." lines inserted to database";

}



}



?>

<html>

<body>

<form method="post" action="" enctype="multipart/form-data">

<br/>

<br/>

<p>

Select a file:<input type="file" name="datafile" size="40">

<br/></p>

<p>

<input type="submit" value="Import"></p>

</form>

</body>

</html>

I forgot to put a check in to see if the form had been submitted yet or not... try that.

October 19th, 2009, 04:11 PM

Naughty

Contributing User

Join Date: Jul 2009

Posts: 83

Time spent in forums: 13 h 53 m 8 sec
Reputation Power: 1

This is my entire code, just to check to whether html and php work together

PHP Code:


		
			
<?php



if(isset($_POST['datafile'])){



//Create Database connection



$connect = mysql_connect("localhost", "root", ""); or die("Unable to connect");

$db = mysql_select_db("test", $connect);





//Target path of remote server

$target_path = "uploads/";



//Original filename to our target path



$target_path = $target_path.basename($_FILES['datafile']['filename']);



//Move the uploaded file from the temp folder to it's destination

if(move_uploaded_file($_FILES['datafile']['tmp_name'], $target_path)) {



//read the contents of the file

$lines = file($target_path);



//Explode each row into an array called $fields

$count = 1;

foreach($lines as $data){

$fields = explode(",", $data);

$query = "INSERT INTO test(field1, field2, field3) VALUES ('".$fields[0]."'.'".$fields[1]."','".fields[2].",)";

mysql_query($query, $conn) or die ("Unable to insert data<br /><br/>".mysql_error());//Displays error if insert fails 

$count++;

}

echo $count."Lines inserted to DB";

}

}

?>



<html>

<head>

<script type="text/javascript" src="calendar.js"></script>

<link href="calendar.css" rel="stylesheet" type="text/css">

</head>

<body>



<form method="post" action="" enctype="multipart/form-data">

<br/>

<br/>

<p>

Select a file:<input type="file" name="datafile" size="40">

<br/></p>

<p>

<input type="submit" value="Import"></p>



<br/>

<br/>



<p>

Select Course:

<select name="course">

<OPTION VALUE=0><?=$options?>

</select>

</p>



<p>

Date:<input type="text" name="date"><a href="#" onClick="setYears(1947, 2020);

showCalender(this, 'date');">

<img src="calender.png">

</a>

</p>



<p>

<input type="submit" value="Send">

</p>





</form>



</body>

</html>

So I should see the html part on running http://localhost/sample.php.

But all I see is just a blank page.

Why

October 19th, 2009, 04:45 PM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,937

Time spent in forums: 1 Week 5 Days 1 h 54 m 18 sec
Reputation Power: 4

add error_reporting(E_ALL); as the first line under the first <?php tag. looks like there is a parse/fatal error that is not allowing the script to run and your server is set to not show errors.

October 20th, 2009, 07:01 AM

jamestrowbridge

Contributing User

Join Date: Jul 2008

Location: Cleveland, Ohio, USA

Posts: 411

Time spent in forums: 4 Days 18 h 54 m 24 sec
Reputation Power: 2

Change:
$connect = mysql_connect("localhost", "root", ""); or die("Unable to connect");
to:
$connect = mysql_connect("localhost", "root", "") or die("Unable to connect");

__________________
Sir, a desire of knowledge is the natural feeling of mankind; and every human being, whose mind is not debauched, will be willing to give all that he has to get knowledge.

#10

October 20th, 2009, 01:07 PM

Naughty

Contributing User

Join Date: Jul 2009

Posts: 83

Time spent in forums: 13 h 53 m 8 sec
Reputation Power: 1

I tried changing mysql_connect and also added
error_reporting(E_ALL);

Still no luck.

FYI, if I try to run any php page my browser is blank, not sure why. Maybe I didn't configure php in right way to make it work with Apache web server.

#11

October 20th, 2009, 02:30 PM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,937

Time spent in forums: 1 Week 5 Days 1 h 54 m 18 sec
Reputation Power: 4

try making a php file named phpinfo.php and putting nothing but:

PHP Code:


		
			
<?php

phpinfo();

?>

in the file. if you don't have php installed/configured correctly, you will see nothing. if you do have php installed, you will get a bunch of details about your php setup. And when installing php, you should just get the installer and let it auto-configure the server for you. You shouldn't have to do pretty much any configuration to get it to work at least a little.

#12

October 20th, 2009, 02:58 PM

Naughty

Contributing User

Join Date: Jul 2009

Posts: 83

Time spent in forums: 13 h 53 m 8 sec
Reputation Power: 1

I did see the php info page.

But all the files with php extension remains blank on the web browser.

Not sure WHY

#13

October 22nd, 2009, 03:46 PM

Naughty

Contributing User

Join Date: Jul 2009

Posts: 83

Time spent in forums: 13 h 53 m 8 sec
Reputation Power: 1

For now I'm not worried about DB part, coz I'm planning to store as whole csv file rather than parsing and then storing.

But I want to explode or parse the csv which is uploaded on my page1.php to display on page2.php.

I tried below code but still no luck:

PHP Code:


		
			
<?php

//If csv file it explodes and displays the contents



if(isset($_POST['submit'])){



$lines = file($_POST['datafile']);

echo "<pre>";

foreach($lines as $data){

$field = explode(",", $data);

print_r($data);

}

echo "</pre>";

?>

<html>

<body>

Upload a file:<input type="file" name="datafile">

</body>

</html>

I want to parse csv the file which is been uploaded in page1.php( Upload a file ) and display the content of csv file without comma or table format format on page2.php ( the php part above )

#14

October 23rd, 2009, 07:30 AM

jamestrowbridge

Contributing User

Join Date: Jul 2008

Location: Cleveland, Ohio, USA

Posts: 411

Time spent in forums: 4 Days 18 h 54 m 24 sec
Reputation Power: 2

How to upload files:
http://www.w3schools.com/PHP/php_file_upload.asp

How to use fopen (while file is on the server):
http://www.php.net/manual/en/function.fopen.php

Use fgetcsv, after using fopen:
http://php.net/manual/en/function.fgetcsv.php

#15

October 23rd, 2009, 09:39 AM

Naughty

Contributing User

Join Date: Jul 2009

Posts: 83

Time spent in forums: 13 h 53 m 8 sec
Reputation Power: 1

Hey James,

PHP Code:


		
			
(page2.php)

<?php

//If csv file it explodes and displays the contents



/*if(isset($_POST['submit'])){*/

echo "<table>\n";

$row = 0;

//Opens the csv file in read mode

$handle = fopen("sample.csv" ,"r");

//Retrives the contents of csv file which is separated by comma

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

if($row == 0) {

//reads the first row of csv file



$num = count($data);

echo "<thead>\n<tr>";

//Loops through the row

$row++;

for($c=0; $c< $num; $c++) {

//echo's out the title of the csv file

echo "<th>".$data[$c] ."</th>";

}

echo "</tr>\n</thead>\n\n<tbody>";

}

else{

$num = count($data);

echo "<tr>";

$row++;

//echo's out the remaining contents of the csv file 

for($c=0; $c < $num; $c++){

echo "<td>" .$data[$c]."</td>";

}

echo "</tr>\n";

}

}

fclose($handle);

echo "</tbody>\n</table>";



?>

The output:

Code:

Data for field 1	some data for field 2	and a bit of data for field 3
Just some test stuff	Mary had a little lamb	roses are red
violets are blue	blah blah blah	and final field

This works fine if I run http://localhost/page2.php.

But if I edit as below, I don't get the above output.

PHP Code:


		
			
if(isset($_POST['submit'])){

echo "<table>\n";

$row = 0;

$handle = fopen($_POST['datafile'] ,"r");

...

...

Which I presume on submit, read the file uploaded in field datafile( name parameter for upload the file on my HTML form )

Any help!

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: