RE: [PHP] a better way to do a data import?

what about uploading the entire file into a [semi]temp table..then doing cross table comparisons to load your main table with the data? bastien> Date: Mon, 21 Jan 2008 13:08:27 -0500> From: blackwaterdev@gmail.com> To: bastien> php-general@lists.php.net> Subject: Re: [PHP] a better way to do a data import?> > I think bastien> that's possible, so I'll give it a shot.> > For some reason, even with straight bastien> inserts my php script is dying around> 180,000 rows. Basically, I took out all the compare/update bastien> code so now I> grab the row from the db and if there isn't one, do an insert. I've bastien> wiped> my db so should do straight inserts so I'm not sure what's taking up the> bastien> memory.> > > > private function processFile($table, $key){> > bastien> $this->openFileForReading(); //foudn in GLFile class> while (!feof($this->fileHandle)) bastien> {> $file_data = fgets($this->fileHandle);> > $this->insert($table, $key, bastien> $file_data);> > }> $this->closeFile();> > }> > private function bastien> insert($table, $key, $data){> if (strlen($data)<10) return false;> > bastien> $data=$this->db->escape_string($data);> > //this is the data we will use should we need bastien> to do an insert> $insert_data = str_replace("\"", "'", $data);> bastien> > //this is a hack we need to change the separator of the file> //we need this because we need bastien> to put the data into an array and if> //we simply use the comma, then it splits address bastien> fields> $data = str_replace("\",\"", "~", $data);> $data = bastien> str_replace("\"","",$data); //let's remove the double quotes> bastien> $this->setDelimiter("~");> $dataToArray=$this->stringToArray($data);> //set it bastien> back for other functions> $this->setDelimiter(",");> > //get the id, we trust bastien> it is the first column> > > $key_data=$dataToArray[0];> > //does the value exist in bastien> the database already?> $sql="select * from prelim_$table where bastien> $key='$key_data'";> $handle=$this->db->select($sql);> if bastien> ($this->db->row_count($handle)>0){> $textData=array();> bastien> $colsToUpdate="";> $dataRow="";> bastien> $dataRow=$this->db->fetch_row($handle);> //now that we have the data, let's merge the bastien> row from the> //file with the column names> > bastien> $textData=array_combine($this->carrierColumns,> $dataToArray);> //cast some values that are bastien> strings in the text file> $textData['cars1']=(int) $textData['cars1'];> bastien> $textData['car_amount']=(int)> $textData['car_amount'];> > bastien> $textData['total_work']=trim($textData['total_work']);> > bastien> $textData['business_zip']=trim($textData['business_zip']);> //clean up some bastien> old db data> $dataRow['rfc_number']=trim($dataRow['rfc_number']);> > bastien> $dataRow['business_zip']=trim($dataRow['business_zip']);> > > > bastien> $colsToUpdate=array_diff($textData,$dataRow);> > //if we have columns to update, do it> if bastien> (count($colsToUpdate)>0){> > $colset="";> foreach ($colsToUpdate as bastien> $column=>$value){> $colset.="$column='$value',";> }> //strip last bastien> comma> $colset=substr($colset, 0, -1);> $sql="update prelim_$table set $colset where> bastien> $key='$key_data'";> $this->db->write($sql);> > }> > bastien> $dataRow=NULL;> $colsToUpdate=NULL;> $colset=NULL;> $textData=NULL;> }> else{> bastien> > //insert the row> $sql="insert into prelim_$table values (";> bastien> $sql.=trim($insert_data);> $sql.=");";> $this->db->write($sql);> }> > bastien> > > > }> > > On Jan 21, 2008 12:55 PM, Robert Cummings bastien> <robert@interjinn.com> wrote:> > >> > On Mon, 2008-01-21 at 12:35 -0500, bastien> blackwater dev wrote:> > > I have a text file that contains 200k rows. These rows are to bastien> be> > imported> > > into our database. The majority of them will already exists while bastien> a few> > are> > > new. Here are a few options I've tried:> > >> > bastien> > I've had php cycle through the file row by row and if the row is there,> > > bastien> delete it and do a straight insert but that took a while.> > >> > > Now I have php bastien> get the row from the text file and then to array_combine> > with> > > a default array bastien> I have in the class so I can have key value pairs. I> > then> > > take that generated bastien> array and do array_diff to the data array I pulled> > from> > > the db and I then bastien> have the columns that are different so I do an update> > on> > > only those columns bastien> for that specific row. This is slow and after about> > > 180,000 rows, php throws a memory bastien> error. I'm resetting all my vars to> > NULL> > > at each iteration so am not bastien> sure what's up.> > >> > >> > > Anyone have a better way to do this? bastien> In MySQL, I could simply a replace> > on> > > each row...but not in postgres.> bastien> >> > Does Postgres support any method of temporarily disabling keys/indexing?> > bastien> Indexing is what causes the inserts to take a while. MySQL can optimize> > an import by bastien> locking the table and allowing the keys/indexes to be> > temporarily disabled. You'll see bastien> the following lines in recent MySQL> > database dumps surrounding the inserts:> >> bastien> > /*!40000 ALTER TABLE xxx DISABLE KEYS */;> > INSERT bastien> ...> > INSERT ...> > /*!40000 ALTER TABLE xxx ENABLE bastien> KEYS */;> >> > Cheers,> > Rob.> > --> > bastien> ............................................................> > SwarmBuy.com - bastien> http://www.swarmbuy.com> >> > Leveraging the buying bastien> power of the masses!> > ...........................................................> bastien> >> > _________________________________________________________________