another csv textfile to mysql table question

Lcousins

hi all, i've ben trying to figure out hopw to make a script to upload a csv text file containing release dates for dvds. Ive done a search in the forums (eg this thread ) plus googling it and even reading the php manual thingy :eek:

to be perfectly honest im more confused than I was when I started :p

anyway, I have a csv file taken from excel containing the fields (with no column headers) 'catcode', 'title', 'distributor', 'rating', 'catagory', 'release date' (dd/mm/yyyy). I have a tableset up waiting for the data, I just can't work out (using the milllion and one examples ive found searching) how to get this to work. Oh and I need to be able add additional titles to the table using the same method at a future date.

anyone kind enough to help a poor fool thats seeing his weekend rapidly disappear without having achieved anything??

regards

lcousins

Sxooter

what you wanna do is use fgetcsv() and a while loop to read the file and insert it into the database.

Here's a super simple script written for pgsql, should be easy to convert to whatever you're running:

$filename = "test.csv";
$fp = fopen($filename,"r");
$conn = pg_connect("dbname=dbname user=username");
pg_query("begin");
while (!feof($fp)){
        $row = fgetcsv($fp,100000);
        $query = "insert into test (d, f1, f2, f3) values ('";
        $query.= implode("','",$row);
        $query.= "')";
        pg_query($query);
}
pg_query("commit");

Lcousins

hi there,

this looks good!! but erm, could you kindly exlain what is happening in the while loop line by line? Im using mysql and I dont recognise what exactly that bit does :p

thanks

Luke

Sxooter

Now, with comments! 😉

$filename = "test.csv";
$fp = fopen($filename,"r");
$conn = pg_connect("dbname=dbname user=username");

# Note that by using a transaction, either all the rows get imported, or none of them.  
# Should the import fail partway through, no data in the table will be changed, since we 
# are doing simple all or nothing transactions.  Note that while some databases like
# Oracle have limits to transaction size, postgresql itself has no such limit, but 
# there is a price to be paid in dead tuples that need to be harvested.  See the 
# vacuum at the bottom of the script

pg_query("begin");

while (!feof($fp)){
        # here we use the built in fgetcsv function
        # which grabs a line from a csv file, parses
        # it, and returns an array
        $row = fgetcsv($fp,100000);
        # Now we create an insert query to put the data
        # we just got into the database.  Our query should
        # be of the form:
        # insert into tablename (field1, field2) values ('value1','value2');
        $query = "insert into test (d, f1, f2, f3) values ('";
        # Here we use implode to put a ',' between each field.
        $query.= implode("','",$row);
        # right now, our query looks kinda like this:
        # insert into tablename (field1, field2) values ('value1','value2
        # So we just close it here to add the missing ') on the end.
        $query.= "')";
        pg_query($query);
}
pg_query("commit");
# clean up the database after ourselves.
pg_query("vacuum full");