Solved

VS 2008 C# read an Excel Spreadsheet

Posted on 2011-09-21
4
277 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 250 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 24

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 250 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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

705 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