Unable to export to excel using BCP

Question

Unable to export to excel using BCP

mihir.nasikkar

SSC-Addicted

Points: 459
More actions
July 1, 2011 at 6:59 am

#240845

Hello All,
I am unable to export the output of the following query to excel using bcp.
The Code / T-sql script for the same is as follows :
USE AdventureWorks ;
DECLARE @sql NVARCHAR(4000);
DECLARE @fullFileName NVARCHAR(40)='D:\Query Excel Files\Test.xls';
DECLARE @queryString NVARCHAR(4000)=
' SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
where OBJECT_NAME (f.referenced_object_id) = ''Address'' ';
select @sql = 'bcp "' + @queryString +'" queryout "' + @fullFileName + '" '
-- execute BCP
print @sql
Exec master..xp_cmdshell @sql
Please let me know if i need to add something more in the above script .
Also, let me know if any further information is needed .
Thanks,
Mihir 🙂

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323754 More actions · Answer 1

what error do you get?

i think bcp exports as raw text only, so any resulting file, no matter what extension you put on it, needs to be opened in something like notepad, or a better, raw text editor like EditPlus, Notepad++ or something.

I've create csv,txt and tsv (tab seperated values) via bcp.

are you getting an error, or is the issue when you try to open the .xls file you created, you get a not valid excel file error or something?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mihir.nasikkar SSC-Addicted Points: 459 More actions · Answer 2

Hi Lowell,

First of all , Thanks for prompt reply.

I do not get any error. In fact i get the output mentioned below.

output

usage: bcp {dbtable | query} {in | out | queryout | format} datafile

[-m maxerrors] [-f formatfile] [-e errfile]

[-F firstrow] [-L lastrow] [-b batchsize]

[-n native type] [-c character type] [-w wide character type]

[-N keep non-text native] [-V file format version] [-q quoted identifier]

[-C code page specifier] [-t field terminator] [-r row terminator]

[-i inputfile] [-o outfile] [-a packetsize]

[-S server name] [-U username] [-P password]

[-T trusted connection] [-v version] [-R regional enable]

[-k keep null values] [-E keep identity values]

[-h "load hints"] [-x generate xml format file]

NULL

Do you mean that we cannot export the output to excel using 'bcp' ?

I am working on 64-bit SQL Server . So When i use OPENROWSET command , i get this error

Msg 7403, Level 16, State 1, Line 47

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

Is there any way i can resolve this ?

Thanks,

Mihir

Lowell SSC Guru Points: 323754 More actions · Answer 3

OLE DB provider "Microsoft.Jet.OLEDB.4.0" is a 32 bit driver...so if your sql instance is 64 bit, you'd get that error.

you probably need to download the updated 64 bit ACE drivers for Office 2007+ to use the openrowset command

here's an example i think i tested and it worked:

--works on my 2005 32 bit express isntall

SELECT * FROM OPENROWSET('MSDASQL',

'DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);

UID=admin;

UserCommitSync=Yes;

Threads=3;

SafeTransactions=0;

ReadOnly=1;

PageTimeout=5;

MaxScanRows=8;

MaxBufferSize=2048;

FIL=excel 12.0;

DriverId=1046;

DefaultDir=C:\Data\BlockGroups_2010;

DBQ=C:\Data\BlockGroups_2010\AKblockgroup.xls',

'SELECT * FROM [AK$]')

--works on my 64 bit SQL2008 install

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=C:\Data\BlockGroups_2010\AKblockgroup.xls',

'SELECT * FROM [AK$]')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mihir.nasikkar SSC-Addicted Points: 459 More actions · Answer 4

Is there any option other than OPENROWSET to export the result set into excel spreadsheet ?

Thanks

Lowell SSC Guru Points: 323754 More actions · Answer 5

mihir.nasikkar (7/1/2011)
Is there any option other than OPENROWSET to export the result set into excel spreadsheet ?
Thanks

I'm getting caught up on words i guess.

OPENROWSET is not used to export...my example above is SELECTing from a spreadsheet via openrowset..i know you can update and delete that way...but what do you mean by export?

you originally said bcp was not working, but now the questions more about drivers and openrowset.

can you take the time and explainw hat you are trying to do, what is not working,a nd what you've tried so far?

i'm not at all sure what the issue is anymore.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mihir.nasikkar SSC-Addicted Points: 459 More actions · Answer 6

I basically want to export the output of the above query into an excel spreadsheet.

For me , Bcp is not working.

OPENROWSET did work , which actually transferred (exported) the output of the query to result set for 32-bit.

I just want to know is there any other way to do this, without updating 64 bit ACE drivers for Office 2007+

Thanks for your help, really appreciate it

- Mihir