As POC I have done a simple project to Import Excel data to SQL Table.
It 's quiet simple & easy. I have tried to Import Excel in 3 different ways to identify which one is most effective way.
I have came across these scenarios:
- Using .Net OleDbCommand
- Using SSIS
- Using OpenXml
- Using XmlTextWriter
Let's brief one by one.
Using .Net OleDbCommand
It's just save the uploaded file & read from the FTP, then map with the columns in both Excel & DB, finally copy data in to the database.
Using SSIS
For SSIS code end it looks easy, But we have to develop and SSIS package as the requirement. If we have the deployed SSIS package, we need to just trigger the function only.
Developing SSIS is easy & fun, But somethings I am stuck with the configurations. Only the important figures I have attached.
By defining variable we can make the package, dynamic for Excel & database.
Using OpenXml
It is similar to OleDbCommand but OpenXML is better than COM Interop in .Net. Though you don't see any significant performance in OpenXML than COM, it is easy to code. Easier to maintain. Considering the large amounts of data you should read, take a look at below link for optimizing.
Using XmlTextWriter
Here data read from an XML file content and write the same content to an Excel file. Using an XmlReader for read the XML file to the Dataset . Loop through the Dataset and add the content to the Excel file, that become slower for the big amount of data.
Refer:
https://www.youtube.com/watch?v=jhPp_Hz54BU
http://www.gemboxsoftware.com/support/articles/article-read-write-openxml-asp-net
http://blogs.msdn.com/b/brian_jones/archive/2010/05/27/parsing-and-reading-large-excel-files-with-the-open-xml-sdk.aspx
https://www.youtube.com/watch?v=79mi31caAag
I think this will be helpful for you..
B' happy always....... :)
