Excel to SQL Question VS C#

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.
rwheeler23Asked:
Who is Participating?
 
Miguel OzConnect With a Mentor Software EngineerCommented:
Yes , it is possible, for Excel xlsx, you can use NPOI (free):
http://npoi.codeplex.com/
How to read cells:
http://stackoverflow.com/questions/5855813/npoi-how-to-read-file-using-npoi
If you are not allowed to use free software, a good commercial alternative is:
http://www.gemboxsoftware.com/spreadsheet/overview

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)
0
 
Julian HansenCommented:
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.
0
 
Miguel OzSoftware EngineerCommented:
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..
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
rwheeler23Author Commented:
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#.
0
 
rwheeler23Author Commented:
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.
0
 
rwheeler23Author Commented:
Thanks for this tip!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.