Importing xls file

[WolverineLogan]

Hi

I would like to know how i can import a xls file into the Enterprise Manager using SQL from the Query Analyser.
I know i about the DTS wizard but it wont help because i need to incorporate this function into my application i am working on using Delphi 5.


Thanks

 

[tlbroadbent]

I don't believe you've correctly stated what you require. This is what I think you mean. If I've misinterpreted your question, please clarify.

How can I import an Excel Spreadsheet into a SQL Server table from a Delphi application?

Enterprise Manager (EM) is a SQL administration tool not a database. You cannot import data into it. You can use it to start other tools such as the Import/Export Wizard or DTS Package Wizard. These tools will help you create an import package.

Query Analyzer (QA) is another SQL tool for analyzing and running queries. QA can be used to import a flat file into a SQL table using Bulk Insert. You can also execute a package or start a job from QA. QA cannot import Excel spreadsheets directly unless the spreadsheets are linked.

Here are some options for importing Excel data when using a Delphi interface. I'm not familiar with Delphi so these options are limited to methods that I know. You may want to check in the Delphi forum for additional methods.

1) Export the Excel data to a flat file. Create a Bulk Insert query and end execute it from the Delphi application. See SQL BOL for details on Bulk Insert.

2) Create and save a DTS package that imports the Excel spreadsheet. (This is very easy.) Execute the DTS package from the application. See Data Tranformation Services and Importing/Exporting data in SQL BOL. Also see faq183-962.

See the followig page for methods of executing a job from applications. Delphi is not in the list but the info may prove helpful.

3) If it proves difficult to execute a package from Delphi, you could create a Job that runs the package. The Delphi application could start the Job.

4) You could create a linked server to the spreadsheet and insert the data into a table using query statements. See Linked Servers in SQL BOL. See also sp_addlinkedserver. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[AWithers]

I dont think you can do if from sql analyser. I think you need to create a DTS package and store it then call it from the command prompt (type in dtsrun ? for options or see books on line)

Andy

 

[raqeeb]

I would suggest develop a COM which imports data from Excel to SQL Server and this COM can be called in your application, guessing that Delphi can use third party COM Objects in its programming.

Raqeeb(Devloper)

http://www.megamastersoft.com