Solved

VS 2008 C# read an Excel Spreadsheet

Posted on 2011-09-21
4
275 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

730 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