[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VS 2008 C# read an Excel Spreadsheet

Posted on 2011-09-21
4
Medium Priority
?
307 Views
Last Modified: 2013-12-17
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.
0
Comment
Question by:rwheeler23
4 Comments
 
LVL 1

Accepted Solution

by:
RG59 earned 1000 total points
ID: 36576792
You can read an excel spreadsheet from c# using an ado.net connection.

Check out this link

http://zamov.online.fr/EXHTML/CSharp/CSharp4.html

0
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 36577882
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
0
 
LVL 7

Assisted Solution

by:tampnic
tampnic earned 1000 total points
ID: 36579212
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.

Cheers,
  Chris
0
 

Author Comment

by:rwheeler23
ID: 36580239
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.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month17 days, 14 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question