Excel to SQL Question VS C#

Posted on 2012-09-07
Last Modified: 2012-09-09
Using a program, is there anyway for a user to be sitting on a spreadsheet, have them mark rows and columns and then have this data copied into SQL? I was hoping I could create an application using VS C#, have it capture this data from the Windows clipboard and then my program would then insert this data into SQL. The structure of the rows and columns would always be the same.
Question by:rwheeler23
    LVL 49

    Expert Comment

    by:Julian Hansen
    Not natively but you can write some VBA code to connect to the server and upload the data.

    That's the easy bit - the hard bit is synchronising the data - are you going to overwrite the database each time or will you need to make updates to exising records.
    LVL 35

    Expert Comment

    by:Miguel Oz
    The answer is that it is not feasible: Clipboard information is hard to handle as it will come out as tabbed text in your program. (test it by copying from Excel to Notepad)

    As an alternative, you are better off have your program reading the Excel file and export the data to to SQL.
    Then your program can do:
    - read Excel file (Lock it for exclusive usage)
    - Validation.
    - Check whether to update or add new rows.
    - Lock the database for doing all required changes.
    - Update SQL database with Exxcel contents

    Depending on your Excel file you have two choices :
    - You can use InterOp or NPOI classes.
    - You can use OleDb .

    Note: You can also build an Add-In and in your ribbon have a button called Transfer To SQL that will read the content on your current sheet and export to SQL..

    Author Comment

    My back-end thought is no different than reading a csv file. All records will be written to a table containing a unique identifier column plus a date/time stamp. My code with then present to the user a lookup window and the user can select which group of records to import. I will then mark these records as imported so they will not be brought over again. Eventually if any records were imported more than once they will see this in their financial statements. It will be up to them to do a reversing entry.

    Getting back to the xlsx file, is it possible to directly read an Excel spreadsheeet? If I can do that, then I will insist that separate spreadsheets be kept and then I can log the spreadsheet name and prevent the same spreadsheet from being read more than once. If you can read an xlsx file directly, can you point me to any sample code that does that? I would prefer to do this all in VS C#.
    LVL 35

    Accepted Solution

    Yes , it is possible, for Excel xlsx, you can use NPOI (free):
    How to read cells:
    If you are not allowed to use free software, a good commercial alternative is:

    Note: Having said that. It is more efficient to read csv files, but I suppose you would like to have all your data in one file (It is more manageable anyway)

    Author Comment

    I am actually thinking of the users. They live and breathe Excel. I do not know if I trust them to consistently tell Excel File ->Save As->Change type to csv and so on and so on. I am trying to have them perform as few steps as possible.

    Thanks for the tips. I can now add one more thing to my 'to do' list.

    Author Closing Comment

    Thanks for this tip!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now