Import Data from Spreadsheet to Database

[kipper]

or at least just echo out the query ( insert statement). I wrote some code to take a very simple spreadsheet with name and address and import into a database table. Simple I thought, right? Well does anyone have some snippet of code that addresses apostrophes in names, extra commas and so on in the data?

 

To keep it simple, I will just use first and last name. tab delimited. Does anyone have any code that is more sophsticated? Thanks Kip

 

<code>

$file = "philly.csv";

 

if($handle = fopen($file, 'r')) {

 

while(!feof($handle)) {

 

  $line=fgets($handle);

 

  $cells = explode("\t", $line);

 

echo " insert into wp_joinus (fname, lname) values ('{$cells[1]}' , '{$cells[2]}') ";

 

  }

fclose($handle);

}

 

</code>

[dabaR]

I think you will be happy to hear about http://ca3.php.net/manual/en/function.fgetcsv.php

[clay1]

I've found if using CSV you need to change your delimiter to something that will not appear in the file. ^ for example

 

 

[Mchl]

I've found if using CSV you need to change your delimiter to something that will not appear in the file. ^ for example

 

If the delimiters in your csv are not escaped properly, which makes it invalid csv anyway.

[kipper]

Thank you guys.  But, I my problem is not the type of csv. Tab delimited is always preferred and the outcome is to generate the insert statements. As in my near last line of the code is:

 

<code>

echo " insert into wp_joinus (fname, lname) values ('{$cells[1]}' , '{$cells[2]}') ";

</code>

 

Since this has got to be a very common procedure, I would think there is some snippet to handle this. I use trim and a snippet I call msqlprep which takes care of magic quotes on or off depending on the version of php and so on. So, I was looking for some snippet that generates insert sql statements and does a really good job of cleaning or validating the values. (don't have to worry about the mysql connection and like that, just echo out the inserts.

 

In response to dabaR, I always go php.net first. Which section do you mean? Your statement: "

 

I think you will be happy to hear about http://ca3.php.net/manual/en/function.fgetcsv.php did you mean?

 

I don't see which snippet that generates the insert statements. Please define.

 

Thanks for all your help guys. But, if you don't know, then it looks I have the really good snippet. I would be happy to share.

 

Kip