locked
Export to Excel RRS feed

  • Question

  •  

    I am having a problem exporting data to excel using t-sql. I need to create a t-sql stored procedure that exports the resultset of a dynamic query to Excel where the columns are not know beforehand.

     

    I have been able to do it using bcp, but the problem is that the bcp method just exports the data to an tab-delimited ascii file with a .xls extension. Excel is smart enough to pick it up and parse it out into the individual cells, but the problem arises if the field value contains any special chars that may have been picked up when the user input the data by using copy & paste.  Then the columns/rows are skewed for the record that has the field with the special chars in its field value.

     

    I have tried using the openrowset method and if you know the fields(cols) that you want to retrieve, it works great. All you have to do is pre-create an excel file with the first row containing the columnNames for each column. But for dynamic queries, this is not an option. I have read somewhere that you can do a range, but have not been able to figure out to do that yet. You would have to pre-determine the number of columns & rows and figure out the range. Columns A-Z may not be as big an issue, but then I would have to figure out the values for any columns if the column count exceeded 26.

     

    Outside of using Ole-Automation, is there any way than I can achieve the results that I really need? It just seems like over-kill for something that should be a simple task.  If it cannot be done outside of Ole-Automation, is there a good example that someone can point me to?

     


    Thanks in advance for any help!

    glh8564

    Friday, November 2, 2007 2:37 PM

All replies

  • You best choice is to use SSIS (SS2005+) or DTS (SS2000).

     

    If that is not an option, then have you tried to use OpenRowSet via dynamic SQL?

     

     

    Friday, November 2, 2007 2:53 PM
  • Hi Dale,

     

    If you will look at my 3rd paragraph, it describes the issues I have encountered so far trying that method. If it is possible to utilize ssis/dts from a stored proc, that would work too. As of yet, I have not been able to figure that one out yet. I am running sql server 2005.

     

    Thanks for your response. Smile

     

    Friday, November 2, 2007 3:08 PM
  • You would invoke SSIS outside of SS, and it in turn would execute the query/SP to retrieve the needed data.

     

    However, I imagine you could invoke it the other way using master..XP_CMDSHELL.  Though I wouldn't recommend that.

     

     

    Friday, November 2, 2007 3:27 PM
  • Hi Dale, I don't think that will work with what I need it to do.

     

    For a test using OpenRowSet, I just tried exporting 2 columns in 1 row using the following statement:

    insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=D:\mssql\excel\ExcelTemplate.xls;',
    'SELECT * FROM [Sheet1$A1:B1]') select top 1 fld1, fld2 from table1

    I got the follow error:

    "Insert Error: Column name or number of supplied values does not match table definition."

     

    Do you know how to get around this?

     

    On a side-note: If you have any say-so with Microsoft, can you ask them to include in the next service pack for sql server 2005 a clean export funtion usable in t-sql? If they are unable to do that, then maybe for the next sql server version. This really is a head-ache!

    Friday, November 2, 2007 3:37 PM
  • glh


    Here's what I did.

    I created a spreadsheet named template.xls, in that spreadsheet I put a value in the first cell of each column (i went to like KK, but go as wide as you need).

     

    Then I ran the following code.

     

    BTW, I still think SSIS is your best approach.

    If have to "initiate" from the SP, then have the SP update a queue table that the package monitors and then runs the request.

     

     

    Code Block

     

     

    create table #excelcolumns (cnt int, colname varchar(4))

    insert into #excelcolumns (cnt, colname)

    select 1, 'A'

    union all select 2, 'B'

    union all select 3, 'C'

    union all select 4, 'D'

    union all select 5, 'E'

    union all select 6, 'F'

    union all select 7, 'G'

    union all select 8, 'H'

    union all select 9, 'I'

    union all select 10, 'J'

    union all select 11, 'K'

    union all select 12, 'l'

    union all select 13, 'M'

    union all select 14, 'N'

    union all select 15, 'O'

    union all select 16, 'P'

    union all select 17, 'Q'

    union all select 18, 'R'

    union all select 19, 'S'

    union all select 20, 'T'

    union all select 21, 'U'

    union all select 22, 'V'

    union all select 23, 'W'

    union all select 24, 'X'

    union all select 25, 'Y'

    union all select 26, 'Z'

    union all select 27, 'AA'

    union all select 28, 'AB'

    union all select 29, 'AC'

     

     

    select 1 as col1, 2 as col2, 3 as col3, 4 as col4, 5 as col5, 6 as col6, 7 as col7, 8 as col8, 9 as col9, 10 as col10,

    11 as col11, 12 as col12, 13 as col13, 14 as col14, 15 as col15, 16 as col16, 17 as col17, 18 as col18, 19 as col19, 20 as col20,

    21 as col21, 22 as col22, 23 as col23, 24 as col24, 25 as col25, 26 as col26, 27 as col27, 28 as col28 --, 29 as col29

    into #exceldata

     

    declare @str varchar(4000)

    select @str = 'insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',

    ''Excel 8.0;Database=C:\My\Excel\Book1.xls;'',

    ''SELECT * FROM [Sheet1$A1:'

    + ec.colname

    + convert(varchar, (select count(*) from #exceldata))

    + ']'') select * from #exceldata'

    from #excelcolumns ec

    where ec.cnt =

    (select count(*)

    from tempdb.information_schema.columns

    where table_catalog = 'tempdb'

    and table_name like '#exceldata%'

    )

     

    exec master..xp_cmdshell ' copy c:\my\excel\template.xls c:\my\excel\book1.xls'

     

    print @str

     

    exec (@str)

     

     

     

     

    Friday, November 2, 2007 4:42 PM
  • This article may offer some help:

     

    Export data to Excel
    http://www.mssqltips.com/tip.asp?tip1202

     

     

    Friday, November 2, 2007 5:03 PM
  • Hi Dale,

     

    That example pointed me in the right direction, but I still have some questions.  I created an excel file and for each cell in the first row, I just put a space. I still want to not have to know the column name in advance. I then ran your code and it worked fine. The only issues were that it did not add a column header and there was a blank line preceding the data. Apparently (and you probably already know this), the jet engine sees the cell values in the spreadsheet as null unless it contains some sort of a value even an empty string.

     

    I then ran my own version of it using the same process of creating the template file. I then extracted the column headers from the sql query, built them into a string in a select statement and did a union all on the original query which gave me a resultset with headers. I then inserted it into the file. This works fine... the data is exported with column headers, but the first row still contains the blank row. Do you know of a way to get around this?

     

    The SSIS solution might work, but I am still uncertain to what you mean. From your post..please correct me if I am wrong, my understanding is that I just have the Stored Proc create a record in a queue table which would be constantly monitored by a SSIS Package. The queue table would contain the query to be run and then it would process it and export the results to an Excel.  I have exported results out to an Excel file using the Import/Export wizard using a query instead of specifying a table and it has worked fine. I know you can save the package, but I do not know how to specify dynamically what the query for the package is to be. If you can point me to an article or provide me with an example, I would really appreciate it.

     

    Thank you very much for your help, you have been a big help.

     

    Friday, November 2, 2007 6:10 PM
  • Yea, it appears that the connector doesn't see the excel cells unless something is there to begin with.

     

    Removing the dummy row:  The only way I know of is to put something in there that you can use to execute a DELETE.  Either put -9999 in the first cell in the template and add an identity column as the first column in your query; or maybe hard code some values that will never exist in the data into the first few cells, like 'SPECIAL ROW' 'EXCEL' 'ROW' 'TO' 'DELETE'.

     

     

    SSIS.  I'm about to head out, let me get back to you on this.

     

    Friday, November 2, 2007 6:46 PM
  • Hi Arnie,

     

    Thank you very much for your response and the link. I had actually already seen that site. It was of some help, but some of the issues I was running across was not covered there.

     

    glh8564

    Friday, November 2, 2007 7:49 PM
  • Dear Sir 

    you will get your answer here

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=106743

    Regards

    Mona

    • Proposed as answer by mnasr Tuesday, June 28, 2011 8:59 PM
    Tuesday, June 28, 2011 8:59 PM
  • Maybe call bcp with xp_cmdshell in the sp?
    • Proposed as answer by mnasr Tuesday, June 28, 2011 9:02 PM
    Tuesday, June 28, 2011 9:02 PM