SQL is creating a table instead of a file - whats wrong with my SQL-String

[fracalifa]

Hi all, I want to export my data from a SQL-Server table to an Excel-File. The following code shows my routine.

oConnection.ConnectionString = "Provider=SQLOLEDB; Server=Servername; database=ABC; Trusted_Connection=yes; User ID=ABC_MAN; Password=secretABC";
oConnection.Open();
oCmd.Connection = oConnection;
oCmd.CommandTimeout = 60;
oCmd.CommandText = "SELECT id AS NO, name AS NAME INTO [Excel 8.0;DATABASE=Test.xls] FROM MyDataTable WHERE typ=1";
oCmd.ExecuteNonQuery();

But instead of an exel file the string creates a new table with the name Excel 8.0;DATABASE=Test.xls Where is my fault ? Different examples has shown that this should work :confused: Thanks Frank

[Bassam Saoud]

The SQL SELECT INTO statement is used to select data from a SQL database table and to insert it to a different table at the same time. Tutorial[^]

[fracalifa]

Thank you, I know what SQL normally does ... but I think you don't know what I am expecting from this query ... Thank Frank

[Bassam Saoud]

BCP [^] into a CSV file

[scottgp]

It looks like you want something like this: INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\testing.xls;', 'SELECT Name, Email FROM [Sheet1$]') SELECT Name, Email FROM tblnames or 1 Export data to existing EXCEL file from SQL Server table insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;', 'SELECT * FROM [SheetName$]') select * from SQLServerTable I'm not sure of the correct syntax since I haven't used it before. I found it at after searching for "sql server export to excel [Excel 8.0;DATABASE=Test.xls]" Scott

[Lost User]

I found this link[^], which may be of help.

[fracalifa]

Thank you, have tried your suggestions, but got some OLEDB-unknown errors, what ever that meens. (But I think,(not sure) the disadvantage is that the Excel-table must already exist) But thanks alot Frank

[fracalifa]

Hi Richard, it's an interesting link - I will convert the code to C# - will take some minutes ;P I will let you know the result. Tnx Frank

[Niladri_Biswas]

Try this

Create an Excel file named testing having the headers same as that of table columns and use this query

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

To export data from Excel to new SQL Server table,

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

To export data from Excel to existing SQL Server table,

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')

Obtained from SQL Server Forums - Export to Excel Hope this helps. :)

Niladri Biswas

[fracalifa]

Thank you, I also found this example, but the examples assume, that an Excel file already exists. But I remember, 5 years ago, I could generate a new file with the sql-sting above (within VB6) without an additional action. Why can't I do that today - or how can I do that today ? Tnx for your time Frank

[Tripathi Swati]

if u dont want to use openrowset or bcp then best way is use string builder and then response.write with

response.AppendHeader("Content-Type", "application/vnd.ms-excel");

here you can give file name n storage location too.

Reasons are not Important but Results are Important. Swati Tripathi

modified on Friday, November 20, 2009 3:05 AM