Welcome Guest, Not a member yet? Register   Sign In
import excel to mysql
#1

[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.
#2

[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.
#3

[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)
#4

[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.
#5

[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..
#6

[eluser]tomcode[/eluser]
There is a CSV reader library on the wiki which I used once.
#7

[eluser]umefarooq[/eluser]
hmmm that cool can you give me link or name of that library and ill try to find out myself also
#8

[eluser]Dam1an[/eluser]
I guess he's refering to this one
#9

[eluser]tomcode[/eluser]
here You go : CSVReader
#10

[eluser]umefarooq[/eluser]
wowo man really great that's why i really like CI forum you will get quick reply, really fast




Theme © iAndrew 2016 - Forum software by © MyBB