May 4, 2009 at 5:42 pm
I need to create a web based application that would update/insert/delete some records in the table in SQL Server db. Users would want to maybe "export" filtered data (not whole table) into Excel sheet, edit it there and import the updates back using SSIS. Not all the table should be updated but only what was imported from the Excel.
table is simple like ID, Description.
What would happen if several users would want to import their Excel sheets into the same table at the same time?
I would prefer to let users create/delete/edit records in the web app in the datagreed but they insist on the Excel.
Maybe somebody can point me in the right direction, what the workflow should be. Is it worth to use SSIS for this purpose? I didn't work with SSIS before.
Situation is similar to this: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71233
----------------------------------DATA FLOW----------------------------------
Excel as data source -> data Conversion(to data tyope for table) ->Sort -> Merge join as left join component
Table as data source -> Sort -> Merge join as 'right' table
Join on the primary key to the excel, and include the table primary key as part of the resultset.
Merge Join -> conditional split
conditional split (where tablePK in result in Merge join is null) -> Table Destination (insert, since null table key = new rows)
conditional split (where tablePK in result in Merge join is NOT null) -> OLE DB Command using column names to replace parametre's in an update statement.
----------------------------------
This does an update for rows that exist, based on PK match to the excel spreadsheet, and an insert for rows that don't exist.
*** I am stuck at this point: "Join on the primary key to the excel, and include the table primary key as part of the resultset." Do I need to create an Excel as destination here and send there output from the left join?
May 5, 2009 at 6:55 am
You may want to look into using VBA in Excel and letting the users do the updates directly from Excel. As far as multiple users updating, it is like any other application, last one in wins. One way to manage updates would be to include a rowversion/timestamp column in the table(s) and compare it as well as the primary key. If it has changed you would not allow the update, but make the person re-load the spreadsheet to verify that they still want the change.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 6, 2009 at 12:53 pm
Thanks a lot Jack. I will look into it.
May 7, 2009 at 6:33 am
Vika
I would do as Jack recommends. Working with SSIS and Excel is not that easy (
look at the questions in this forum).
You can do it in Excel with VBA but be careful if you have many concurrent users
and a lot of data in Excel to be updated/inserted into the database.
I have designed budget and forcast systems with 100 users like project leaders
and they use Excel also as a tool working off line and then by pressing a button
loads data back to the database. Its not very much of data per update less than
200 rows.
I include som VBA code examples (not tested exactly as written).
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim source,key,userid as string
Dim K as long
'Userid the user is promted and maybe validated.
source = "Data source=Name of/IP adr of databaseserver;User ID=XXX;Password=YYY;Initial Catalog=databasename"
'Comment string source or part of I usually save in file not accesable for the user.
'Remote oledb which means that the database and the client Excel can be separated on a company network or Internet
con.Provider = "sqloledb"
con.Properties("Network Library").Value = "DBMSSOCN"
con.Open source
'Working with a recordset
Set rs.ActiveConnection = con
rs.CursorType = adOpenDynamic 'Or change type
rs.LockType = adLockOptimistic 'Or change type
'If you have records in a table which you want to update or insert from data in the Excelsheet
'Update (if exist) or insert a single record!
rs.open "SELECT field1,field2 etc FROM Tabel WHERE something" 'Something can involve the user logged in"
if rs.eof
then
rs.addnew
else
rs.edit
end if
rs!field1=cell(x,Y).value
rs!field2=cell(x,z).value
etc
rs.update
rs.Close
'If you want to update part of existing data in a table from data in an Excelsheet
'or insert new records you can do it in a loop.
'Include userid and a timestamp recommended.
'Lets say you have a sheet with records from row 1 to XXXX (xxxx can be found by lastcellfunction)
'kolumn A is a keyfield to select where from.
'Kolumn B field1
'Kolumn C field2
etc
for K = 1 to xxxx
key = Cell(K,1).value 'String should be unique generated and locked in Excel. Maybe
'an autogenerated number can be used.
rs.open "SELECT keyfield , field1 , field2 . updated, userid FROM Tabel WHERE keyfield = '" & key & "'"
if rs.eof
then
rs.addnew
rs!keyfield=key
rs!field1=cell(K,2).value
rs!field2=cell(K,3).value
rs!Updated = Date & " " & Time 'DateTime format
rs!Userid = userid
else
rs.edit
rs!field1=cell(K,2).value
rs!field2=cell(K,3).value
rs!Updated = Date & " " & Time 'DateTime format
rs!Userid = userid
end if
rs.update
rs.close
next K
Set rs = Nothing
Set con = Nothing
Let us hear about your progress.:-)
/Gosta
May 28, 2009 at 8:57 am
Gosta, thanks!
it helped me a lot. I completed it and set it up on the server for my boss to test.
What would I do without you guys...:-P
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply