Best way to import spreadsheet into Sql Server?

I need users to upload a spreadsheet to my website (ASP.NET 4.0 framework), and then import the data from the spreadsheet into a SQL Server table.

I'll give them a template of the spreadsheet, so can control the spreadsheet's format.

What's the best approach to doing this? Should the spreadsheet be XLS, or CSV?

Also, most of the examples I've seen on the web rely on the old Microsoft.Jet.OLEDB.4.0, which I suspect I should avoid since it's deprecated. But is there another, better way? (I've seen Linq to Excel examples, but they all seem to rely on Excel being in XML format, and I was thinking native Excel would be more reliable and compact)
cdakzAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ehartfield2002Connect With a Mentor Commented:
If you do a Google search using the terms ".net read csv file", you'll see that there are many examples of how to do this that use .Net Base Class Library components.
0
 
ehartfield2002Commented:
If you can control the spreadsheet's format and specify a CSV file, then I'd say that's your best bet.  I say this mostly because CSV is platform neutral.  You aren't tied to any particular technology choice.

I have used OLEDB in the past when I had to consume a native Excel 97-2003 format.
0
 
cdakzAuthor Commented:
Is I use CSV, any suggestions no best way to read it? I expiremented a bit with OLEDB on my development machine, which is running Win 7 64 bit, and got an error. From my understanding, to get around this, I'll have to install OLEDB manually and then do some configuring to target 32-bit. All of which makes me leary for when the site is ultimately posted to the live server (initially shared Windows Server 2008, but likely moved to VPS Server 2008 soon after).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.