Excel to SQL Question VS C#

Posted on 2012-09-07
Medium Priority
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
  • 3
  • 2
LVL 60

Expert Comment

by:Julian Hansen
ID: 38377706
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 36

Expert Comment

by:Miguel Oz
ID: 38378417
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

ID: 38378503
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#.
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 36

Accepted Solution

Miguel Oz earned 2000 total points
ID: 38378656
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

ID: 38378674
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

ID: 38381381
Thanks for this tip!

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

807 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