Post reply

Each Row Into a TEXT file

  • 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?

  • 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!

  • 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 :/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply