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

Stored Procedure for Searching any Value in a Table

This Stored Procedure is for Searching any value in any column in a table


Exec searchforvalues 'param1','param2'

param1 - Tablename
param2 - A String(including wildcard characters)


Use it for your Convienience!!!

---Coding---

alter procedure searchforvalues
@tablename nvarchar(100),@searchstring nvarchar(100)
as

declare @c_id nvarchar(1000)
declare @sstring nvarchar(1000)
declare @sql nvarchar(1000)
declare @String nvarchar(1000)

set nocount on

set @searchstring = ''''+@searchstring+''''

if exists(select * from information_schema.tables where table_name like +@tablename)
begin

if exists(select * from information_schema.tables where table_name like 'Sql_search')

begin
set @sql=''
set @sql=@sql+N'drop table Sql_search'
exec sp_executesql @sql
end


set @sql=''
set @sql=@sql+'create table Sql_search(column_name nvarchar(1000),searchstring nvarchar(1000))'
exec sp_executesql @sql


declare c1 cursor for
select column_name from information_schema.columns
where
table_name like @tablename

--Cursor Starts
open c1

fetch next from c1
into @c_id

while @@fetch_status = 0

begin
set @String =''''+@c_id+''''
set @sql=''
set @sql=@sql+'insert into sql_search select '+@string+',['+@c_id+'] from '+@tablename+' where ['+@c_id +'] like '+@searchstring
exec sp_executesql @sql
fetch next from c1
into @c_id
end

close c1
deallocate c1

--Cursor Ends

--Output
set @sql=''
set @sql=@sql+'select column_name,searchstring ,count(*) as [Count] from sql_search
group by column_name,searchstring
order by column_name'
exec sp_executesql @sql
print @sql



exec('drop table sql_search')

end

else

Print 'Table Does Not Exist'

Stored Procedures - Removing Duplicates

The Following is a Simple Stored Procedure that flags a records as duplicate in a table where duplicate exists

a.Checks for the valid column and tablename.
b. Creates an Srn (identity column).
c. Creates a column dupe
d.Flags Dupes wherever duplicate records found

The Code for the Stored Procedure is as follows

GO
/****** Object: StoredProcedure [dbo].[remove_duplicates] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[remove_duplicates]
@colname varchar(200)
,@tablename varchar(100)

as

set nocount on

declare @sql varchar(1000)
declare @no_duplicates int

--Check for the Table and Column Name
set @sql=''
if exists
(
select * from syscolumns where name like +''+@colname+'' and id = object_id(+''+@tablename+'')
)
begin

--Generate an SRN Number
if exists(select * from syscolumns where id = object_id(+''+@tablename+'') and name like 'Vic_srn')
begin

set @sql=''
set @sql = @sql + 'alter table '+@tablename+ ' drop column Vic_srn'
exec(@sql)

end


set @sql=''
set @sql = @sql+'alter table '+@tablename+ ' add Vic_srn numeric(6) identity(1,1)'
exec(@sql)

--Add a column for Dupes

if exists(select * from syscolumns where id = object_id(+''+@tablename+'') and name like 'dupes')
begin

set @sql=''
set @sql = @sql + 'alter table '+@tablename+ ' drop column dupes'
exec(@sql)

end

set @sql=''
set @sql=@sql+' alter table '+@tablename+ ' add dupes varchar(10)'
exec(@sql)


--Update the Column Dupes

set @sql=''
set @sql=@sql+'update a '+char(13)
+'set a.dupes = ''yes'''+char(13)
+'from '+char(13)
+'vic_orderdetails a '+char(13)
+'join '+char(13)
+'vic_orderdetails b '+char(13)
+'on '+char(13)
+'a.orderid = b.orderid '+char(13)
+'where '+char(13)
+'a.vic_srn > b.vic_srn '+char(13)
exec(@sql)

if object_id(@tablename) is not null
begin
exec('select count(*) as ''Number of Duplicates'' from '+@tablename+' where dupes is not null')
end

Print 'The Number of Duplicates are '+convert(varchar(100),@no_duplicates)

end


else

Print 'Invalid Table and Column Name'