Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2011
    Posts
    9

    Unanswered: Outputting data to csv file

    Hi
    I am looking to write a sql command to export selected data to a cvs file on the local c drive.
    I have tried using the following
    select * from dmr_articledetails where article like '%_x%' and createdate ='2011-01-12'

    i am getting the following error when i execute this command
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'c:\tmp\test.csv'.

    Can anyone help me with this?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This should help
    Code:
    CREATE PROCEDURE dbo.P_CalcResult2File (
    	@CalcResultId BigInt
    	)
    -- Write a CalcResult to the file
    --	D:\PRD\Data\CalcResult\#IndNormalTest##CalcResultId#_#YearFrom#_YYYYMMDD_HHMMSS.txt
    --	with #IndNormaalTest# : 'T'est or 'N'ormal
    --		#CalcResultId# het CalcResultId
    --		#YearFrom# StartYear
    AS
    BEGIN
    	SET NOCOUNT ON
    
    	DECLARE @FileName varchar(250)		-- file name
    	DECLARE @YearFromStr CHAR(4)		-- startyear as string
    	DECLARE @IndNormalTest	CHAR(1)		-- 'T'est or 'N'ormal 
    	DECLARE @DtExtractionEnd DATETIME	-- timestamp end of extraction
    	DECLARE	@SQLStr varchar(500)		-- SELECT SQL
    	DECLARE @bcpCommand varchar(2000)	-- the BCP command
    	DECLARE @CalcResultIdStr VARCHAR(12)	-- @CalcResultId converted to a string
    
    	SElECT  @CalcResultIdStr = RIGHT('0000' + CONVERT(VARCHAR(12), @CalcResultId), 4) 
    
    	SELECT	@YearFromStr = CONVERT(CHAR(4), YearFrom),
    			@IndNormalTest = IndNormalTest, 
    			@DtExtractionEnd = DtExtractionEnd
    	FROM CalcResult 
    	WHERE CalcResult.id = @CalcResultId
    
    	SET @FileName = 'D:\PRD\Data\CalcResult\' + @IndNormalTest + @CalcResultIdStr + '_' + @YearFromStr + '_' + 
    						CONVERT(CHAR(8),@DtExtractionEnd,112) + '_' + RTRIM(REPLACE(CONVERT(VARCHAR(8), @DtExtractionEnd, 108) ,':', '')) + '.txt'
    --PRINT '@Filename = ' + @FileName
    	SET @SQLStr = 'select Bulkdata COLLATE SQL_Latin1_General_Pref_CP850_CI_AS as Bulkdata from **databaseName**.dbo.CalcResultRecord where CalcResultId = ' + @CalcResultIdStr + ' ORDER BY ID'
    --PRINT '@SQLStr = ' + @SQLStr
    	SET @bcpCommand = 'bcp "' + @SQLStr + '" queryout "' + @FileName + '" -T -c'
    --PRINT '@bcpCommand = ' + @bcpCommand
    
    	EXEC master..xp_cmdshell @bcpCommand
    
    	SET NOCOUNT OFF
    
    END	--  PROCEDURE dbo.P_CalcResult2File
    GO
    Don't forget to supply your own value for **databaseName**.

    The database server must be able to access the path to the file must.

    EXEC master..xp_cmdshell @bcpCommand
    must be enabled on the database sever.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2011
    Posts
    9
    Do i need to make it a procedure or can i run this off as a query?

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That's too much code, as I used to say...Why not just BCP? Or, if you like coding, - you can make a parameterized batch script. Or you can make a parameterized SQL Agent job...Using xp_cmdshell together with dynamic SQL was never a good idea.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jan 2011
    Posts
    9
    Thanks for the reply,Do you have an example of how i could use BCP? i have never used this before?

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    bcp "select * from dmr_articledetails where article like '%_x%' and createdate ='2011-01-12'" queryout c:\tmp\test.csv -Smyserver -T -c
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov View Post
    bcp "select * from dmr_articledetails where article like '%_x%' and createdate ='2011-01-12'" queryout c:\tmp\test.csv -Smyserver -T -c
    At least on my machine this generates a tab delimited file instead of a CSV.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jan 2011
    Posts
    9
    I have tried running the query bcp "select * from dmr_articledetails where article like '%_x%' and createdate ='2011-01-12'" queryout c:\tmp\test.csv -Smyserver -T -c in the sql query analyzer but i am getting the following error.

    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'queryout'.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Pat Phelan View Post
    At least on my machine this generates a tab delimited file instead of a CSV.
    -PatP
    Oh, just add "-t," at the end of the command.

    daveomally, you need to run this from command line.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •