Post reply

using sqlcmd output to text file

  • January 21, 2016 at 1:37 am

    #323771

    Hi, i have explore using sqlcmd to run a script and output to a text files.

    command used:

    sqlcmd -S hostname\instance_name -i myScript.sql -o myScript.txt -Y 30

    my question is on the parameter -Y used to display my result output column.

    The -Y parameter make my headers to display in fixed length column.

    There seem to be no way to set it as auto size or more dynamic as in, my concern is the result value per column can be longer then i set using the -Y parameter.

  • January 21, 2016 at 3:12 am

    #1853394

    Have you tried the parameters listed in the samples on this blog:

    Export Data AS CSV from Database Using SQLCMD[/url]

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • July 31, 2019 at 1:56 pm

    #3667555

    hola matraz

    pudiste solucionar tu tema?

    tengo un caso parecido, el tema es que cuando exporta a un excel, me sale solo en una linea, debe salir en varias columnas.

    C:\PruebaXLS>sqlcmd -S GCTEST -d PracticeDB -E -Q "SELECT id,Nombre,Apellido,Dieccion,Distrito from EMPLEADO" -o result.xls

    Attachments:
    You must be logged in to view attached files.

  • July 31, 2019 at 2:12 pm

    #3667560

    it's better to post questions in English to avoid issues with translation

    regarding your case:    it's better to use BCP command because you can specify field delimiters (-t",") and get CSV file which should be opened by Excel properly

    example:

    example:
    bcp "SELECT id,Nombre,Apellido,Dieccion,Distrito from PracticeDB..EMPLEADO" queryout result.csv -SGCTEST -T -t"," -c

     

  • August 1, 2019 at 10:38 pm

    #3668072

    Gracias Andrey,

    Utilice el siguiente comando:

    C:\>bcp "SELECT id,Nombre,Apellido,Direccion,Distrito FROM practiceDB.DBO.EMPLEADO" queryout C:\PruebaXLS\empleado.xls -T -c

    pero no sale el encabezado en el excel -_-

    prueba2

     

  • August 2, 2019 at 8:42 am

    #3668160

    Thanks for ignoring my hint about English. I will follow the same path next time.

     

    Regarding the column names:   there's no quick and easy way to get both data & header in desired delimitted form ready for XLS.

    Try this one (run as BAT file):

    SQL2017+  (usage of STRING_AGG function)

    @echo off 
    c:
    cd \PruebaXLSset query=SELECT id,Nombre,Apellido,Direccion,Distrito FROM practiceDB.DBO.EMPLEADO
    set delimitter=,

    @bcp "select string_agg(name,'%delimitter%') from sys.dm_exec_describe_first_result_set('%query%', null, null)" queryout header.txt -S . -T -c
    @bcp "%query%" queryout body.txt -S . -T -c -t"%delimitter%"
    @copy header.txt + body.txt empleado.csv

    Previous versions (XML PATH('')):

    @echo off 
    c:
    cd \PruebaXLSset query=SELECT id,Nombre,Apellido,Direccion,Distrito FROM practiceDB.DBO.EMPLEADO
    set delimitter=,

    @bcp "select distinct replace(stuff((select '%delimitter%'+name as 'data()' from sys.dm_exec_describe_first_result_set('%query%', null, null) for xml path('')),1,1,''),char(32),'') from sys.dm_exec_describe_first_result_set('%query%', null, null)" queryout header.txt -S . -T -c
    @bcp "%query%" queryout body.txt -S . -T -c -t"%delimitter%"
    @copy header.txt + body.txt empleado.csv

     

    • This reply was modified 5 years, 4 months ago by  Andrey. Reason: added version for SQL2017- with XML PATH('')

  • August 2, 2019 at 5:17 pm

    #3668327

    Thank you very much for the help Andrey !!

    prueba3

     

Viewing 7 posts - 1 through 6 (of 6 total)

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