current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.6 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

I have an application where I would like a user to be able to upload an spreadsheet which will end up being inserted into one of our PostgreSQL database tables. The guys that will be doing the uploading will not want to / not know how to save as CSV or other delimited files. so opting for the phpExcel class which I can use to generate:

  1. an array of each row in the sheet
  2. a multi dimensional array of the entire sheet

I could iterate each row and perform an insert for each one, but this could be a little heavy on the database so I was trying to find out if anyone knows of a way to populate a table quickly and efficiently from a multidimensional array:

e.g.

<?php
$data['row1'] = array('column1','column2','column3','column4');
$data['row2'] = array('column1','column2','column3','column4');
$data['row3'] = array('column1','column2','column3','column4');
// Some PSQL Insert.
?>

Thanks in advance.

share|improve this question
1  
You can specify multiple rows in a single VALUES: insert into t (...) values (...), (...), (...). So chunk it into pieces of a size that works and wrap the whole thing in a transaction (or several transactions depending on what happens in real life). – mu is too short Nov 16 '13 at 23:55
    
Thanks mu is too short! Some of the sheets could easily have 15,000 rows or more (will probably end up restricting it to 10,000 or 1MB file) so certainly would still require a few transactions but could push maybe 100 rows into each insert which would reduce some overhead/time costs. still very interested to know if there is a way more like the "copy tname from 'PathToCSV' delimiter ',' csv;" to achieve this. – Bunny Huggan Nov 17 '13 at 0:17
up vote 0 down vote

If this is a CSV I would take a different approach (please familiarize yourself with the documentation first!)

COPY mytable FROM stdin WITH CSV DELIMITER ',';

And then you feed it in over the appropriate interface. If you can't do it clearly in PHP, there is an example of wrapping it in a function at php postgresql pdo copy from stdin

If that doesn't work, you could put the file somewhere accessible by the server and create a function that could wrap

COPY mytable FROM filename....
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.