locked
Move data from Excel to SQL (can the data be front ended with Excel?) RRS feed

  • Question

  • I have a spreadsheet that a number of users share and save, perhaps 10 times a day. Periodially a user will have put a bunch of data in and excel crashes, changes made are lost. The shared spreadsheet solution has seen it's usful life for us and I would like to move away from this to SQL backend. Does anyone have an idea on how to preserve the data in the spreadsheet, including text and background colour, while moving into a database. Ideally I would like to front end the data with Excel and backend with a SQL connection. If Excel crashed, only the last transaction would be lost. 

    Would this be possible? I have SQL 2008 and Excel 2007. Perhaps I need to write something in dotnet with a data-linked table (although I don't know how I would get the existing formatting across). Any ideas on this would be appreciated.

     

    Monday, March 14, 2011 6:20 PM

Answers

  • With a lot of VBA code, you can make just about everything but Excel if not really designed to serve as a frontend to SQL-Server and probably that you will end up with a little monster that will be hard to develop and maintain.

    Excel is just like a big calculator and it's fine for that if you need just that but if you need to go beyond that, your best solution would be probably to get rid of Excel completely and to go to a solution based on SQL-Server as the backend and possibly Access as the frontend; either as an ADP project or with ODBC Linked Tables.

    If necessary, you can put some functons somewhere to export some reports to Excel or to have an Excel spreadsheet capable of importing its data from the SQL-Server but for entering the data, ie. to server as the fronten, Excel is not really the tool for that and while it may work more of less, to cost of developing such a solution will not be justified with the result that you'll get at the end.  You can keep Excel but only for data crunching and generating reports based on data imported from the backend SQL-Server when they are needed.

    • Proposed as answer by WeiLin Qiao Sunday, March 20, 2011 6:32 AM
    • Marked as answer by WeiLin Qiao Wednesday, March 23, 2011 4:53 AM
    Tuesday, March 15, 2011 6:22 AM