Bazzaah Posted November 10, 2011 Share Posted November 10, 2011 Not sure if this is the right forum, but I have a database that I will need to populate with a large number of rows (2000+). I have written a PHP script that uploads individual entries. Is it possible to use something like a spreadsheet where I can set out the rows/columns as they will appear in the database, and then upload in one go rather than uploading each row individually? Thanks for any observations and/or help. Link to comment https://forums.phpfreaks.com/topic/250868-upload-script-for-multiple-rowscolumns/ Share on other sites More sharing options...
tomfmason Posted November 10, 2011 Share Posted November 10, 2011 Yes, this can be done easily by exporting your excel file to a csv and then importing that directly using mysql. Here is an example of what the query would look like: LOAD DATA LOCAL INFILE '/path/to/your.csv' INTO TABLE `table` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (`field1`,`field2`,`field3`); If I am not mistaken(it has been a while since i have done this) you will need to remove the top line from the cvs otherwise the column list at the top will also get inserted as a new row Link to comment https://forums.phpfreaks.com/topic/250868-upload-script-for-multiple-rowscolumns/#findComment-1287044 Share on other sites More sharing options...
cypher86 Posted November 10, 2011 Share Posted November 10, 2011 sure you can.. you just need to build a parser for that, then take a line at a time, explode it by special characters which separte every column and then run the query. all of this untill the end of the file. Link to comment https://forums.phpfreaks.com/topic/250868-upload-script-for-multiple-rowscolumns/#findComment-1287045 Share on other sites More sharing options...
Bazzaah Posted November 10, 2011 Author Share Posted November 10, 2011 Ok thanks for the ideas there. I'll give the .csv a go. The db has an auto_increment to number each row - would that be covered or would I need to number each entry within the spread? Link to comment https://forums.phpfreaks.com/topic/250868-upload-script-for-multiple-rowscolumns/#findComment-1287056 Share on other sites More sharing options...
tomfmason Posted November 10, 2011 Share Posted November 10, 2011 as long as you don't have an id column in the csv it will automagically insert the id for you like normal. Link to comment https://forums.phpfreaks.com/topic/250868-upload-script-for-multiple-rowscolumns/#findComment-1287057 Share on other sites More sharing options...
Bazzaah Posted November 10, 2011 Author Share Posted November 10, 2011 nice one, thanks. Link to comment https://forums.phpfreaks.com/topic/250868-upload-script-for-multiple-rowscolumns/#findComment-1287099 Share on other sites More sharing options...
Bazzaah Posted November 17, 2011 Author Share Posted November 17, 2011 The script worked just fine thanks - the entries were all surrounded by quotes though. Any ideas how to avoid this? Link to comment https://forums.phpfreaks.com/topic/250868-upload-script-for-multiple-rowscolumns/#findComment-1288898 Share on other sites More sharing options...
tomfmason Posted November 17, 2011 Share Posted November 17, 2011 After reading the docs I see that I forgot to add "ENCLOSED BY". This should solve the quote problem LOAD DATA LOCAL INFILE '/path/to/your.csv' INTO TABLE `table` OPTIONALLY ENCLOSED BY '"' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (`field1`,`field2`,`field3`); Link to comment https://forums.phpfreaks.com/topic/250868-upload-script-for-multiple-rowscolumns/#findComment-1288974 Share on other sites More sharing options...
Bazzaah Posted November 20, 2011 Author Share Posted November 20, 2011 Thanks - I really appreciate your help. I'll read the manual. Link to comment https://forums.phpfreaks.com/topic/250868-upload-script-for-multiple-rowscolumns/#findComment-1289713 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.