Solved

Excel 2003 -> SQL server

Posted on 2004-09-06
8
168 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 17

Assisted Solution

by:Wim ten Brink
Wim ten Brink earned 250 total points
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 6

Author Comment

by:PHD
Comment Utility
OK, thanks a lot.

First I will try your solution.
0
 
LVL 6

Expert Comment

by:bpana
Comment Utility
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
Comment Utility
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
Comment Utility
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now