Post reply

get update from excel sheet to database

  • 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

  • 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.

  • March 3, 2010 at 7:23 pm

    #1128202

    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

  • March 3, 2010 at 8:23 pm

    #1128214

    thanks a lot mike i have written the SSIS package yes excel is reachable

    thanks a lot fidel, and user onle update that excel.

  • March 4, 2010 at 12:38 pm

    #1128714

    Cool - best of luck to ya'!

    ---FR

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

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