command to save to a csv file?
-
Is there a way in T-SQL that I can save the results of a query to a CSV file, instead of having to do it manually?
-
In Query Analyzer you can choose "Results to File.." and it will prompt you for a filename.
To perform the same function using OSQL, use the -o option.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
and you can also say how you want your columns delimited (,|tab etc)
-
If you are thinking of running this script from a job, or within a stored procedure then you might want to think about bcp. Check out BOL for exact syntax.
-
I am thinking of doing this within a stored procedure called from a Cold Fusion application. I'll take a look at BCP in BOL.
Thanks
-
There is another approach you can use, but you must be cautious. You can use OLE Automation to write output directly to a file, if you want.
I've included a simple script below that creates a file on the server's C:\ drive, listing the ID and Name of all the databases on your server. This is nothing fancy, but it should give you an idea of what you could do.
Enjoy.
Pete
========
DECLARE @RET VARCHAR(8000)
DECLARE @ID VARCHAR(6), @NAME VARCHAR(50)
DECLARE @FSOBJ INT, @TSOBJ INT, @HR INT
EXEC @HR = sp_OACreate 'Scripting.FileSystemObject', @FSOBJ OUT
IF @HR <> 0
BEGIN
PRINT @HR
PRINT 1
EXEC xsp_displayoaerror @FSOBJ, @HR
RETURN
END
EXEC @HR = sp_OAMethod @FSOBJ, 'CreateTextFile', @TSOBJ OUT, 'c:\outputfile.csv', 1, 0
IF @HR <> 0
BEGIN
PRINT @HR
PRINT 2
EXEC xsp_displayoaerror @FSOBJ, @HR
RETURN
END
SELECT @RET = 'ID, NAME'
EXEC @HR = sp_OAMethod @TSOBJ, 'WriteLine', null, @RET
IF @HR <> 0
BEGIN
PRINT @HR
PRINT 3
EXEC xsp_displayoaerror @FSOBJ, @HR
RETURN
END
DECLARE CUR CURSOR FOR
SELECT convert(varchar(10),dbid), name from master..sysdatabases
OPEN CUR
FETCH NEXT FROM CUR INTO @ID, @NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @RET = @id + CHAR(44) + @name
EXEC @HR = sp_OAMethod @TSOBJ, 'WriteLine', null, @RET
IF @HR <> 0
BEGIN
PRINT 3
EXEC xsp_displayoaerror @FSOBJ, @HR
RETURN
END
FETCH NEXT FROM CUR INTO @ID, @NAME
END
CLOSE CUR
DEALLOCATE CUR
EXEC @HR = sp_OAMethod @TSOBJ, 'Close'
IF @HR <> 0
BEGIN
PRINT 3
EXEC xsp_displayoaerror @FSOBJ, @HR
RETURN
END
EXEC @HR = sp_OADestroy @TSOBJ
IF @HR <> 0
BEGIN
PRINT 4
EXEC xsp_displayoaerror @FSOBJ, @HR
RETURN
END
EXEC @HR = sp_OADestroy @FSOBJ
IF @HR <> 0
BEGIN
PRINT 5
EXEC xsp_displayoaerror @FSOBJ, @HR
RETURN
END
===========
-
Thanks for the script. I need all the help I can get. You mentioned being cautious with this. Could you expand on that? What should I look out for with it?
-
Thanks powilliams for the SQL code example. It was just what I needed. I only had one issue: stored procedure xsp_displayoaerror could not be found. I still need to add some error handling code but here is what I have and it works quite well.
What are the parameters on the 'CreateTextFile' OA method?
--Procedure to export a batch of order records to a flat file for
-- EDI upload to Varnet. Designed in cooperation with Nisha Patel
-- and EDI Development.
--Written by Brent Coats
--Creation date 6/7/2006
--Revision history
-- 1.0 Initial release
DECLARE
--DATA
@ponumber nchar(22),
@contact nchar(30),
@shippingText nchar(80),
@addr1 nchar(30),
@addr2 nchar(30),
@city nchar(30),
@state nchar(2),
@zip nchar(10),
@country nchar(12),
@linenbr nchar(8),
@qtyord nchar(8),
@itemno nchar(18),
@desc nchar(80),
@totqty nchar(6),
@totlines nchar(6),
@customerID int,
@CustID int,
@OrderID int,
@Exported bit,
--CALCULATED
@intLine int,
@intTotalQty int,
--RESULTS
@strPOH nchar(321),
@strPON nchar(321),
@strPOS nchar(321),
@strPOL nchar(321),
@strPOE nchar(321)
DECLARE @FSOBJ INT, @TSOBJ INT, @HR INT
EXEC @HR = sp_OACreate 'Scripting.FileSystemObject', @FSOBJ OUT
EXEC @HR = sp_OAMethod @FSOBJ, 'CreateTextFile', @TSOBJ OUT,
'D:\TechServOrders\po14901.dat', 1, 0
--Get Customer list with orders to be exported:
DECLARE CUST CURSOR FOR SELECT * FROM OrdersToExport
OPEN CUST
FETCH NEXT FROM CUST INTO @ponumber,@contact,@addr1,@addr2,@city,@state,@zip,
@country,@customerID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC @HR = GetEDIPOHrecord @ponumber,@contact,@strPOH OUT
EXEC @HR = sp_OAMethod @TSOBJ, 'WriteLine', null, @strPOH
EXEC @HR = GetEDIPOSrecord @ponumber,@contact,@addr1,' ',
@city,@state,@zip,@country,@strPOS OUT
EXEC @HR = sp_OAMethod @TSOBJ, 'WriteLine', null, @strPOS
--Get order lines for current customerID and export:
SELECT @intLine = 1
SELECT @intTotalQty = 0
DECLARE LIN CURSOR FOR SELECT * FROM OrderLinesToExport
WHERE CustID = @CustomerID
OPEN LIN
FETCH NEXT FROM LIN INTO @qtyord,@itemno,@desc,@shippingText,@CustID,
@OrderID,@Exported
WHILE @@FETCH_STATUS = 0
BEGIN
IF @intLine = 1 AND (NOT @ShippingText = NULL)--Only write PON record once per order
BEGIN
EXEC @HR = GetEDIPONrecord @ponumber,@shippingText,
@strPON OUT
EXEC @HR = sp_OAMethod @TSOBJ, 'WriteLine', null, @strPON
END
SELECT @linenbr = @intLine
EXEC @HR = GetEDIPOLrecord @ponumber,@linenbr,@qtyord,
@itemno,' ',' ',@desc,@strPOL OUT
EXEC @HR = sp_OAMethod @TSOBJ, 'WriteLine', null, @strPOL
SELECT @intLine = @intLine + 1
SELECT @intTotalQty = @intTotalQty + @qtyord
UPDATE OrderLinesToExport SET Exported = 1 WHERE CURRENT OF LIN
FETCH NEXT FROM LIN INTO @qtyord,@itemno,@desc,@shippingText,@CustID,
@OrderID,@Exported
END
CLOSE LIN
DEALLOCATE LIN
SELECT @totlines = @intLine - 1
SELECT @totqty = @intTotalQty
EXEC @HR = GetEDIPOErecord @ponumber,@totqty,@totlines,@strPOE OUT
EXEC @HR = sp_OAMethod @TSOBJ, 'WriteLine', null, @strPOE
FETCH NEXT FROM CUST INTO @ponumber,@contact,@addr1,@addr2,@city,@state,@zip,
@country,@customerID
END
CLOSE CUST
DEALLOCATE CUST
EXEC @HR = sp_OAMethod @TSOBJ, 'Close'
EXEC @HR = sp_OADestroy @TSOBJ
EXEC @HR = sp_OADestroy @FSOBJ
RETURN
Kindest Regards,Brent
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply