Post reply

File Formatting exporting to Excel using BCP Command

  • August 28, 2015 at 7:57 am

    #320144

    Declare@QRYvarchar(8000)

    Select@QRY='bcp "Select COL1, COl2 From table(nolock)" queryout "D:\test.xls" -c -T -S ' + convert(varchar(20), serverproperty('servername'))

    Select@QRY

    Execmaster..xp_cmdshell@qry

    The file test.xls is getting generated but when opening getting the message

    "File you are trying to open is in a different format than specified by the file extension"

    Is there any property that can be set to avoid this message?

  • August 28, 2015 at 8:15 am

    #1823052

    SQL's bcp does not create native xls files.

    it only creates flat files with the delimiters you specify(tab delimited/comma delimited/custom delimited.) so your file is a text file with the wrong extension, which is why you get the error.

    typically you want to create csv files, and let people open them in excel, which is most peoples default application for that extension.

    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!

  • August 28, 2015 at 8:22 am

    #1823056

    Is there any other command available to generate Excel file without throwing that error message?

  • August 28, 2015 at 8:27 am

    #1823058

    nothing within SQL. SQL doesn't do files, technically.

    SSIS or SSRS subscriptions can export data to native excel format.

    otherwise, not really.. you have to use something external, like a CLR, Excel automation via sp_OaCreate, or third party dll or application.

    if you have an existing template excel file, you could use a linked server to connect to it and insert/update/delete rows in that file.

    if the file headers are dynamic, i'm not sure what you would do.

    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!

  • August 31, 2015 at 8:01 am

    #1823367

    Hi, I think as suggested, using SSRS would be your best bet.

    What you don't know won't hurt you but what you know will make you plan to know better

  • August 31, 2015 at 8:38 am

    #1823374

    As mentioned already the closest you'll get using BCP is csv file format. But using another language with the appropriate plugins its not that difficult such as powershell, python, vb etc etc.

    one example in python:

    http://stackoverflow.com/questions/18977387/how-to-export-sql-server-result-to-excel-in-python

    MCITP SQL 2005, MCSA SQL 2012

  • September 2, 2015 at 1:00 am

    #1823873

    Ok thanks Taylor. Will check and revert.

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

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