Import Excel to SQL Table

23. July 2009 15:57

T-SQL 

Using bulk operations using code with sql can be complex, time consuming and tedious for most of the programmers. I just used a stored procedure in one of my projects instead of traversing from cell to cell and save the value in the database........so I search the net and found this SQL Stored Procedure achieve this. I have just copied and paste the code as it is

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--drop procedure sp_excel_Convert_Excel_To_Table
--go
Create procedure [dbo].[sp_excel_Convert_Excel_To_Table]
(
@excel_full_file_name varchar(666)
,@convert_to_table_name varchar(50)
,@transfer_to_table bit=1
,@clear_existing_records_first bit=1
,@good int =null output
,@error_code int =null
,@error_description varchar(255) = null output
)
as
set nocount on
declare @command varchar(8000)
-- copy excel file under temp and change worksheet name
set @good=0
set @error_description = ''
set @error_code=0
declare @object int
,@hr int
,@src varchar(255)
exec @hr = master.dbo.sp_OACreate 'Excel.Application', @object out
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '1. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=1
set @good=0
goto error
end
exec @hr = master.dbo.sp_OASetProperty @object, 'DisplayAlerts', 'false'
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '2. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=2
set @good=0
goto error
end
declare @workbook int
,@workbook_path_save_as varchar(666)
set @workbook_path_save_as = 'c:\test.xls'
exec @hr = master.dbo.sp_oaMethod @Object,'WorkBooks.Open',@workbook out,@excel_full_file_name
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '3. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=3
set @good=0
goto error
end
exec @hr = master.dbo.sp_OASetProperty @object, 'Workbooks(1).Worksheets(1).Name','excel_data'
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '4. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=4
set @good=0
goto error
end
exec @hr = master.dbo.sp_oaMethod @workbook ,'SaveAs',null,@workbook_path_save_as
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '5. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=5
set @good=0
goto error
end
-- get full file name
exec @hr = master.dbo.sp_OAGetProperty @object, 'Workbooks(1).FullName',
@workbook_path_save_as output
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '6. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=6
set @good=0
goto error
end
exec @hr = master.dbo.sp_oaMethod @Object,'Application.Quit'--,@workbook out,@workbook_path
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '7. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=7
set @good=0
goto error
end
if @transfer_to_table = 1
begin
if @clear_existing_records_first=1
begin
set @command = 'delete from ' + @convert_to_table_name
exec (@command)
end
-- copy records from excel into table:
set @command = 'insert into ' + @convert_to_table_name +
' select * from ' +
' OpenRowSet(''MSDASQL'' '+
', ''Driver={Microsoft Excel Driver (*.xls)};'+
'DBQ=' + @workbook_path_save_as + ''''+
',''SELECT * FROM [excel_data$]'')'
print @command
exec (@command)
set @command = 'select * from ' + @convert_to_table_name
exec (@command)
end
error:
select @error_description as 'Error', @error_code as 'Error Code'
exec @hr = master.dbo.sp_OADestroy @workbook
if @hr <> 0
begin
set @error_description = '7. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=7
set @good=0
end
exec @hr = master.dbo.sp_OADestroy @object
if @hr <> 0
begin
set @error_description = '8. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=8
set @good=0
end
Currently rated 4.0 by 1 person

Rarely available photos

20. July 2009 15:54

Fundoo 

 

1) Tim Berners Lee -- Founder of the World Wide Web


2) Picture taken when microsoft was started (the one who is highlighted is Bill Gates)

 

3) Steve Woznaik(sitting) and Steve Jobs of APPLE Computers.
He was three months late in filing a name for the business because he didn't get any better name for his new company.
So one day he told to the staff: "If I'll not get better name by 5 o'clcok today, our company's name will be anything he likes..."
so at 5 o'clcok nobody comeup with better name, and he was eating APPLE that time...
so he keep the name of the company 'Apple Computers'



4) Bill Hewlett(L) and Dave Packard(R) of HP.
Behind them in the picture is the famous HP Garage.
Bill Hewlett and Dave Packard tossed a coin to decide whether the company they founded would be called Hewlett-Packard or Packard-Hewlett.
And the winner was NOT Bill... the winner was Dave.


5) Ken Thompson (L)and Dennis Ritchie(R) ,creators of UNIX.
Dennis Ritchie improved on the B programming language and called it 'New B'.
B was created by Ken Thompson as a revision of the Bon programming language (named after his wife Bonnie) He later called it C.


6) Larry Page(L) and Sergey Brin(R), founders of Google.
Google was originally named 'Googol'.
After founders (Stanford graduates) Sergey Brin and Larry Page presented
their project to an angel investor...
they received a cheque made out to 'Google' !...
So they kept name as GOOGLE

7) Gordon Moore(L) and Bob Noyce(R) ,founders of Intel.
Bob Noyce and Gordon Moore wanted to name their new company 'Moore Noyce'.
But that was already trademarked by a hotel chain...
So they had to settle for an acronym of INTegrated ELectronics. .. INTEL


8) Andreas Bechtolsheim , Bill Joy, Scott Mc Nealy and Vinod Khosla of
SUN(StanfordUnivers ity Network) MicroSystems.
Founded by four StanfordUniversity buddies.
Andreas Bechtolsheim built a microcomputer;
Vinod Khosla recruited him;
Scott McNealy to manufacture computers based on it;
and Bill Joy to develop a UNIX-based OS for the computer...
SUN is the acronym for Stanford University Network .


9) Linus Torvalds of Linux Operating System Linus Torvalds originally used
the Minix OS on his system which he replaced by his OS.
Hence the working name was Linux (Linus' Minix).
He thought the name to be too egotistical and planned to name it Freax
(free + freak + x).
His friend Ari Lemmk encouraged Linus to upload it to a network so it could
be easily downloaded.
Ari gave Linus a directory called linux on his FTP server, as he did not
like the name Freax.
Linus like that directory name and he kept the name of his new OS to LINUX...


10) Picture taken when INFY was started. This picture was found in the
album of the clerk who took this picture...
The picture was with that clerk only because it was his birthday and he
just told everyone to stand together at one place to take a pic.
He borrow a camera from his friend and as he can not tell any of his boss
to take pic, so he took pic by himself... even it was his birthday.

Currently rated 5.0 by 2 people

C# Method: Encrypting a string using MD5 algorithm

20. July 2009 00:40

C# Code Snippets 

Encrypting a string using the MD5 algorithm

This C# method will encrypt any string using MD5 algorithm. It generates the same hash as the PHP MD5() function

using System;using System.Collections.Generic;
using System.Linq;using System.Text;
using System.Text.RegularExpressions;
using System.Security.Cryptography;
namespace EncryptString
{
    class Program
    {
        public static string EncodePassword(string originalPassword)
        {
            Byte[] originalBytes;
            Byte[] encodedBytes;
            MD5 md5;
            md5 = new MD5CryptoServiceProvider();
            originalBytes = ASCIIEncoding.Default.GetBytes(originalPassword);
            encodedBytes = md5.ComputeHash(originalBytes);
            return Regex.Replace(BitConverter.ToString(encodedBytes), "-", "").ToLower();
         }
         static void Main(string[] args)
         {
            Console.WriteLine("Enter a string to Encrypt");
            string strEnc = Console.ReadLine();
            Console.WriteLine(EncodePassword(strEnc));
            Console.ReadLine();
         }
    }
}
Currently rated 3.0 by 2 people

Insert binary files to SQL server using BULK operation

11. July 2009 18:15

T-SQL 

Performing BULK operations with SQL can be useful when you are trying to insert any type of file. Don't mess up with the complex code, just use a simple SQL query to insert any file type in SQL table. 

To perform this operation first you need to have a column with datatype as varbinary(max). See below example:

Create a table with an ID and Data as columns with INT and VARBINARY(MAX) as their Datatypes respectively.

Create table tblData
(
FileId int,
FileData varbinary(max)
)

Perform INSERT to Save a file. Here just change the location of the file you want to insert

Insert into tblData
(FileId, FileData)
SELECT 1, BulkColumn
FROM OPENROWSET(BULK N'D:\Software\siw.exe', SINGLE_BLOB) AS Document

Display table data after INSERT

Select * from tblData
Currently rated 3.0 by 2 people

GUIDs in C# / SQL

26. June 2009 15:37

C# SQL Server Code Snippets 

GUID pronounced as goo'id - Globally unique identifier is a 128-bit integer that can be used to uniquely identify something. If you want to identify the large collection of data GUID is the best approach in my view. Also you can use the autoincremental integer to identify records, this is the basic approach programmer's use.

GUID method can be found under the System namespace and it also provides some overloads. Here is how we can use the GUID overloads:

System.Guid.NewGuid().ToString();    //c93ded50-7cce-4f3a-b8aa-5a1a34d80b14
System.Guid.NewGuid().ToString("N"); //636bdfd2bbc541229fbc6b869995ce6f
System.Guid.NewGuid().ToString("D"); //45e602b6-0904-47ac-a764-a5522f56f40f
System.Guid.NewGuid().ToString("B"); //{68f55508-f72c-4a38-b96b-f493c3c5adb9}
System.Guid.NewGuid().ToString("P"); //(1c2e52a7-e384-445e-b80a-e8704c6ecbd6)

To create GUID in SQL Server use the simple select statement:

SELECT NEWID()

 No Rating