importing file excel to table

[versillus]

hi, I'm using visual foxpro 6 and Sql server 2000.
Now i have excel file , i want to import this excel to sql table.
For example i have excel file called icmstdt.xls and i want import this to sql table called icmstdt too.
Can anyone help me to solve this problem.

 

[Mike Lewis]

There are several way of doing this. The easiest would be to create a DSN for the Excel file, and then to use the Data Transformation Services tool in SQL Server to import it. You can find DTS in the Enterprise Manager or under Import/Export in the Start menu.

Another option would be to save the file as a DBF from within Excel, and then to write a VFP program which copies it, row by row, to the server. But DTS is easier.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[versillus]

I have already try DTS in sql. But apparently it create new table. In my case the table already exist, all i want to do is to add the record to that table.
For the option No 2. How can i put row to row to sql table. How's the syntax ?

 

[Mike Lewis]

Versillus,

I thought DTS had an option to append to an existing table rather than create a new one. You might want to check that -- I'm not 100 percent certain.

If not, the other option would be to use a remote view in VFP. Once you have got the Excel file as a DBF, create an updateable remote view on the server table (if you don't know how to do that, read the Help section on accessing remote data).

Then, write a little program which loops through the DBF, appending each record in turn to the remote view. As you add each record, it will be inserted into the server table.

This is a bit of an over-simplification, but it should give you a good start.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[versillus]

MikeLewis,
Thanks for your suggestion, I think i will try use DTS. I want to ask you about dtsrun.
Can i run dtsrun in Visual Foxpro, which i save DTS package in file not in Sql.

 

[Mike Lewis]

Versillus,

Yes, I'd agree that DTS is a better choice in most cases. As for running it from within VFP, take a look at thread184-773049

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com