• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

VS 2008 C# read an Excel Spreadsheet

Is it possible to extract values using VS 2008 C# from an Excel Spreadseet? We have a user who refuses to give up Excel. What I was thinking was to create a worksheet within the spreadsheet where I can store information in a format that I can then use to import data into SQL. I am looking for the best way to pull that data out of the spreadsheet and into SQL. I was thinking of creating an application where all the user does is point to the location of the spreadsheet and the code will do the rest.
2 Solutions
You can read an excel spreadsheet from c# using an ado.net connection.

Check out this link


DBAduck - Ben MillerPrincipal ConsultantCommented:
You could do that or you could use SSIS to extract the data and load it into SQL.

The connection string you can use for VS 2008 C# is at http://www.connectionstrings.com
There are many options here - you could use ADO.NET or SSIS as others have previously mentioned. You could create an Excel object in your code and manipulate Excel through OLE Automation. You could use the OpenSDK and read/write the Excel XML file. You could use the ODBC interface directly, missing out the ADO layer.

I have seen an application in the past which used some Excel VBA in the spreadsheet to validate and export data into a delimited format which was then imported into the database using RDBMS tools manually. This operation was performed by 2nd line support so they could monitor and data cleanse the input to the database (couldn't rely on the users to get it right and some validations couldn't be done automatically!). Is there scope for a manual operation in your requirement?

You could do this by creating an intermediate database with MS-Access - have ODBC connected tables to the spreadsheet and your main database in the intermediate database. Write Access queries to move data between the two connections. Run the queries from your C# code through an ODBC connection. This approach saves you trying to parse SQL statements in your code which can get tricky sometimes.

rwheeler23Author Commented:
Well it certainly appeears that this is possible. The only MS language I know is VS C#. As long as I can give the user a simple interface where all they do is browse for a file and then push one button I will be a happy camper. Thanks for all the advise.
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now