July 31, 2015 at 8:56 am
I need a little assistance. I have the following code below where I need to have all of the query results output into a .csv file to use in a VBA macro. The issue I am running into is that the data is not deliminating correctly and my rows are being shifted incorrectly. Would anyone happen to know what may be wrong with the code below or maybe a better way of out putting the results into a .csv file with a common delimiter.
-- Declare the variables
DECLARE @CMD VARCHAR(4000),
@DelCMD VARCHAR(4000),
@HEADERCMD VARCHAR(4000),
@Combine VARCHAR(4000),
@Path VARCHAR(4000),
@COLUMNS VARCHAR(4000)
-- Set values as appropriate
SET @COLUMNS = ''
SET @Path = '\\172.17.10.23\global$\_test_jonathan'
-- Set up the external commands and queries we'll use through xp_cmdshell
-- Note that they won't execute until we populate the temp tables they refer to
SET @CMD = 'bcp "select * from ##OutputTable1" queryout "' + @Path + '\Temp_RawData.csv" -S ' + @@SERVERNAME + ' -T -t "^" -c'
SET @HEADERCMD = 'bcp "SELECT * from ##cols" queryout "' + @Path + '\Temp_Headers.csv" -S ' + @@SERVERNAME + ' -T -t , -c'
SET @Combine = 'copy "' + @Path + '\Temp_Headers.csv" + "' + @Path + '\Temp_RawData.csv" "' + @Path + '\MyCombinedFile.csv"'
SET @DelCMD = 'del "' + @Path + '\Temp_*.csv"'
-- Create and populate our temp table with the query results
-- Modification of Original Report Run
select * INTO ##OutputTable1 from tho_supr_itm_oo_vw
-- end of Modification of Original Report Run
-- Generate a list of columns
SELECT @COLUMNS = @COLUMNS + c.name + '^'
FROM tempdb.sys.columns c
WHERE
c.object_id = OBJECT_ID('tempdb..##OutputTable1')
ORDER BY c.column_id
SELECT @COLUMNS as Cols INTO ##Cols
-- Run the two export queries - first for the header, then for the data
exec xp_cmdshell @HEADERCMD
exec xp_cmdshell @CMD
-- Combine the two files into a single file
exec xp_cmdshell @Combine
-- Clean up the two temp files we created
exec xp_cmdshell @DelCMD
-- Clean up our temp tables
drop table ##cols
IF OBJECT_ID('tempdb..##OutputTable1') IS NOT NULL
drop table ##OutputTable1
Go
output from querry (please post in a text editor. The line starting with (only ) should be on line 1 after 20 pks and is shifted to a new line.):
557898^1^9885E25^80082^9.0 CM GLASS FIBER PADS 20PKS
(only 12 pks in stock that will ship today)^12.00000000^.00000000^18.32000000^219.84000000000^28.30000000^339.60000000000^9.98000000^35.2650176678445^DR9146322^0^Allow BackOrder^Invoice^New^VN000261^VAN NOTE ENT INC.^FISHER SCIENTIFIC COMPANY^4509331149^55205500^237^OWENS CORNING^29801-8124^2015-07-22 18:13:50.293^2015-07-22 00:00:00.000^2015-07-22 18:13:50.537^032^DD^Supplies^BALANCES^7.00000000^TS^ORIGIN^3^.00000000^1^ ^-24.00000000^.00000000^24.00000000^-24.00000000^.00000000^ - ^fishd^.00000000^Manual^PK^1.00000000^40.00000000^ ^7602335797^ ^
August 3, 2015 at 6:41 am
I don't see any obvious reason why you couldn't just do this in an SSIS package instead. SSIS can avoid the extra complexity of having to combine your header row with your data rows. You can use any delimiter you want. If you are using XP_CMDShell to do this, you can avoid that just by using SSIS instead. What's the reason behind using just T-SQL to do this?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply