get update from excel sheet to database

Question

Post reply

get update from excel sheet to database

tayyeb.us

SSChasing Mays

Points: 609
More actions
March 2, 2010 at 10:34 pm

#386420

Hi All,

I have small query, I have excel sheet, where user put details in it once in a week, now i have imported that excel sheet to the database which is looking great, my query is how can I write a procedure whenever user update that excel sheet it should get updated in database too can I do that ? if yes how ?

thanks in advance

Regards,

Tayyeb

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

mike mcneer Ten Centuries Points: 1129 More actions · Answer 1

mike mcneer

Ten Centuries

Points: 1129

March 3, 2010 at 10:06 am

#1127914

Write a SSIS package that imports the excel file in daily or hourly. Hopefully the excel file is on a reachable file share.

Fidel Rodriguez SSC Enthusiast Points: 145 More actions · Answer 2

Hi:

I'm not an expert with this, but I think the first step here would be to identify what is the user doing to update the spreadsheet. Is it being deleted from, added to, combinations? Perhaps regardless of the way, you might want a way to version it---perhaps with a hash mark?

Then maybe you can build a version tracking table that checks for the hash of the spreadsheet against what's in the table and if it doesn't exist, add it and consume the file? If it does exist, bypass the consumption as the data is already in the table.

If the user is only adding records and not updating / deleting, then you can opt to always consume the file. This approach could be a time bomb though. If, say the user were to update an old record accidentally or wipe it out.

Curious: Is the file the authoritative source of the data or just data that you want to append to the table? I'm not really sure which way you're going with this. From what it sounds like a simple DTS or SSIS package combined with scheduling should do the trick. In fact, one could probably use the import / export wizard, save the package, schedule it and be done. I usually run with xp cmdshell for file checks and command line stuff.

Assuming a simple approach of just truncating the destination table and replacing it with the contents of the spreadsheet. It really depends on what one wants to do though.

I hope that helps a little!

---FR