+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: import excel to mysql (/showthread.php?tid=19954)
import excel to mysql - El Forum - 06-23-2009
[eluser]umefarooq[/eluser]
im creating a directory site, but my problem is my client don't want to add data one by one from form, he/she has an excel sheet they want to import data in one shoot to the database table every year they are updating excel sheet, want to update same data in table, how can i solve this problem any solution, code sample or suggestions.
import excel to mysql - El Forum - 06-23-2009
[eluser]TheFuzzy0ne[/eluser]
I'd suggest dumping the table data, and parsing it, and comparing it to the new data after parsing that. An insert statement can be built on the fly, and cached to a file, then you can iterate through the items to be added, and add them into the database in batches.
import excel to mysql - El Forum - 06-23-2009
[eluser]Dam1an[/eluser]
Here's a high level overview of a possible solution
1) Export from Excel to CSV
2) Read file into array of lines using the file() function
3) For each line, explode on comma and build the insert query as needed
4) Execute query, inserting that row
5) Rinse and repeat
I can see one instant flaw with that, being if the speadhseet keeps the old data after an import, it will try to import it again, so you'll need to check on PK (maybe get the IDs of all the PKs into an array at the start, and check if the ID exists in that array before inserting, of course this won;t work for auto incrementing IDs, so you would need to check the whole row)
import excel to mysql - El Forum - 06-23-2009
[eluser]TheFuzzy0ne[/eluser]
If the update will also contain all of the old stuff, you can simply insert it into a table, and then delete the current table, and rename the new table.
import excel to mysql - El Forum - 06-23-2009
[eluser]umefarooq[/eluser]
my client is happy to delete all existing records and add new records from the excel file. well thanks for the options ill check it all if have in any problem ill share here..
import excel to mysql - El Forum - 06-23-2009
[eluser]tomcode[/eluser]
There is a CSV reader library on the wiki which I used once.
import excel to mysql - El Forum - 06-23-2009
[eluser]umefarooq[/eluser]
hmmm that cool can you give me link or name of that library and ill try to find out myself also
import excel to mysql - El Forum - 06-23-2009
[eluser]Dam1an[/eluser]
I guess he's refering to this one
import excel to mysql - El Forum - 06-23-2009
[eluser]tomcode[/eluser]
here You go : CSVReader
import excel to mysql - El Forum - 06-23-2009
[eluser]umefarooq[/eluser]
wowo man really great that's why i really like CI forum you will get quick reply, really fast