Post reply

Export to a text file

  • October 14, 2003 at 8:04 am

    #82441

    I'm sure this has been covered before, but does anyone have a stored procedure for exporting SQL text fields into a text file.

  • October 14, 2003 at 8:16 am

    #478149

    Would bcp work for you? You can use a query string with bcp to get just the information you desire.

    Guarddata-

  • October 14, 2003 at 8:32 am

    #478155

    I would rather do it via a stored procedure.

  • October 14, 2003 at 3:30 pm

    #478221

    I don't know how directly - unless you have the stored procedure call xp_cmdShell with a bcp argument.

    Guarddata-

  • October 15, 2003 at 5:30 am

    #478269

    I have ussed the following. You do realize that DTS makes this very easy.

    -- To BCP the table out to a text file

    DECLARE @sql varchar(255)

    BEGIN

    SELECT @sql = "master.dbo.xp_cmdshell " + '"bcp dbName.dbo.tableName out F:\BackUp\ExportTableName.txt -c -yourWORKSTATION -Usa -P"'

    EXEC (@SQL)

    END

  • October 15, 2003 at 5:43 am

    #478270

    Slight variation using a query and tab as field separator:

    DECLARE @cmd varchar(1000)

    , @sql varchar(1000)

    SET @sql = 'SELECT Field1, Field2'

    SET @sql = @sql + ' FROM dbo.table'

    SET @cmd = 'bcp "' + @sql + '" queryout \\server\docs\test.csv -c -t, -T'

    EXEC master..xp_cmdshell @cmd, no_output

  • October 15, 2003 at 7:07 am

    #478283

    How can this method be used to initialize an excel worksheet rather than using Excel's menu option Get External Data?

    Thanks

  • October 15, 2003 at 7:21 am

    #478287

    Here's a snippet of some code from a stored proc that extracts contact data to a file. I first insert and format my output within a staging table.

    CREATE PROCEDURE p_extract (@file_nm varchar(255)) AS

    BEGIN

    DECLARE @cmd varchar(255)

    -- e.g., @file_nm = 'C:\output.txt'

    ...

    -- prepare the extract to file statement

    SELECT @cmd = 'osql -Sservername -Uloginname -P -Q' + CHAR(34) + 'SELECT line_output_txt FROM t_extract' + CHAR(34) + ' -ddatabasename -s' + CHAR(34) + CHAR(34) + ' -w3000' + ' -o' + @file_nm

    -- extract the records to the specified file

    exec master..xp_cmdshell @cmd

    END

  • October 15, 2003 at 11:38 am

    #478346

    Gary,

    If you want to create/get data from Excel use OPENDATASOURCE. It is by far the easier way to go. The only problems I have had is that I have to isolate the distributed transactions I have going across multiple servers from the ones I do going against the OPENDATASOURCE query. 

    

    INSERT INTO #TempTable(<FieldList>)

    SELECT <FieldList>

    FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0'

        ,'Data Source="<FilePath>.xls";User ID=Admin;Password=;Extended properties=Excel 8.0'

        )...Sheet1$

    Gary Johnson

    Microsoft Natural Language Group

    DBA, Sr. DB Engineer




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • October 16, 2003 at 6:53 am

    #478443

    Gary,

    Looks like you are opening a text file. I think Guarddata was looking for outputting to a file. Can OpenDataSource be used for outputting?

  • October 16, 2003 at 7:40 pm

    #478579

    lambje,

    What "Gary" are you refering to? 🙂

    I was responding to Gary Billins question on Excel files only. For text files I would use BCP as stated above. But for Excel I actually prefer to use OPENDATASOURCE as I never have to shell out to do it. The problem with OPENDATASOURCE against Excel is that you must use an XLS file. Unfortunately OPENDATASOURCE doesn't support writing directly to a text file (at least as far as I have found in my use of it).

    Gary Johnson

    Microsoft Natural Language Group

    DBA, Sr. DB Engineer




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • October 17, 2003 at 7:33 am

    #478631

    Yes, you are the Gary I was referring to. I apologize as I didn't follow the whole flow. I did learn something new though. I'll put OpenDataSource in my toolkit as I seem to be doing more file-related activities these days.

    Thanks, Jeff

Viewing 12 posts - 1 through 11 (of 11 total)

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