Easiest way to fill sql db from spreadsheet?

[Mibble]

Hello All,

Is there an easy way to fill a table from a spreadsheet csv file into a sql table?

i need to add about 4000 items which are tied into numerous tables, making sure the relationship stays the same.

[Sxooter]

Well, if you're running the latest development version of postgresql, its copy command understands CSV and can suck it right in.

If you're running anything else, it's pretty easy, just write a short file like this:

$fp = fopen($filename,"r");
while (!feof($fp)) {
        $line = fgetcsv($fp,4096);
        $query = "insert into $table values ('";
        $query.= implode("','",$line);
        $query.= "')";
        pg_exec($conn,$query);
}

this simple code fragment doesn't escape any characters, which it probably should. But it gets the idea across.

[csn]

With mysql, there's LOAD DATA INFILE... I think you can also disable f/k checks until the import is complete too (can also be done with pgsql).

[Sxooter]

Will that work with fields that have commas embedded in them? like this:

'abc','123','my dog is slow, but smart'

I.e. is it going to see three or four fields?

[Sxooter]

Never mind, I just saw the "enclosed by" clause... carry on. :-)