posts - 83 , comments - 119 , trackbacks - 2

My Links


MJ Ferdous

Ferdous has industry level experience on Microsoft Platform and has done several presentations and workshops on SharePoint. Prior to joining Microsoft, He worked as SharePoint Architect at BrainStation-23, OT Consulting, Rockwell Automation etc. Ferdous also worked as SharePoint Consultant and SharePoint Trainer for many organizations such as BASIS, Robi Axiata, etc.

He was the key organizer & speaker of “SharePoint Developer’s Conference 2013” & "SharePoint Community Day 2012" jointly with Microsoft. He is the author of several technical articles at code project, MS SharePoint Tips, Dev Media etc. He is also founder of SharePoint Experts Bangladesh user-group.

invisible hit counter View My Stats

Tag Cloud


Image Galleries

Blogs [MVP]

My Articles

My Others Link

Public Speaking

Data Import from excel file and build an automated insert Script to export into excel file or to export into Sql Server table

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
 Enable OPENROWSET and OPENDATASOURCE SUPPORT  from the Surface Area Configuration
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)
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 ,
      from information_schema.columns
      where       TABLE_NAME = @TblName
      order by ORDINAL_POSITION
      if not exists (select * from #temp)
            raiserror('No columns found for table %s', 16,-1, @TblName)
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
            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         
      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)
CREATE TABLE testObjects1 ( QueryString varchar(1000) NOT NULL )
INSERT testObjects1 EXEC SP_DataInsertScript 'InsertTblName'
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

You have Enable xp_cmdshell from the Surface Area Configuration to run the above command

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 

Print | posted on Thursday, February 19, 2009 5:15 AM |


No comments posted yet.
Post A Comment

Powered by: