?
Solved

VS 2008 C# read an Excel Spreadsheet

Posted on 2011-09-21
4
Medium Priority
?
288 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

752 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