Solved

Excel 2003 -> SQL server

Posted on 2004-09-06
8
174 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 22

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Assisted Solution

by:Wim ten Brink
Wim ten Brink earned 250 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 250 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
 
LVL 6

Author Comment

by:PHD
ID: 12004708
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

740 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