Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel 2003 -> SQL server

Posted on 2004-09-06
8
Medium Priority
?
192 Views
Last Modified: 2010-08-05
Hello,

I'm writing a delpi application. In the application when a user click on a button, I have to transfer data from a formated Excel sheet into Sql server 2000.

What is the best way and How can I do that ?

Thank

Sang-Do

0
Comment
Question by:PHD
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 23

Expert Comment

by:Ferruccio Accalai
ID: 11989329
you could access the excel sheet data using ADO and then always using ADO insert datas to sql server (2 connections, 1 to excel and 1 to sql server)

to connect to excel using ado see http://delphi.about.com/library/weekly/aa090903d.htm
0
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 11989911
I think you could do this directly from SQL Server, without the need of Delphi in-between. Okay, you'd have to call a stored procedure to tell SQL Server to do it's work but it might be possible more directly, since both SQL Server as Excel have excellent communication protocols. I think this Q is better asked in the SQL Server secion or Excel section of EE.

You could do it through Delphi, though. Then Ferruccio68 has given you the correct answer, though. Use ADO to connect to both Excel and SQL Server, then kick the data from one datasource to the other.
0
 
LVL 6

Author Comment

by:PHD
ID: 11990305
OK thank a lot but now I have created an sql package that import Excel data into sql server via DTS.

So, If  I can launch the package from my delphi application, my problem is solve.

Any Idea ?

  WorkShop Alex : I write a program for a client that need to import excel files when he want. I cannot ask him to do it via SQL
                           server directly.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 17

Assisted Solution

by:Wim ten Brink
Wim ten Brink earned 1000 total points
ID: 11990483
I know. But you can create a stored procedure in your SQL Server environment and CALL this procedure from Delphi, using the TADOStoredProc.ExecProc you just execute the stored procedure. If you have any parameters in it (like the filename of the Excel sheet) you could pass them to the stored procedure or retrieve them after the stored procedure is done.

Anyway, the advantage here is that you let SQL Server handle the whole import for you. IT increases the performance a bit since data doesn't have to be sent from sheet to Delphi to SQL Server. Furthermore, it is easier to adjust if it's an SQL script.

Thus, all you need to do is write that stored procedure. :-)
0
 
LVL 6

Author Comment

by:PHD
ID: 11990521
OK, thanks a lot.

First I will try your solution.
0
 
LVL 6

Expert Comment

by:bpana
ID: 11991115
use the dtsrun command prompt utility:

WinExec('cmd /c dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password', SW_HIDE);
0
 
LVL 6

Accepted Solution

by:
bpana earned 1000 total points
ID: 11991206
or maybe better is using master..xp_cmdshell stored procedure:

ADOConnection1.Execute('exec master..xp_cmdshell ''dtsrun /Sserver_name /Uuser_name /Ppassword /Npackage_name''', cmdText, [eoExecuteNoRecords]);
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question