Solved

Excel 2003 -> SQL server

Posted on 2004-09-06
8
183 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Course of the Month6 days, left to enroll

626 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