Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel 2003 -> SQL server

Posted on 2004-09-06
8
Medium Priority
?
187 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
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…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

705 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