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)
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)
ASKER
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I have used OLEDB in the past when I had to consume a native Excel 97-2003 format.