[SOLVED] Trouble with .CSV Import Script

sstoveld · March 24, 2009

hey guys, im having some difficulty getting my .CSV import script to work. i have a .CSV file that has info about the amount of millionaire in each of the 50 states. its 3 columns:

State

Amount of Millionaires in State

Total Wealth Of Millionaires

xxx

i want it so when the user first comes to the page, there is a submit button that he presses to import the csv data into 2 different tables in the database

here's my database:

here's my code:

http://pastebin.com/m678b065d

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Insert Data From Text File</title>
<link href="styles/style.css" rel="stylesheet" type="text/css" media="screen" />
</head>

<body>

<div id="wrapper">
<div id="banner">
    	<h1><center><a href="../php6/index.php">Inserting Data From A Text File</a></center></h1>
    </div><!-- banner -->
    
  <div id="mainContent">
  	<h2>Hit the Submit button below to import the data from the Excel CSV file.</h2>
    
    
<?php

// This PHP script will import data from an Excel .CSV (Comma Delimited File) into a MySQL database //
// Coded by: Steve Stoveld//
// INF 216: Programming for the Web II //
// Due: 3/29/09 //
// PHP Assignment 5 //

// Connect to the server //
$dbcnx = @mysql_connect('localhost', 'dbase18', '*******') or die ('I cannot connect to the database because: ' .mysql_error());

// Connect to the database //
mysql_select_db('dbase18', $dbcnx);
if (!@mysql_select_db('dbase18'))
{
	exit ('<p>Error in query.'. mysql_error(). '<p>');
}

// Load the wealth .CSV file //
if (isset($_GET['loadwealth']))
{
	$text = file("wealth.csv"); // This is the .CSV file //
	$headers=array_shift($text); // This will get rid of the column headers //
	foreach ($text as $line)
		{
			$line=trim($line); // This gets rid of the line endings //
			$pieces = explode(",", $line); // Breaks into sub-arrays along the commas //
			$pieces = str_replace('"', '', $pieces); // Gets rid of double quotes, if there are any //
			$pieces = str_replace("'", "\'", $pieces); // This will manage the single quotes, if there are any //
			$states=$pieces[0];
			$millionaires=$pieces[1];
			$dollars=$pieces[2];
			echo $states;

			// Query the states_table table to get the states id //
			$get_states=@mysql_query("SELECT id FROM states_table WHERE states='$states'");
			if (!$get_states)
				{
					exit ('<p>There was an error in the query. '. mysql_error().'</p>');
				}
				$get_states_row = mysql_fetch_array($get_states);
				$states_id = $get_states_row['id'];

			// Insert the state into the table //
			$sql="INSERT INTO states_table SET states='$states'";
			if (!@mysql_query($sql))
				{
					echo "<p>There was an error adding the state: ".mysql_error()."</p>";
				}
			$states_id = mysql_insert_id();

			// Query the wealth_table table to get the wealth id //
			$get_wealth=@mysql_query("SELECT wealth_id FROM wealth_table WHERE millionaires='$millionaires' && dollars='$dollars'");
			if (!$get_wealth)
				{
					exit ('<p>There was an error in the query. '. mysql_error().'</p>');
				}
				$get_wealth_row = mysql_fetch_array($get_wealth);
				$wealth_id = $get_wealth_row['wealth_id'];

			// Insert the wealth info into the table //
			$sql2="INSERT INTO wealth_table SET millionaires='$millionaires', dollars='$dollars'";
			if (!@mysql_query($sql2))
				{
					echo "<p>There was an error adding the wealth info: ".mysql_error()."</p>";
				}

		}
}

		// Select the data from the MySQL database //
		$queryresult = @mysql_query('SELECT id, states, wealth_id, states_id, millionaires, dollars FROM wealth_table LEFT JOIN states_table ON wealth_table.states_id = states_table.id');

		if (!$queryresult)
			{
				exit ('<p>There was an error in the MySQL Query:'. mysql_error().'</p>');
			}	

// Make an HTML table to hold the data from the .CSV //

?>

<p>
   <form name="loadwealth" action="<?php echo($_SERVER["PHP_SELF"]); ?>" method="post">
   <table>
   <tr>
   <td><strong>State</strong></td>
   <td><strong>Millionaires</strong></td>
   <td><strong>Dollars</strong></td>
   </tr>
   <?php
   while ($row = mysql_fetch_array($queryresult))
   		{
		echo ('<tr><td>' . $row['states'] . '</td><td>' . $row['millionaires'] . '</td><td>' . $row['dollars'] . '</td></tr>');
	}
   ?>
   <input type="submit" value="Load" name="loadwealth" />
   </form>
   </table>
</p>

    
  </div><!-- mainContent -->
    
    <div id="footer">
    	<p>Coded and designed by: <a href="mailto:sstoveld@gmail.com">Steve Stoveld</a></p>
    </div><!-- footer -->
</div><!-- wrapper -->

</body>
</html>

currently, when the button is pressed, it doesnt do anything

any help is appreciated. thanks

p.s. i actually tried to keep the code nice and neat this time

edit: its getting late here, so i wont be able to check back until tomorrow morning. thanks

Yesideez · March 24, 2009

Why are you writing your own CSV parsing routine when PHP has some handy functions built-in or is it part of your assignment?

http://uk2.php.net/fgetcsv

sstoveld · March 24, 2009

Why are you writing your own CSV parsing routine when PHP has some handy functions built-in or is it part of your assignment?

http://uk2.php.net/fgetcsv

its part of my assignment

Yesideez · March 24, 2009

If I had to write my own parse routine this is what I'd do...

1. The first line of a CSV is sometimes the table headers - have the option to ignore/use this line

2. CSV stands for Comma Separated List but because some data can contain a comma you can't just use explode() on it and expect everything to be fine

3. Because of (2) data is often surrounded with "limiters" and these are mostly either the single quote ' or double quote "

So this means a sample file can look like this:

ID,NAME,WEIGHT,AGE,ABOUT
"1", "Fred", "1.5", "21", "Some sample text"
"2", "Pete", "2.2", "35", "Some text, with a comma"

There's an easy way we can apply explode() on this and that's to first use str_replace to replace ", " to "," (no space)

Then we can use trim() to remove the start and end quotes following it by explode to split at ","

foreach ($csvfile as $line) {
  $line=-str_replace('", "','","',$line); //remove space after comma
  $line=trim($line,' "'); //remove spaces and quotes
  $arrCSV=explode('","',$line); //now $arrCSV[0]=1, $arrCSV[1]=Fred, $arrCSV[2]=1.5, $arrCSV[3]=21 etc.
}

It isn't difficult to allow the user to choose between single/double/quotes/whatever etc.

Hope this is of help.

sstoveld · March 24, 2009

If I had to write my own parse routine this is what I'd do...

1. The first line of a CSV is sometimes the table headers - have the option to ignore/use this line

2. CSV stands for Comma Separated List but because some data can contain a comma you can't just use explode() on it and expect everything to be fine

3. Because of (2) data is often surrounded with "limiters" and these are mostly either the single quote ' or double quote "

So this means a sample file can look like this:
ID,NAME,WEIGHT,AGE,ABOUT
"1", "Fred", "1.5", "21", "Some sample text"
"2", "Pete", "2.2", "35", "Some text, with a comma"
There's an easy way we can apply explode() on this and that's to first use str_replace to replace ", " to "," (no space)

Then we can use trim() to remove the start and end quotes following it by explode to split at ","
foreach ($csvfile as $line) {
  $line=-str_replace('", "','","',$line); //remove space after comma
  $line=trim($line,' "'); //remove spaces and quotes
  $arrCSV=explode('","',$line); //now $arrCSV[0]=1, $arrCSV[1]=Fred, $arrCSV[2]=1.5, $arrCSV[3]=21 etc.
}
It isn't difficult to allow the user to choose between single/double/quotes/whatever etc.

Hope this is of help.

i've already got that in there i think im all set with that. im just having a problem having it load the file and import it into the database when the submit button is hit on the page

Yesideez · March 24, 2009

Sorry, only reason I pointed that method out was because I saw you doing this...

$pieces = explode(",", $line); // Breaks into sub-arrays along the commas //

What if your data contains a comma somewhere you need it?

Yesideez · March 24, 2009

OK, you're using $_GET to check for your submit button where you should be using $_POST instead.

EDIT: I made a typo and included a hyphen where it shouldn't be:

$line=str_replace('", "','","',$line); //remove space after comma

sstoveld · March 24, 2009

OK, you're using $_GET to check for your submit button where you should be using $_POST instead.

ahh i knew it was something stupid

as for the comma's where i need it, i was planning on number_format() later

i still have 2 problems though. well, one problem and one other thing i overlooked.

when the load button is hit, the states appear outside the table, but the other 2 columns are inside the table like they should be.

and the other thing that i overlooked, the database keep getting populated every time the load button is hit. how can i make it so that when the load button is hit, it deletes all of the entries in the tables so the database doesnt get massive?

Yesideez · March 24, 2009

To clear a table you can use:

TRUNCATE TABLE <tablename>

Can you give an example of the states not appearing where they should be? I don't quite follow...

sstoveld · March 24, 2009

To clear a table you can use:
TRUNCATE TABLE <tablename>
Can you give an example of the states not appearing where they should be? I don't quite follow...

sure, here's a screenshot:

how exactly would i use TRUNCATE TABLE?

EDIT: would it be possible to add another button (Clear or Reset) so that when the user hits that button it will clear both tables?

Yesideez · March 24, 2009

mysql_query("TRUNCATE TABLE `states_table`");

That will empty it.

As for the text outside of the tables, take a look where you're sending it to the browser - make sure it's all inbetween a <td> and </td>

Yesideez · March 24, 2009

While looking for the state thing, I noticed you're setting up your form then creating your table but you're closing the form before you close the table - should be the other way round.

As for the state thing...

            echo $states;

That's line 52 (how ironic the error is on the number of states??? lol)

I don't think the states info is either being added to the table or being pulled correctly.

sstoveld · March 24, 2009

While looking for the state thing, I noticed you're setting up your form then creating your table but you're closing the form before you close the table - should be the other way round.

As for the state thing...
            echo $states;
That's line 52 (how ironic the error is on the number of states??? lol)

I don't think the states info is either being added to the table or being pulled correctly.

hmm ok i changed it so the form is closed after tha table is closed.

i added a reset button so that when it is hit, it's supposed to a query to truncate both tables, but i have to hit it twice for it to run both of the queries. here's the code:

if (isset($_POST['clear']))
	{
		mysql_query("TRUNCATE TABLE `states_table`");
		mysql_query("TRUNCATE TABLE `wealth_table`");
	}
   ?>
   <input type="submit" value="Load" name="loadwealth" />
   <input type="submit" value="Reset" name="clear" />
   </table>
   </form>

how can i make it so it whack em both when you hit that button?

also, im still not sure whats wrong with the states. is this section correct?

while ($row = mysql_fetch_array($queryresult))
   		{
		echo ('<tr><td>' . $row['states'] . '</td><td>' . $row['millionaires'] . '</td><td>' . $row['dollars'] . '</td></tr>');
	}

Yesideez · March 24, 2009

You need to check your code that's inserting the states into the table - use echo to show the values so you can see what you're putting in there as I suspect the problem is there.

I have to pop out, will be back in about an hour.

sstoveld · March 24, 2009

You need to check your code that's inserting the states into the table - use echo to show the values so you can see what you're putting in there as I suspect the problem is there.

I have to pop out, will be back in about an hour.

i got it working, the problem was in my query

i had this:

$sql2="INSERT INTO wealth_table SET millionaires='$millionaires', dollars='$dollars'";

it needed to be this:

$sql2="INSERT INTO wealth_table SET states_id='$states_id', millionaires='$millionaires', dollars='$dollars'";

now the only issue i'm having is that when the Reset button is hit, it clears the tables, it still displays the data in the tables on the web page unless he hits the reset button a second time, or revisits the page. how can i make it so it automatically shows the data has been cleared when the button is hit?

here's the code:

<p>
<table>
   <form name="loadwealth" action="<?php echo($_SERVER["PHP_SELF"]); ?>" method="post">
   <tr>
   <td><strong>State</strong></td>
   <td><strong>Millionaires</strong></td>
   <td><strong>Dollars</strong></td>
   </tr>
   <?php
   while ($row = mysql_fetch_array($queryresult))
   		{
		echo ('<tr><td>' . $row['states'] . '</td><td>' . $row['millionaires'] . '</td><td>' . $row['dollars'] . '</td></tr>');
	}

if (isset($_POST['clear']))
	{
		mysql_query("TRUNCATE TABLE `states_table`");
		mysql_query("TRUNCATE TABLE `wealth_table`");
	}
   ?>
   <input type="submit" value="Load" name="loadwealth" />
   <input type="submit" value="Reset" name="clear" />
   </table>
   </form>
</p>

sstoveld · March 24, 2009

ive hosted this on my own web server instead of on my professors private server so you guys can view it.

here's the url: http://stovelddesign.com/INF216/php5/index.php

so you can see what i mean with the reset button

Yesideez · March 24, 2009

The reason you have to click the clear button twice is because you're displaying the table contents BEFORE you check if the button has been clicked - this means when you clear the data it shows you the data then clears the table and waits for input.

Move the if() to check for "clear" BEFORE the while() loop and it should work.

EDIT: logic/me error typo

sstoveld · March 24, 2009

The reason you have to click the clear button twice is because you're displaying the table contents BEFORE you check if the button has been clicked - this means when you clear the data it shows you the data then clears the table and waits for input.

Move the if() to check for "clear" BEFORE the while() loop and it should work.

EDIT: logic/me error typo

thanks man, works great! all good now

Sign In

[SOLVED] Trouble with .CSV Import Script

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information