question

ni10 avatar image
ni10 asked

Error Occurred in Bulk inserting Excel to Database Table

Hi i'm trying to dump all Excel data to Given Database Table. but their is some problem occured. My Temporary table is given below CREATE TABLE #Temp( [Id] varchar(15) , [SSNumber] varchar(20) , [SSDate] datetime , [CCCode] varchar(15) ) OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\New Microsoft Excel Worksheet.xls;HDR=YES', 'select * from [Sheet1$] where id is not null') ; for Bulk inserting i'm use OPENROWSET(Shown Here ^ ), when command is executed Id, SSDate and CCCode is properly inserted in Given Database Table, but SSNumber doesn't inserted properly. Bcoz in that Column NUMBER & CHARACTERS are there (for Example"11107" and also "HN112") See Below :- 11107 HN111 11108 HN112 11109 11111 when all data is inserted in SQL Table the following output is displayed. OUTPUT : 11107 NULL 11108 NULL 11109 11111 But Actually i wan't (same output same as Excel File) ie (See Below) 11107 HN111 11108 HN112 11109 11111 I'm Attaching here to my procedure and Excel file. please let me know whats wrong in this code. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO -- ============================================= -- Author: NK -- Create date: 02 Aug 2011 -- Description: Export Excel to Sql Server Table -- When you run this procedure first you can check sp_configure setting is started. -- If this setting is not started then run "exec PrExportToExcelStart" this procedure. -- ============================================= ALTER PROCEDURE [dbo].[TestExcel] ( @DestinationLink as varchar(200) ) AS DECLARE @strSql nvarchar(max), @lastEditDate nvarchar(20) BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --exec PrExportToExcelStart CREATE TABLE #Temp( [Id] varchar(15) , [SSNumber] varchar(20) , [SSDate] datetime , [CCCode] varchar(15) ) set @lastEditDate = convert(char(11),getdate(),106) set @strSql = N'INSERT INTO #Temp ' set @strSql = @strSql + "(Id,SSNumber,SSDate,CCCode) " set @strSql = @strSql + " SELECT DDId,SSNumber,SSDate,CCCode" set @strSql = @strSql + " FROM OPENROWSET ('" set @strSql = @strSql + 'Microsoft.Jet.OLEDB.4.0' set @strSql = @strSql + "', '" set @strSql = @strSql + 'Excel 8.0;Database=' set @strSql = @strSql + ""+@DestinationLink+"" set @strSql = @strSql + ';HDR=YES' set @strSql = @strSql + "', '" set @strSql = @strSql + 'select * from [Sheet1$] where ID is not null' set @strSql = @strSql + "')AS A;" print "@strSql => " + @strSql exec sp_executesql @strSql SELECT * FROM #Temp DBCC FREEPROCCACHE WITH NO_INFOMSGS --exec sp_configure 'Ad Hoc Distributed Queries', 0; --RECONFIGURE WITH OVERRIDE End -- EXEC TestExcel 'C:\New Microsoft Excel Worksheet (2).xls' And Excel File Data Is Id SSNumber SSDate CCCode 2 11107 4/2/2011 PM000261 9 HN111 4/2/2011 PM000019 7 HN111 4/2/2011 PM000019 1 11108 4/2/2011 PM000197 9 HN112 4/2/2011 PM000174 5 HN112 4/2/2011 PM000174 8 11109 4/2/2011 PM004127 9 11111 4/2/2011 PM000635
sql-server-2005t-sqlexcelsql-server-express
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Daniel Ross avatar image
Daniel Ross answered
It sounds like the good old import mode problem with sql and excel which has absolutely done my head in in the past. When I know i am going to work with excel and ssis, I always add IMEX=1 to the connection string. change this one line to; set @strSql = @strSql + ';HDR=YES;IMEX=1' have a look at this support MS KB http://support.microsoft.com/kb/194124
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rikky48 avatar image
rikky48 answered
Solve out excel problems with the aid of xlsx file repair [ http://www.xlsxrepairtoolbox.com][1] repairs all objects, including fonts, cells, colors, formulas, preserving the original formatting [1]: http://www.xlsxrepairtoolbox.com
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.