Exporting Sql Server Table to MS Excel

Here is the Coding for Exporting the any Sql Server to Excel Sheet using VBA

a. Take an excel sheet and place the Database Name and Table name in any 2 cells as below



In my Sheet i placed the Database Name and Table name on cells M12 and cells M14 respectively.

Place a button just below them and enter the following code by creating a new Module in Visual Basic Editor(alt + F11)


Sub DataExtract()

Dim tablename As String
Dim Databasename As String
' Create a connection object.
Dim cnPubs As New ADODB.Connection

tablename = Range("M14").Value
Databasename = Range("M12").Value

Worksheets("Sheet2").Cells.ClearContents
Sheet2.Cells.Font.Name = "Tahoma"
Sheet2.Cells.Font.Size = "8"

'Provide the connection string.
Dim strConn As String

cnPubs.Provider = "sqloledb"

'Use the SQL Server OLE DB Provider.
strConn = "DATA SOURCE=Server Name;INITIAL CATALOG =" & Databasename & ";user id = ;password = "

''Change the Connecting String (www.connectionstrings.com)
'Now open the connection.
cnPubs.Open strConn

Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset


With rsPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.

.Open "SELECT * FROM " & tablename
' Copy the records into cell A1 on Sheet1.
Sheet2.Range("A1").CopyFromRecordset rsPubs

' Tidy up
.Close
End With

Sheet2.Rows.Font.Bold = False

Sheet2.Activate
Rows("1:1").Select
Selection.Insert Shift:=xlDown


Dim i As Integer

For i = 0 To rsPubs.Fields.Count - 1
Sheet2.Cells(1, i + 1) = rsPubs.Fields(i).Name
Next

Sheet2.Rows(1).Font.Bold = True


cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

End Sub




When the import button is clicked The Sql Server Table will be imported to Sheet 2 as below!!!

Columns in CSV Format

Eg: The Popular Northwind Database.

Combine the Products and Orderdetails table with the Productid to get the Productname and name a new table as Product_orders

The Join is as follows


select
od.*,p.productname
from
[order details] od
join
products p
on
od.productid=p.productid
order by od.orderid

The Data is as follows




Now If you need all the products and the Quantity in per orderid in a single column like



so the Table looks like

OrderID Productname
343434 Product1,Quantity||Product2,Quantity||Product3,Quantity

Here is the Code Sample to do the Reporting Structure


--Create the table for orderid and allproducts as

create table result
(
orderid int
,Values_CSV varchar(8000)
)


--Use the Following Cursor Code


declare csno cursor for
select distinct orderid from product_orders

declare @snos int

open csno
fetch next from csno into @snos

declare @allvalues varchar(8000)

while @@fetch_status = 0

begin

set @allvalues=null
select @allvalues =isnull(@allvalues+'||','') + [productname]+','+convert(varchar(50),[quantity])
from product_orders
where orderid = @snos

insert into result values(@snos,@allvalues)

fetch next from csno into @snos

end

close csno
deallocate csno

Proper Case using Sql

Here is the Sql function that will Proper Case any column in a table


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
create function [dbo].[ProperCase](@Text as varchar(8000))
returns varchar(8000)
as
begin
declare @Reset bit;
declare @Ret varchar(8000);
declare @i int;
declare @c char(1);
select @Reset = 1, @i=1, @Ret = ''
while (@i <= len(@Text))
select @c= substring(@Text,@i,1),
@Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
@Reset = case when @c like '[a-zA-Z]' or @c in ('''') then 0 else 1 end,
@i = @i +1
return @Ret
end



--Example

declare @name varchar(100)
set @name ='brue willis'
set @name = dbo.propercase(@name)
print @name

Result -- Bruce Willis