Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Thursday, May 8, 2014 8:48 AM
Points: 10, Visits: 55
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 @ 3:40 PM
Points: 12,897, Visits: 32,105
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1534632
Posted Friday, January 24, 2014 2:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 8, 2014 8:48 AM
Points: 10, Visits: 55
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