StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>See <a href="http://devzone.zend.com/27/reading-and-writing-spreadsheets-with-php/" rel="nofollow">this article on using PHP-ExcelReader</a>, in particular the short section titled "Turning the Tables".</p> <p>Any solution you have will end up looking like this:</p> <ol> <li>Read a row from the XLS (requires an XLS reader)</li> <li>Modify the data from the row as needed for your database.</li> <li>Insert modified data into the database.</li> </ol> <p>You seem to have this fixation on "Editing the data". This is just PHP--you get a value from the XLS reader, modify it with PHP code, then insert into the database. There's no intermediate file, you don't modify the XLS--it's just PHP.</p> <p>This is a super-simple, untested example of the inner loop of the program you need to write. This is just to illustrate the general pattern.</p> <pre><code>$colsYouWant = array(1,2,3,4,8); $sql = 'INSERT INTO data (id, email, fname, lname, country) VALUES (?,?,?,?,?)'; $stmt = $pdo->prepare($sql); $sheet = $excel->sheets[0]; // the excel reader seems to index by 1 instead of 0: be careful! for ($rowindex=2; $rowindex <= $sheet['numRows']; $rowindex++) { $xlsRow = $sheet['cells'][$rowindex]; $row = array(); foreach ($colsYouWant as $colindex) { $row[] = $xlsRow[$colindex]; } // now let's "edit the row" // trim all strings $row = array_map('trim', $row); // convert id to an integer $row[0] = (int) $row[0]; // capitalize first and last name // (use mb_* functions if non-ascii--I don't know spreadsheet's charset) $row[2] = ucfirst(strtolower($row[2])); $row[3] = ucfirst(strtolower($row[3])); // do whatever other normalization you want to $row // Insert into db: $stmt->execute($row); } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload