Export File To Text - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Export File To Text

Author

Topic

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-19 : 11:24:42

Can some tell me how to export a select to a text file with no commas or qoutes? Thank You

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-19 : 11:42:37

The easiest way to export data is to use the BCP utility. The code below was written in SQL 2005.

DECLARE @cmd VARCHAR(2048)
SET @cmd = 'bcp '
+ ' "SELECT Manufacturer,ManufacturerPartNumber'
+ ' FROM MyDatabase.dbo.MyTable Order by Manufacturer,ManufacturerPartNumber" queryout'
+ ' D:\Excel_Exports\myfile.csv -c -t\, -r \n -T'

EXEC master..xp_cmdshell @cmd

hope that helps !

Also I encourage you to look up BCP (google is your friend!). That way you can understand the switches that I used after the file name.

r&r

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-19 : 12:12:51

Thanks revdnrdy

I tried this and got an error.

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'DLT683'.

DECLARE @cmd VARCHAR(2048)
SET @cmd = 'bcp '
+ ' "'DLT683',s.socscnum,sum(d.uprtrxam)'
+ ' FROM upr30300 as d left join upr00100 as s on d.employid = s.employid group by s.socscnum," queryout'
+ ' C:\Test.csv -c -t\, -r \n -T'
EXEC master..xp_cmdshell @cmd

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-19 : 12:31:06

Yours:
+ ' "'DLT683',s.socscnum,sum(d.uprtrxam)'

Mine
+ ' "SELECT Manufacturer,ManufacturerPartNumber'

See the difference yet? ; )

You are missing a select statement and I think you also have additional quote marks. You don't need them around DLT683 as that is already encapsulated by the string

r&r

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources