Import Excel sheet into SQL Server through file upload in asp.net

Question

I am designing for a requirement which would provide user with an asp.net upload control to upload excel file(xls/xlsx/any advanced format) and load this excel data to SQL server 2008 data table with custom column mapping.

On internet I have found many blogs to implement this functionality using Jet OLEDB provider but there are some limitation to this functionality as below -

The data is truncated for the data after first few rows in the table - Reasone is Jet provider checks initial few rows to decide on the data type and its size. Resolution that I found for this is go to Registry and change the value for "TypeGuessRows". My question is - Is this advisable to do this on the web server in case of asp.net application
Unicode characters does not get imported correctly - This issue can be resolved by changing the connection string to add "CharacterSet=65001;" as per link UnicodeCharacters

Again question is whether this would be advisable and full proof solution.

What is the best way to implement Import Excel to SQL Server 2008 for huge data intensive excel to be imported to Database without loosing any data and unicode characters?? The solution should be compatible with Excel 2003/2007/2010/2013.

Hi Nagraj, thanks for the link, however I am not importing image to database — CHash11
– CHash11, Commented Dec 31, 2013 at 9:20

nickand · Accepted Answer · 2014-01-23 14:58:49Z

2

I use this library to import xlsx files after upload.

https://exceldatareader.codeplex.com/

Does this help?

answered Jan 23, 2014 at 14:58

nickand

12710 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CHash11 Over a year ago

Thanks Nickand, I am using ClosedXML library similar to this one, however I am still doubtfull whether this would work for above to problems I pointed in question. I tested with basic scenarios and it works falwlessly. How is your experience with this library?

nickand Over a year ago

I looked at Closedxml and didnt need a excel writer so decided on exceldatareader. It handles the job well, imports about 200,000 lines regularly but havent tried unicode characters yet. Can be put into a dataset or each row handled.

Parm.denny · Accepted Answer · 2014-04-25 02:11:26Z

0

You could help you. https://freenetexcel.codeplex.com

 Workbook workbook = new Workbook();

 workbook.LoadFromFile(@"..\..\parts.xls",ExcelVersion.Version97to2003);
 //Initialize worksheet
 Worksheet sheet = workbook.Worksheets[0];

 DataTable dataTable = sheet.ExportDataTable();

answered Apr 25, 2014 at 2:11

Parm.denny

1

Collectives™ on Stack Overflow

Import Excel sheet into SQL Server through file upload in asp.net

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related