0

I would like to export data from a SQL Server stored procedure to an Excel file. How can I achieve that?

I test like that:

insert into OPENROWSET(
   'Microsoft.ACE.OLEDB.12.0', 
   'Excel 8.0;Database=D:\test.xlsx;;HDR=YES', 
   'SELECT EmpID FROM [Sheet1$]')
select * from tb1

but it returns an error:

Column name or number of supplied values does not match table definition.

I think it's because Excel doesn't have columns right? But there's no way to pre-write columns before exporting to Excel, and SQL Server can't create Excel by itself ...

I also tried with bcp:

bcp "SELECT * FROM mydb.tb1" queryout 'D:\test.xlsx' -t, -c -S . -d DESKTOP-D3PCUR7 -T

It returns error:

Incorrect syntax near 'queryout'.

How can I export table to Excel easily in SQL Server?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Loran
  • 772
  • 4
  • 16
  • 38
  • Try using double quotes around your file name in your bcp command: `bcp "SELECT * FROM mydb.tb1" queryout "D:\test.xlsx" -t , -c -S . -d DESKTOP-D3PCUR7 -T` – marc_s Apr 21 '20 at 06:48
  • @marc_s, I already tried like that but still getting the same error. I change query like `SELECT * FROM mydb.dbo.tb1` but It still shows the same error. – Loran Apr 21 '20 at 07:07
  • Also: `bcp` cannot create `.xlsx` files directly - "only" csv/tsv which you can then import into Excel – marc_s Apr 21 '20 at 07:48

0 Answers0