bcp export to CSV - 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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

bcp export to CSV

Author

Topic

OnMyOwn
Starting Member

2 Posts

Posted - 2005-03-17 : 11:51:08

How can I export a SQL Server table to a CSV file and have each field in my query go to individual cells in the CSV file? When it goes to the CSV file, the entire query is stuffed into cell A1 without any visible separation between what should be different fields.

I am exporting file on a server with the following stored procedure:

DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

SET @FileName = REPLACE('H:\DailyData\Jobs\eMax\ERC\ERC_appts_' +CONVERT(varchar(10),GETDATE(),1)+'.csv','/','-')

SET @bcpCommand = 'bcp "SELECT ControlNum, ClientID, CandidateID, CallAttempts, LastCallDate, CallDispositionID, CallDispositionDesc, LastName, FirstName, Address1, Address2, City, State, ZIP, Country, BestPhone, Phone, HomePhone, WorkPhone, MobilePhone, Email, left(ApptDate,11) as ApptDate, right(ApptTime,7) as ApptTime, Q1, Q2, Q3, Q4, Q5, Q6, Q7, Comments FROM [ERC3963_db].[dbo].[workSpace]" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P "" -c'

print @bcpCommand

EXECUTE master..xp_cmdshell @bcpCommand

Please help!!!

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-17 : 11:57:02

YOu need to specify a row terminator and a row ending delimitor...

Look these up in Books online

Brett

8-)

OnMyOwn
Starting Member

2 Posts

Posted - 2005-03-18 : 12:08:39

Thanks for the tip. I knew it would be something simple! I got the script from the Microsoft knowledge base in the first place. I wish they had mentioned that!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources