Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Each Row Into a TEXT file Expand / Collapse
Author
Message
Posted Friday, January 24, 2014 12:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 8, 2016 2:14 PM
Points: 19, Visits: 114
Hello,

I searched online everywhere and could not find the answer to this.

I have an OLE DB Source with a query that outputs 1000-8000 rows. I need each individual row to be outputted into a .txt file.

Is this even possible?
Post #1534627
Posted Friday, January 24, 2014 1:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 14,184, Visits: 37,066
if this is a one time report/export, you can select the data in ssms and chose results to text instead of to grid.

otherwise,
bcp.exe or sqlcmd, either using the -o parameter, are designed for exactly that: outputting data to a file, delimiting columns by a specified character (comma, tab,space, yourchoice)and rows by a specified character(s) (CrLf =\n, for example )

you have to be careful if your exported data contains CrLf, of course, but that's how we do it:

dump your oledb source into a global temp table, for example, and then bcp it out.
--only global temp tables can be exported, so move a results of a complex query into a global, then export
Select PatientDisplayID as 'Client ID', ISNULL(SugarEntryCount,0) as 'BG Count',
ISNULL(CarbsEntryCount,0) as 'Carb Count', ISNULL(MedsEntryCount,0) as 'Med Count'
INTO ##PIPE_REPORT
FROM #temp_patients

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM ##PIPE_REPORT " queryout C:\Data\Objects.txt -t"|" -c -T '
DROP TABLE #temp_patients
DROP TABLE ##PIPE_REPORT



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1534632
Posted Friday, January 24, 2014 2:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 8, 2016 2:14 PM
Points: 19, Visits: 114
Is it possible to do this without using xp_cmdshell?
My dba's will not allow it :/
Post #1534644
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse