Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
XL -> MS SQL Database (Read 3385 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
XL -> MS SQL Database
22.11.06 at 22:10:39
 
XL -> MS SQL Database
 
Duncan from Austria asked the following question:
 
I am looking for simple to use software which will perform DTS tasks for normal mortal users.
 
I want to transfer an Excel file into an MS SQL database table:
-  overwrite table completely
-  append records to table
 
Answer:
Yes, you can do this and it is soo simpel a mortal user can do this.
 
To overwrite the existing table it is easiest if you can delete all records from the table before you start. This can be done in 2 ways:

  • Truncate the table
    Run a truncate table statement from the SQL dialog: truncate table mytable;
    Advantage: this is quick and efficient
    Disadvantage: this is definitive! Truncate will implicitly do a commit.
  • Delete rows from the table
    Run a delete statement from the SQL dialog: delect * from mytable;
    Advantage: this command will be done in a transaction. You can still rollback. Another advantage is that you can partly delete a table by adding a where clause where you specify the conditions rows will need to match before they are deleted.
    Disadvantage: it can be slow, especially when the table is large. The transaction may become too large leading to problems. In Oracle you may hit rollback segment problems.

 
To let your users easilly run this statement you can create it as a SQL file or a SQL favorite.
 
Then loading in new data. There is a special dialog for this: the multiple rows insert dialog. The insert process is done and the results are presented by a status indicator that is put behind the rows. Successful or a one line error message.
Back to top
 
« Last Edit: 22.11.06 at 22:17:13 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: XL -> MS SQL Database
Reply #1 - 23.11.06 at 08:46:45
 
Duncan wrote:
Quote:

Thanks for the quick answer! But I feel my question has not been completely answered... I can cope with the sql code, but I am really looking for a tool which will perform the following:

Normal user has an XL-spreadsheet.
This XL-spreadsheet should be downloaded into an mssql database table.
In other words the same way as using the standard DTS tool but just with one or two simple mouse clicks.

 
Answer:
SQL*XL uses the multiple rows insert dialog to insert. This is a very easy to use screen. I am sure your end users can work with it.  
 
If this is too much work you can automate it by recording a macro. With a macro you can run the insert process without showing the dialog.  
 
If it really needs to be a one click process you can make it a single button click or a process that runs when you open an xls file. There are pointers to articles how to acieve this in the vba forum on the site. Let me know if you need a hand.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1