Each Row Into a TEXT file

• John215

  SSC Eights!

  Points: 871

  More actions

  January 24, 2014 at 12:57 pm

  #282729

  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?

• Lowell

  SSC Guru

  Points: 323522

  More actions

  January 24, 2014 at 1:23 pm

  #1683250

  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!

• John215

  SSC Eights!

  Points: 871

  More actions

  January 24, 2014 at 2:09 pm

  #1683259

  Is it possible to do this without using xp_cmdshell?

  My dba's will not allow it :/