Best way to import Excel data into SQL Server 2005 (ASP.Net 2.0)



  • Hey,

    I'm currently designing a website which will have an admin console. Other than the usual method of adding data through the relevant entry forms, it will have an option to import data through an excel sheet. 

    Using this blog, I was able to figure out how to do this. My questions:

    1. Is there a better way to do this through some sort of SQL Server automated DTS?

    2. Right now, the only way I can think of doing this involves manually reading each row's cells, and after validation, storing them in the fields of a dataset, which I then update. Is there a better way?

    For example, let's say the excel contains the data

    ID Name

    2  Jack

    4  Jay

    I would read these rows, create a datarow, fill the individual values and pass them to the appropriate object's (in the business logic class) Insert method. This would also involve ensuring that the record isn't duplicated (which I suppose the indices will automatically take care of).

    Sorry if I'm being too vague. Please let me know if you need more info.

    Thanks,

    Donniel

     





  • Off the top of my head, I'd go with treat the Excel file as a database via Jet and through it in a SqlBulkCopy operation as the articles bstorer referenced.  There are caveats though.  First the user performing the SqlBulkCopy needs to be part of the bulk admin security group which may be a security risk.  Second, you may need to see if the SqlBulkCopy does a row or table lock by default as this may impact simultaneous use.  And third, the Jet drivers have some incompatibilities with 64 bit OSes, so be prepared to migrate to Visual Studio Tools for Office if deploying to a 64 bit web server.

     



  • Wow...I can't believe I didn't properly FGI! Oops!

    Thanks for the links!



  • Am not really familiar with that method, will research it. Thanks for the input!



  •  So... you asked for help and then came back after a week for the answer?

     

    ...What is this world coming to?



  •  @MasterPlanSoftware said:

     So... you asked for help and then came back after a week for the answer?

     

    ...What is this world coming to?

    We're going to keep adding to this thing: 

    <font color="#ff0000" size="5">Seriously, people, quoting the OP isn't an offense against nature, but it is redundant and makes it harder to catch up on threads because you end up scrolling past the same junk over and over.  Also, if you aren't replying to the OP, please quote for some context.  It doesn't have to be the entire post you are replying to, but if I have to click "In Reply To" just to figure out who in the hell you're talking to, I'm more likely to just ignore your comment.  This is basic forum etiquette that not only helps the reader but also helps your point come through more clearly.  Thanks.</font>

    <font size="5"><font color="#00ff00">Also, don't revive old threads, especially for inane comments like "bump," or "i agree," or "thx 4 teh codez!"  Such posts are a total waste of everyone's time.  Think of it as showing up at a party as everyone else is leaving.  If a thread hasn't been posted to in over a week, the forum regulars have already said all that needed to be said on an issue.  Please, by all means, find one of the many active threads and post there.  Have a nice day.</font></font>



  • @MasterPlanSoftware said:

     So... you asked for help and then came back after a week for the answer?

     

    ...What is this world coming to?

    Correct. I was working on another section on the website, and realized I had forgotten to check back to see if I'd received replies.

    But I too, often wonder what the world is coming too...<stares off into space>



  • @donniel said:

    Correct.
     

    And now you have done it again.

     

    JFC


Log in to reply