export an excel work sheet into a sql server 2014 table via asp.net vb

Assuming its a single sheet, convert it to CSV and then use the sql load CSV process to load the data.

Whatever you do, don't automate excel.

Not only is that frowned upon its not actually licensable, and there are plenty of guides by Microsoft on how to avoid automating office and plenty of utilities by 3rd parties to do all of the heavy lifting for you too.

Hell, even if you need multiple sheet, you could use powershell's import-excel module and use it to grab all the data from the sheets, and then export them as CSVs, and call ths SQL CSV Load function, to create some temp tables or pass it to a sql stored proceedure that loads the data.

Once you have the data in CSV format its a matter of running SQL commands either directly from .net or in sql stored proceedures to do the dataload.

generally the code is best just making sure data meets the format needed by SQL while the actual data load and processing should probably be done in SQL, however that is preferential fof speed and reliability but not an absolute requirement as you can do all of this in code and then have the SQL database be a dumb back-end, but this rather defeats the point of putting it in the SQL back end, as its function IS data manipulation.

Appreciate the comment!

sorry but I guess I left out a few important details

I would be able to do this myself using your suggestion of  exporting it as a csv and then using sql commands


I was trying to develop a "turn key" application for an end user


basically there is a an excel file from a vendor that is dropped in to a file path location each week

It will have the same column headers (differing amount of rows)

I have the application allowing the user to select the path using file dialog

was not trying to automate excel

since it is excel 2013 was hoping to use officeopenxml

found a couple c# examples that might be useful   but not all that familiar with c#

Looking or example if possible


Thanks!

Any other thoughts????

Hi Ben

Appreciate the comments!




I realize my original question didn't have a lot of details

Though I did mention asp.net (vb) :-)

I know it is possible to open the excel file manually and save as a csv

I was just hoping to keep the end user from having to do this

Since the format of the excel file will remain the same

I think what I may do is create an ssis package to copy the excel file to the table

have a stored procedure call the ssis package

execute the stored procedure from the asp.net (vb) application

as I mentioned earlier I saw an example of using officeopenxml using c# but was hoping someone could pont me to a vb version

Thanks again!