CSV into mySQL

[groberts23]

Hi guys-

I have a question. Actually a problem. I have a text file, comma delimited, which needs to be automatically uploaded to a database. My problem is:

1. The last record on the line doesn't have a comma, so I'm not getting to the next record properly. And I'm not getting all records uploaded

2. The records are also surrounded by "", which I'd like to strip off.

Here's some example data:

"1","5K RUNNER","DAVIS","PRISCILLA","3637 40TH ST SW","GRANDVILLE","MI","49418","5/20/1962","38","F","XL"

"2","5K RUNNER","SHOEMAKER","SCOTT","3410 NAVAHO SW","GRANDVILLE","MI","49418","5/31/1957","43","M","L"

"3","5K RUNNER","RUNNER","","","GRAND RAPIDS","MI","49501","1/1/1901","100","M","U"

Here's my current upload code:

<?

$file = fopen('MY FILE', 'r');

while(!feof($file)) {

$line = fgets($file, 8092);
//$line = fgetcsv($file, 4096);

if ($line) {

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

//each field in a text file will become a field in MySQL table

$ID =$fields['0'];
$event_desc= $fields['1'];
$last_name= $fields['2'];
$first_name= $fields['3'];
$address= $fields['4'];
$city= $fields['5'];
$state= $fields['6'];
$zip= $fields['7'];
$birth_date= $fields['8'];
$age= $fields['9'];
$gender= $fields['10'];
$shirt_size= $fields['11'];

}
// create connection

$db_name="MY DB";
$table_name="MY TABLE";

$connection=@mysql_connect('localhost', 'USERNAME', 'PASSWORD') or die ("Couldn't connect");

$db=@mysql_select_db($db_name, $connection) or die ("Couldn't connect to the database");

// insert into YOUR table

$sql = "INSERT INTO MY TABLE(ID, event_desc, last_name, first_name, address, city, state, zip, birth_date, age, gender, shirt_size) VALUES ('$ID','$event_desc','$last_name','$first_name','$address','$city','$state','$zip','$birth_date','$age','$gender','$shirt_size')";

$result=@mysql_query($sql, $connection) or die ("Couldn't execute query");

}

?>

Any and all help would be appreciated!
Thanks...

[jlhaase]

Please note, I haven't actually tried the following code, but it should work.

First of all instead of reading in the file one line at a time I personally prefer the file() function, where you read the entire file into an ARRAY. Note however that the lack of a comma after the last record on a line shouldn't affect the explode function, provided that each line actually ends with a newline character. Then for the second part of your problem use the str_replace() function to strip out the quotes.... Also create the connection to the database outside your loop, as you don't need to reconnnect for each query.

So for example....
<?php

// create connection

$db_name="MY DB";
$table_name="MY TABLE";

$connection=@mysql_connect('localhost', 'USERNAME', 'PASSWORD') or die ("Couldn't connect");

$db=@mysql_select_db($db_name, $connection) or die ("Couldn't connect to the database");

// insert into YOUR table
$contents = file("MY FILE");

foreach($contents as $line) {
$line = str_replace('"', '', $line);
$fields = explode(',', $line);
$ID =$fields[0];
$event_desc= $fields[1];
$last_name= $fields[2];
$first_name= $fields[3];
$address= $fields[4];
$city= $fields[5];
$state= $fields[6];
$zip= $fields[7];
$birth_date= $fields[8];
$age= $fields[9];
$gender= $fields[10];
$shirt_size= $fields[11];
$sql = "INSERT INTO MY TABLE(ID, event_desc, last_name, first_name, address,";
$sql .= "city, state, zip, birth_date, age, gender, shirt_size) ";
$sql .= "VALUES ('$ID','$event_desc','$last_name','$first_name','$address',";
$sql .= "'$city','$state','$zip','$birth_date','$age','$gender','$shirt_size')";
$result=@($sql, $connection) or die ("Couldn't execute query");
}

[Anon]

Thanks for the tip. This may sound stupid, but I cannot make it go away. When I run this script, I get the error:

Parse error: parse error, expecting `'('' in MY FILE LOCATION on line 16

Do you know how I can get rid of this?

Here's the syntax from that line:

for each ($contents as $line){

Thanks again...