1,105,286 Community Members

Feeding a SQLServer db with Excel spreadsheets vs Web interface

Member Avatar
cris651
Newbie Poster
9 posts since Apr 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
AleMonteiro
Master Poster
758 posts since Aug 2010
Reputation Points: 115 [?]
Q&As Helped to Solve: 143 [?]
Skill Endorsements: 29 [?]
 
0
 

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 ^^

Member Avatar
pritaeas
mod_pritaeas
11,285 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,829 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
0
 

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

Member Avatar
adam_k
Veteran Poster
1,056 posts since Jun 2011
Reputation Points: 239 [?]
Q&As Helped to Solve: 212 [?]
Skill Endorsements: 17 [?]
 
2
 

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.

Member Avatar
cris651
Newbie Poster
9 posts since Apr 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
adam_k
Veteran Poster
1,056 posts since Jun 2011
Reputation Points: 239 [?]
Q&As Helped to Solve: 212 [?]
Skill Endorsements: 17 [?]
 
0
 

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

Question Answered as of 1 Year Ago by adam_k, pritaeas and AleMonteiro
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article