Sometimes it is necessary to import data from Excel file and insert into SQL Server table or building an automated script for inserting data into SQL Server table or building an automated script for inserting data and save into another excel file to insert later. Today I will discuss all the possibilities to solve this kind of problems. You need to follow one or more than one steps of the following depending on your requirement.
Step1: How to Import Data from Excel to SQL Server 2005
SQL Server OpenRowSet command makes the data transformation easily. You can use the following simple command to get the data from Import.xls file and stored into new table tmpTable1. You can perform an additional check that the table is already existing or not. You have to specify the parameter for driver, file path and query into OpenRowSet().
SELECT * INTO [dbo].[tmpTable1] FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\Import.xls;','SELECT * FROM [Sheet1$]')
Note: The following error could be raised if you do not configure the Surface Area Configuration below.
Error: SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
Enable OPENROWSET and OPENDATASOURCE SUPPORT from the Surface Area Configuration to avoid error
Step2: How to build a data insert query script programmatically by stored procedure
The following stored procedure will return you the Insert Commands in the result Set. You can copy it from the results Grid and run from the Query Editor on SQL Server 2005 Management Studio
i.e. EXEC SP_DataInsertScript 'InsertTblName'
===================================================================================
Create Procedure SP_DataInsertScript
@InsertTblName varchar(128)
as
/*
exec SP_DataInsertScript 'InsertTblName'
*/
DECLARE @TblName varchar(128)
SELECT @TblName = 'tmpTable1'
create table #temp (id int identity (1,1), ColType int, ColName varchar(128))
insert #temp (ColType, ColName)
select case when DATA_TYPE like '%char%' then 1 else 0 end ,
COLUMN_NAME
from information_schema.columns
where TABLE_NAME = @TblName
order by ORDINAL_POSITION
if not exists (select * from #temp)
begin
raiserror('No columns found for table %s', 16,-1, @TblName)
return
end
declare @id int ,
@maxid int ,
@cmd1 varchar(7000) ,
@cmd2 varchar(7000) ,
select @id = 0 ,
@maxid = max(id)
from #temp
select @cmd1 = 'select '' insert ' + @InsertTblName + ' ( '
select @cmd2 = ' + '' select '' + '
while @id < @maxid
begin
select @id = min(id) from #temp where id > @id
select @cmd1 = @cmd1 + ColName + ','
from #temp
where id = @id
select @cmd2 = @cmd2
+ ' case when ' + ColName + ' is null '
+ ' then '''' '
+ ' else '
+ case when ColType = 1 then ''''''''' + ' + ColName + ' + ''''''''' else 'convert(varchar(20),' + ColName + ')' end
+ ' end + '','' + '
from #temp
where id = @id
end
select @cmd1 = left(@cmd1,len(@cmd1)-1) + ' ) '' '
select @cmd2 = left(@cmd2,len(@cmd2)-8) + ' from ' + @tblName
exec (@cmd1 + @cmd2)
drop table #temp
===========================================================================
Step3: How to Dump Insert Query into another Excel File
If you want to dump resultset into Excel File then you need to store the resultset in another temporary table and export from table into Excel file using bcp command. Let me explain it how to do:
DECLARE @QueryString varchar(1000)
SET XACT_ABORT ON
BEGIN TRAN
CREATE TABLE testObjects1 ( QueryString varchar(1000) NOT NULL )
INSERT testObjects1 EXEC SP_DataInsertScript 'InsertTblName'
COMMIT TRAN
The above command will store the result set of into testObjects1 Table. Now you have execute bcp command to export into excel file.
Run the following command from the Query Window
EXEC Master..xp_cmdshell 'bcp [dbo].testObjects1 out C:\Result.xls -U {UserName} -P {Pass} -S {ServerName} -T –c{or –n(binary)}'
Note: You have to Enable xp_cmdshell from the Surface Area Configuration to run the above command
OR
You can also run the following command from the command Promt
bcp [dbo].testObjects1 out C:\Result.xls -U {UserName} -P {Pass} -S {ServerName} -T –c{or –n(binary)}
The Result Excel file should be generated with Insert command here C:\Result.xls
First Time Deployment in the Production Environment using Script | Move sites between Production and Development Environment | Content Deployment using STSADM
It is a common problem in the sharepoint 2007 developemt to hide the “Sign in” link in the client web browser. I didnt find any perfect solution for that. Let's think one scenario. For example, You are developing a WCM website in MOSS and have two entry points to the website - one web app for internal use to edit the site (i.e. you have to authenticate), and one for the website (i.e. for anonymous access).
You have created a master page which obviously has to contain controls that allow users to log in and author content. However since the same master page is used for both web apps, you need to hide those controls when the site's being accessed anonymously. You don't want there to be a 'Sign in' link when viewed anonymously.
Before doing this posting i need to say something about my recent sharepoint post. because One question could be arise that "why all of my recent post is related to sharepoint 2007?"
The answer is: i am working with sharepoint 2007 for last few months specially for one of our client. I am responsible for all Reserch and Development work and architectural setup. You will also find some important post in the next days.
How to customize the EditForm.aspx when you Edit a list item in a SharePoint 2007 list (EditForm.aspx).
Sometime this error has been occured from Windows SharePoint Services Administration command when the user do retractsolution or deploysolution like the follwing
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsadm -o retractsolution -name "%PackageName%" -local -url %TargetWebUrl%
or
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsadm -o deploysolution -name "%PackageName%" -local -allowGacDeployment -url %TargetWebUrl%
A deployment or retraction is already under way for the solution "xxx.wsp", and only one deployment or retraction at a time is supported
it occurs if anomalies deployment is running for the same package in the backend due to some previous deployment has been crashed before for some reason.
To avoid this problem do the followings:
Look for the deploy jobId to break the running by the following command:
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsadm-o enumdeployments
• Cancel the deploy typing the following command:
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsadm-o canceldeployment-id "GUID jobId"
• Check that deploy has been canceled by typing the following command:
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsadm-o enumdeployments
Hope it should work.
How to Customize theme code for MYTHEME theme. It is only necessary if you want to create another one like MYTHEME theme. Here are the instructions of creating Custom Theme with MYTHEME standard.
How to: Customize Themes
You can add new themes or customize existing ones for application to Web sites in Microsoft Windows SharePoint Services 3.0. This programming task shows how to customize an existing theme.
To create and customize from an existing theme
- Copy one of the theme folders in Local_Drive
:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\THEMES and give the folder a unique name.
Note: In this example, the name is MyTheme. This folder contains cascading style sheets (CSS) files, image files, and other files that define the styles, formatting, and color for the various user interface (UI) elements that are used in the theme.
- Find the .inf file in the copied folder, and rename it with the name given to the folder. i.e. in this example rename that .inf file with MyTheme.INF
- Open the .inf file and assign the same name to the title in the [info] and [titles] sections of the file.
- There is one theme.css file in that MyTheme folder, open that, Customize the styles defined in the following as needed.
Field-Label Text: It is for all sharepoint text label and field in the form. You have to insert the following code to change in MyTheme standard.
Code:
.ms-formbody {
background: transparent;
border-top: 1px solid #ffffff;
}
.ms-formlabel {
border-top: 1px solid #ffffff;
color: #000000;
}
h3.ms-standardheader{
color:#000000;
font-weight:normal;
font-family:vardana;
}
Button: You have to integrate the following code in the theme.css inside corresponding theme(i.g. “MYTHEME”) folder to change all button style.
Code:
.ms-ButtonHeightWidth
{
width:12.0em;
font:8pt verdana;
height:2.1em;
padding-top:0.1em;
padding-bottom:0.4em;
color:#00231b;
background-color:#cbdeda;
border:#00231b 1px solid;
font-weight:bold;
cursor:hand;
}
.ms-NarrowButtonHeightWidth
{
width:9em;
height:2em;
font:8pt verdana;
padding:0;
color:#00231b;
background-color:#cbdeda;
border:#00231b 1px solid;
font-weight:bold;
cursor:hand;
}
.ms-ButtonHeightWidth2
{
height:2.1em;
font:8pt verdana;
width:11.72em;
padding-top:0.1em;
padding-bottom:0.4em;
color:#00231b;
background-color:#cbdeda;
border:#00231b 1px solid;
font-weight:bold;
cursor:hand;
}
Modify the image files in the copied folder by using the business graphics software of your choice.
- Add a file for thumbnail and preview images for your custom theme to the Local_Drive:
\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\IMAGES directory. In this example, the file is called myPreview.gif.
- Add a theme template definition to SPTHEMES.XML, which is the file that determines which themes are available as options on the Site Theme page. This XML file is located in the Local_Drive
:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\LAYOUTS\1033 directory.
The following example specifies a template for the custom theme.
Xml
<Templates>
<TemplateID>mytheme</TemplateID>
<DisplayName>My Theme</DisplayName>
<Description>Description</Description>
<Thumbnail>images/myPreview.gif</Thumbnail>
<Preview>images/myPreview.gif</Preview>
</Templates>
- Reset Internet Information Services (IIS) by typing
iisreset at the command prompt so that your custom theme appears in the list of options on the Site Theme page and can be applied to SharePoint sites.
Robust Programming
Warning Changes that you make to SPTHEMES.XML might be overwritten when you install updates or service packs for Windows SharePoint Services, or when you upgrade an installation to the next version.
Ref: http://msdn.microsoft.com/en-us/library/aa979310.aspx
I am MJ Ferdous!!, Software Engineer professional in Microsoft Platform from 6 years ago.
Now Responsible for research and developent (R & D) on SharePoint 2007 platform and also provides some consultancy on sharepoint and ASP.Net development.
I am also Technical Author of DevMedia Group, Brazil for their Large Portal www.Mrbool.com as well as owner & modaretor of some groups of sharepoint and asp.net.
My Existing blog is http://geekswithblogs.net/ferdous/Default.aspx
From now, my latest post will also available here.
Enjoy!! my post.
Cheers
Ferdous