Results 1 to 23 of 23

Thread: how to write in excel file of sql query result

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Aug 2019
    Posts
    27

    how to write in excel file of sql query result

    i need to write certain sql result in excel file

    like i run select * from products where name='xyz' when it fetch result it should write in excel file..

    how can do this

  2. #2
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,957

    Re: how to write in excel file of sql query result


  3. #3

    Thread Starter
    Junior Member
    Join Date
    Aug 2019
    Posts
    27

    Re: how to write in excel file of sql query result

    i have below stuff an i write each result in excel sheet . i have an issue to write in excel sheet of each sql result

    drop table #table_Northwind
    create table #table_Northwind
    (

    RowID int not null identity(1,1) primary key,
    column_name varchar(50)

    )

    insert into #table_Northwind (column_name)
    (SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    where TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='Northwind' )

    --select * from #table_Northwind
    declare @tbl_name nvarchar(50)
    declare @i int
    select @i = min(RowID) from #table_Northwind
    declare @max int
    select @max = max(RowID) from #table_Northwind


    while @i <= @max begin

    SELECT (select column_name from #table_Northwind where RowID=@i) 'TableName' , o.name, [Scehma]=schema_name(o.schema_id), o.type
    FROM sys.sql_modules m
    INNER JOIN sys.objects o
    ON o.object_id = m.object_id
    WHERE m.definition like +'%'+ (select column_name from #table_Northwind where RowID=@i) +'%'

    set @i = @i + 1
    end

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,130

    Re: how to write in excel file of sql query result

    How to you intend on creating the .XLSX file?

    I am assuming you are creating this type of EXCEL file and not the older .XLS type.

    I am also assuming you are not creating a .TDF file - which will also open in EXCEL. These are text files with TAB delimiters, very easy to make.

    Please confirm the type of EXCEL file you want to make and then tell us what code you can use - VB6? VB.Net?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Aug 2019
    Posts
    27

    Re: how to write in excel file of sql query result

    want to create from database command/stored procedure ?? and also i mentioned my problem above ,want to create .xls file and want some one to write few basic lines

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Aug 2019
    Posts
    27

    Re: how to write in excel file of sql query result

    like excel in msoffice

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,130

    Re: how to write in excel file of sql query result

    Ok - so you limit yourself when you say you want an older version .XLS format file.

    And I cannot imagine how a STORED PROCEDURE would interact with EXCEL.

    Now you can flip the issue and manually create an EXCEL file - and create in that spreadsheet a datasource and pull from a query. That can be saved as an older EXCEL 1997 .XLS file and given to another user. I've done this trick.

    Otherwise, I use VB.Net and the Open XML SDK to create spreadsheets. These are .XLSX excel files.

    https://docs.microsoft.com/en-us/off...l/spreadsheets

    I could share code to do that - but it's not going to go into a SPROC or run in the database.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Aug 2019
    Posts
    27

    Re: how to write in excel file of sql query result

    right click on export database

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Aug 2019
    Posts
    27

    Re: how to write in excel file of sql query result

    i want to exec below line in above while loop
    INSERT INTO OPENROWSET
    ('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=c:\Test.xls;','SELECT query')

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,130

    Re: how to write in excel file of sql query result

    Quote Originally Posted by erum_mirza View Post
    i want to exec below line in above while loop
    INSERT INTO OPENROWSET
    ('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=c:\Test.xls;','SELECT query')
    I've never even seen this type of OPENROWSET use to create XLS files. That was a link given to you by JDC2000 in the second post here in this thread.

    Code:
    while @i <= @max begin
    
        Set @tbl_name = (SELECT (select column_name from #table_Northwind where RowID=@i) 'TableName' , o.name, [Scehma]=schema_name(o.schema_id), o.type
              FROM sys.sql_modules m
              INNER JOIN sys.objects o
              ON o.object_id = m.object_id
              WHERE m.definition like +'%'+ (select column_name from #table_Northwind where RowID=@i) +'%')
    
         INSERT INTO OPENROWSET
              ('Microsoft.Jet.OLEDB.4.0',
              'Excel 8.0;Database=c:\Test' + Cast(@i as varchar(100)) + '.xls;','SELECT * From ' + @tbl_name ) 
    
    set @i = @i + 1
    end

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Aug 2019
    Posts
    27

    Re: how to write in excel file of sql query result

    drop table #table_Northwind
    create table #table_Northwind
    (

    RowID int not null identity(1,1) primary key,
    column_name varchar(50)

    )

    insert into #table_Northwind (column_name)
    (SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    where TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='Northwind' )
    declare @tbl_name nvarchar(50)
    declare @i int
    select @i = min(RowID) from #table_Northwind
    declare @max int
    select @max = max(RowID) from #table_Northwind


    while @i <= @max begin

    Set @tbl_name = (SELECT (select column_name from #table_Northwind where RowID=@i) 'TableName' , o.name, [Scehma]=schema_name(o.schema_id), o.type
    FROM sys.sql_modules m
    INNER JOIN sys.objects o
    ON o.object_id = m.object_id
    WHERE m.definition like +'%'+ (select column_name from #table_Northwind where RowID=@i) +'%')

    INSERT INTO OPENROWSET
    ('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=d:\book2' + Cast(@i as varchar(100)) + '.xls;','SELECT * From ' + @tbl_name )

    set @i = @i + 1
    end

    showing errorr

    Msg 102, Level 15, State 1, Line 32
    Incorrect syntax near '+'.

  12. #12
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,130

    Re: how to write in excel file of sql query result

    Where did you get this? And what is it supposed to do?

    Code:
    Set @tbl_name = (SELECT (select column_name from #table_Northwind where RowID=@i) 'TableName' , o.name, [Scehma]=schema_name(o.schema_id), o.type
    FROM sys.sql_modules m
    INNER JOIN sys.objects o
    ON o.object_id = m.object_id
    WHERE m.definition like +'%'+ (select column_name from #table_Northwind where RowID=@i) +'%')
    And please put things in [code] blocks!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  13. #13
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    4,863

    Re: how to write in excel file of sql query result

    You are still not giving much information.

    Which database are you talking about?
    Do you actually need to create it from a stored procedure or can you also do it with an additional program (written in VB.Net or VB6)?

    A sample:
    https://www.sqlservercentral.com/for...e#post-3736926

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Aug 2019
    Posts
    27

    Re: how to write in excel file of sql query result

    sql server 2008 R2, from stored prcoedure

  15. #15

    Thread Starter
    Junior Member
    Join Date
    Aug 2019
    Posts
    27

    Re: how to write in excel file of sql query result

    its my code

  16. #16
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,130

    Re: how to write in excel file of sql query result

    This should work

    Code:
    insert into #table_Northwind (column_name)
    (SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    where TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='Northwind' )
    declare @tbl_name nvarchar(50)
    declare @i int
    select @i = min(RowID) from #table_Northwind
    declare @max int
    select @max = max(RowID) from #table_Northwind
    Declare @sqlstr nvarchar(max)
    
    while @i <= @max begin
    
    Set @tbl_name = (SELECT (select column_name from #table_Northwind where RowID=@i) 'TableName'-- , o.name, [Scehma]=schema_name(o.schema_id), o.type
    FROM sys.sql_modules m
    INNER JOIN sys.objects o
    ON o.object_id = m.object_id
    WHERE m.definition like '%'+ (select column_name from #table_Northwind where RowID=@i) +'%')
    
    Set @SQLSTR='INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=d:\book2' + Cast(@i as varchar(100)) + '.xls;'',''SELECT * From ' + @tbl_name + ')'
    select @SQLSTR
    Exec(@SqlStr)
    
    set @i = @i + 1
    end
    Might be missing a ' or two in the SQL string - fix it first!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  17. #17

    Thread Starter
    Junior Member
    Join Date
    Aug 2019
    Posts
    27

    Re: how to write in excel file of sql query result

    i have below updated code
    Code:
    --drop table #table_Northwind
    create table #table_Northwind
    (
    
    RowID int not null identity(1,1) primary key,
    column_name varchar(50)
    
    )
    
    insert into #table_Northwind (column_name)
    (SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    where TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='Northwind' )
    declare @tbl_name nvarchar(50)
    declare @i int
    select @i = min(RowID) from #table_Northwind
    declare @max int
    select @max = max(RowID) from #table_Northwind
    Declare @sqlstr nvarchar(max)
    
    while @i <= @max begin
    
    Set @tbl_name = (SELECT (select column_name from #table_Northwind where RowID=@i)) --'TableName'
    
    --Set @tbl_name = (select column_name as TableName from #table_Northwind where RowID=@i)-- 'TableName'-- , o.name, [Scehma]=schema_name(o.schema_id), o.type
     SELECT o.name, (select column_name from #table_Northwind where RowID=@i) 'TableName' , o.name, [Scehma]=schema_name(o.schema_id), o.type
    FROM sys.sql_modules m
    INNER JOIN sys.objects o
    ON o.object_id = m.object_id
    WHERE m.definition like +'%'+ (select column_name from #table_Northwind where RowID=@i) +'%'
    Set @SQLSTR='INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=d:\book2' + Cast(@i as varchar(100)) + '.xls;'',''SELECT * From ' + @tbl_name + ''')'
    select @SQLSTR
    Exec(@SqlStr)
    set @i = @i + 1
    end
    
    
    drop table #table_Northwind
    have below error
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ')'.

    unable to find ,can help me

  18. #18
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Posts
    3,590

    Re: how to write in excel file of sql query result

    You should first check your OLEDB providers installed on the SQL Server machine, use xp_enum_oledb_providers (extended) stored procedure for this

    EXEC master.dbo.xp_enum_oledb_providers

    . . . to get results similar to this:

    Code:
    Provider Name			Parse Name				Provider Description
    SQLOLEDB			{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}	Microsoft OLE DB Provider for SQL Server
    MSOLAP				{10154F28-4979-4166-B114-3E7A7926C747}	Microsoft OLE DB Provider for Analysis Services 10.0
    SSISOLEDB			{1AD8F6DD-9411-4908-BF39-DAE7696EB426}	OLE DB Provider for SQL Server Integration Services
    SQLNCLI11			{397C2819-8272-4532-AD3A-FB5E43BEAA39}	SQL Server Native Client 11.0
    Microsoft.ACE.OLEDB.12.0	{3BE786A0-0366-4F5C-9434-25CF162E475E}	Microsoft Office 12.0 Access Database Engine OLE DB Provider
    ADsDSOObject			{549365d0-ec26-11cf-8310-00aa00b505db}	OLE DB Provider for Microsoft Directory Services
    MSOLEDBSQL			{5A23DE84-1D7B-4A16-8DED-B29C09CB648D}	Microsoft OLE DB Driver for SQL Server
    SQLNCLI10			{8F4A6B68-4F36-4e3c-BE81-BC7CA4E9C45C}	SQL Server Native Client 10.0
    Search.CollatorDSO		{9E175B8B-F52A-11D8-B9A5-505054503030}	Microsoft OLE DB Provider for Search
    MSDASQL				{c8b522cb-5cf3-11ce-ade5-00aa0044773d}	Microsoft OLE DB Provider for ODBC Drivers
    MSDAOSP				{dfc8bdc0-e378-11d0-9b30-0080c7e9fe95}	Microsoft OLE DB Simple Provider
    Notice that Microsoft.Jet.OLEDB.4.0 provider is 32-bit only so if you are using an x64 version of SQL Server you cannot possible find it in the list above and cannot possible use it with OPENROWSET and/or linked servers per se.

    What you need to work with xls and newer xlsx/xlsb files in x64 environment is the x64 version of Microsoft.ACE.OLEDB.12.0 provider. This is Microsoft Access Database Engine 2010 which you have to first download and then install on your SQL Server machine.

    Another caveat you have to consider is that with OPENROWSET you cannot *create* new xls files. What you can do is provide a pre-created template xls file and insert/append new data into a sheet of a copy of your template workbook.

    Your original query can be dumped into the target/copy xls file like this

    Code:
    INSERT OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\TEMP\testing.xls', [Sheet1$])
    SELECT * FROM products WHERE name='xyz'
    Keep in mind that the OPENROWSET above returns a resultset with certain columns so your SELECT * FROM products should return the same number and match the columns from OPENROWSET otherwise the INSERT statement will fail.

    Professional Solution to I/O of external for SQL Server data

    Here are two utility stored procedures that we are using to deal with external data in SQL Server. These are handling data in xls, xlsx, xlsb and csv files for us by first creating "linked" views in tempdb.

    For instance using something like

    EXEC dbo.usp_adm_CreateLinkView 'C:\TEMP\testing.xls', NULL, 'v_testing'

    . . . will create a permanent tempdb.dbo.v_testing "linked" view (in tempdb database) to the first sheet of your xls file. Such regular view can be used to SELECT from or INSERT into with regular T-SQL syntax like

    INSERT tempdb.dbo.v_testing(Col1, Col2) SELECT code, name FROM products WHERE name='xyz'

    Once done working with the external data in the xls file just use

    EXEC dbo.usp_adm_DropLinkView 'v_testing'

    . . . to drop the "linked" view and the resource lock used internally.

    cheers,
    </wqw>

  19. #19

    Thread Starter
    Junior Member
    Join Date
    Aug 2019
    Posts
    27

    Re: how to write in excel file of sql query result

    i find below
    Provider Name Parse Name Provider Description
    SQLOLEDB {0C7FF16C-38E3-11d0-97AB-00C04FC2AD98} Microsoft OLE DB Provider for SQL Server
    MSOLAP {10154F28-4979-4166-B114-3E7A7926C747} Microsoft OLE DB Provider for Analysis Services 10.0
    SQLNCLI11 {397C2819-8272-4532-AD3A-FB5E43BEAA39} SQL Server Native Client 11.0
    ADsDSOObject {549365d0-ec26-11cf-8310-00aa00b505db} OLE DB Provider for Microsoft Directory Services
    SQLNCLI10 {8F4A6B68-4F36-4e3c-BE81-BC7CA4E9C45C} SQL Server Native Client 10.0
    Search.CollatorDSO {9E175B8B-F52A-11D8-B9A5-505054503030} Microsoft OLE DB Provider for Search
    MSDASQL {c8b522cb-5cf3-11ce-ade5-00aa0044773d} Microsoft OLE DB Provider for ODBC Drivers
    MSDAOSP {dfc8bdc0-e378-11d0-9b30-0080c7e9fe95} Microsoft OLE DB Simple Provider
    MSIDXS {F9AE8980-7E52-11d0-8964-00C04FD611D7} Microsoft OLE DB Provider for Indexing Service

  20. #20
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Posts
    3,590

    Re: how to write in excel file of sql query result

    You have to install AccessDatabaseEngine_X64.exe on your SQL Server to have an x64 OLEDB provider capable of accessing Excel files.

    cheeers,
    </wqw>

  21. #21

    Thread Starter
    Junior Member
    Join Date
    Aug 2019
    Posts
    27

    Re: how to write in excel file of sql query result

    installed 32 bit,as it was supported not 64

  22. #22

    Thread Starter
    Junior Member
    Join Date
    Aug 2019
    Posts
    27

    Re: how to write in excel file of sql query result

    have 32 bit office products

  23. #23
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Posts
    3,590

    Re: how to write in excel file of sql query result

    Quote Originally Posted by erum_mirza View Post
    installed 32 bit,as it was supported not 64
    You need the x64 OLEDB provider. Might want to try x64 version from Microsoft Access Database Engine 2016 Redistributable if you are using newer Office.

    Use xp_enum_oledb_providers to make sure Microsoft.ACE.OLEDB.12.0 provider is accessible.

    Until resolving OLEDB provider problem you cannot make any progress on your original issue.

    cheers,
    </wqw>

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width