Hi all,

Ok, I am wanting to insert into a table multiple rows automatically. I relaise that you can use "VALUES ("string1", "string2"), ("string3", "string4")" to insert multiple rows, but I cannot specify the values I want to insert, and similarly the number of records to insert.

I am getting the records from a text file which I have uploaded to the server. Where each value is seperated by a ",". This is a small selection of the data stored in the text file uploaded:

"000000000000952019","000000952019","DESC",1.00,1,0
"000000000001542141","000001542141","DESC2",1.00,1,0

As you can see there are 2 records shown here. So I need a way of inserting these records into the table, along with a possible 20,000 other records. (serious!) - (i.e. I maybe a loop of some sort?)

If I need a loop, a foreach sounds good but I am unaware as to how to use it here and create appropriate arrays using the data.

Any help would be appreciated.

Cheers guys.

    My preferred method is to do it the old fashioned way, one line at a time, so that you can process arbitrarily large files:

    <?php
$filename="myfile.csv";
$fp = fopen($filename,"r");
while (!feof($fp)){
    $row = fgetcsv($fp,10000);
    // build an insert query here...  $query="insert ...
    // then execute it
}
?>

      Simplest and quickest way to do this is with Load Data Infile which will process the whole file for you in 1 query. (not a facility in postgres I believe sxooter 🆒 )

        In postgres you have the copy command. You can copy from a file (super users only) or from stdin. Reading from STDIN Works much the same as reading a file, but avoids the security concerns of letting joe user import a file since the postgres account is the one readng the file if you use it with a file.

        Trust me, other than enum, there's not much MySQL can do that PostgreSQL can't do. At least not a lot of good things. OK, I'll stop before I get snarky. 🙂

        Just edited to add that I prefer to use plain sql methods for stuff unless it's absolutely necessary for performance reasons not to. Makes for more portable code.

        Oh, edited again to add a link to the copy command:

        copy

          Write a Reply...