November 2, 2004 at 11:14 pm
Hi All,
What is the simplest way to export the results of a query to individual text files, one file per row returned in the query?
November 4, 2004 at 8:08 am
just a thought...
create a stored procedure with a cursor that spins through each record
for each record, you could dymically build a bcp statement and utilize the xp_cmdshell function
thus, a file per record
give it a shot
Jeff Buckley
November 4, 2004 at 10:34 am
The code below (ActiveX Script) shows how to create a text file and write to it using FSO. Simply add some ADO code to execute the query and generate a recordset. Then loop through the recordset creating the text file for each record.
Option Explicit
Function Main()
'Declare variables
Dim objFSO
Dim objTextFile
'Create object
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Opening a file for writing
Set objTextFile = objFSO.CreateTextFile("yourPath\yourFileName" & _
DTSGlobalVariables("yourNewFileNum").Value & ".txt", true, false)
'Writing to the file
objTextFile.Write DTSGlobalVariables("yourQueryValues").Value
'Closing File
objTextFile.Close()
Main = DTSTaskExecResult_Success
End Function
November 4, 2004 at 2:39 pm
Hi All,
Thanks for the replies and suggestions. I was working down the cursor / bcp route myself. It works a charm - here's a summary FYI.
DECLARE @id int,
@bcpJob varchar(2000),
@FileName varchar(2000)
DECLARE the_cursor CURSOR
FOR
SELECT id FROM table WHERE data_long IS NOT NULL
OPEN the_cursor
FETCH NEXT FROM the_cursor
INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FileName = REPLACE('C:\' + CONVERT(varchar(20),@id,1) + '.txt','/','-')
SET @bcpJob = 'bcp "select data_long from database..table where id = "' + CONVERT(varchar(20),@id,1) + ' queryout "' + @FileName + '" -c'
EXEC master..xp_cmdshell @bcpJob
FETCH NEXT FROM the_cursor
INTO @id
END
CLOSE the_cursor
DEALLOCATE the_cursor
November 8, 2004 at 7:15 pm
nice!
Jeff Buckley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply