Feeding a SQLServer db with Excel spreadsheets vs Web interface

[cris651]

Hi everyone, I need suggestions. A little corporate needs to track its data with 2-3 employees making a daily data entry/updating of few records (20-30). Data must be available to other employees. They want to store data in a SQL Server db that has been already implemented. The whole db doesn't reach 10,000 records.
They would like to implement a Web interface but they should pay someone external for this; internal resources proposed to use a shared Excel spreadsheet to be modified daily and a SQL Server job running at intervals to import Excel data.
Any problem in using Excel spreadsheets in such a scenario? Any help will be appreciated.
Thank you! Cris

[AleMonteiro]

I think there's no problem, it's good. If they already have the data in excel it's the easiest way to update the DB.

Couple months ago I did an web app so the user could upload the excel and import to the SQL DB.

If you are thinking of doing such thing, I recommend using C# and EPPLus lib, it was really peaceful to work with ^^

[pritaeas]

I haven't automated this before, but I know you can use the BULK INSERT query on a CSV file.

From SQL Server Management Studio you can import Excel directly: http://www.wiseowl.co.uk/blog/s231/schedule_data_import_in_sql_server_pt1.htm

[adam_k]

What I don't like is "shared" and "intervals". Even in small scale shared workbooks can cause user frustration and problems. Running a job at intervals reading a shared workbook, that users might be still editing - but haven't saved or have saved half-way sounds like bad idea.

May I propose an Excel macro using ADODB to insert records directly in SQL ?
I've used this in past to read & write to SQL and you simply give a template to the user to fill in and when done he/she has to press a button and the magic happens.

The code required should be :

Public Sub create_request()
Dim conn As New ADODB.Connection
Dim comm As New ADODB.Command
Dim rs As New ADODB.Recordset

conn.ConnectionString = "Your connection string here"
conn.Open

comm.ActiveConnection = conn
comm.CommandText = "insert into table (column1, column2, column3) values ('" & 
Sheets("Sheet1").Range("A2").Value & "','" & Sheets("Sheet1").Range("B2").Value & "','" & Sheets("Sheet1").Range("C2").Value & "')" 

comm.Execute
End If

If you have multiple records to insert, you can either loop through records or union them in a select.

I prefer it this way, because users know instantly if their insert succeeded and the template can be nice, with vlookups, data validation, calculations and whatever Excel supports. You also can save with data from several worksheets and can validate before the insert.

[cris651]

Thank you all. Importing into SQL Server could be done overnight, as they told me that real-time update isn't needed, so they could avoid concurrency issues; in the meantime, I realized that some entered values must be checked against the database (there are uniqueness constraints), so inserting the values directly in SQL through Excel spreadsheet, as adam_k explained, could be the best solution. I'll share with them your suggestions so they can begin to work on the solution. Thank you again! Cris

[adam_k]

If this issue has been resolved, please mark this thread appropriately