Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

import Excel file in SQL 2005 Table as a variable filename Expand / Collapse
Author
Message
Posted Friday, January 14, 2011 1:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 14, 2011 10:19 PM
Points: 4, Visits: 11
I am struggling with this and could not figure out. I need to import an excelfile as an variable.

The code is

----------
Create Proc bulkinsertfromexcel
as
Begin
Declare @sessionID nvarchar(MAX)
Declare @docpath nvarchar(max)
Declare @jobcreator nvarchar(max)
Declare @SQL nvarchar(MAX)
DECLARE @filename varchar(100)
Set @docpath = 'E:\Workflow\SPP\Attachments\TR\ExcelUpload\''+@filename'

Set @SQL = 'INSERT INTO [Tactical.Claim_LineItems_BULK_Audi] ([JObCreator], [ModelCode],
[ChassisNo], [CommissionNo], [AGSupport],[AVMESupport], [Brand], [Remark])
SELECT A.[JObCreator], A.[ModelCode], A.[ChassisNumber], A.[CommisionNumber],
A.[AG], A.[AVME], A.[Brand], A.[Remarks]
FROM OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;;Database=''+@docpath+'', ''select * from [Sheet1$]'') AS A';



PRINT @SQL
EXEC(@SQL)
End
---------------
The error is
-----------
Msg 102, Level 15, State 1, Procedure bulkinsertfromexcel, Line 23
Incorrect syntax near ','.
------------

Any help to figure out this issue and run the sp successfully is highly appreciated
Post #1047759
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse