Need to export Excel data to Sql server via VB

If you are doing this as a one off though, SQL Server can use DTS (in 2000) or SSIS (in 2005) to bring the data in.

Otherwise, if you are after writing a VB app to do this for you, you can either generate the appropriate DTS/SSIS code, or use CreateObject() to create an Excel object, then using the object open up the excel file in question and go through the Worksheets.  For each worksheet, generate a CREATE TABLE <worksheetname> statement building up the column names from the worksheet's first row.  You would then execute that statement before generating INSERT INTO statements for each line of data.

Since you do not know the type of data in each column, you would probably specify each column as being varchar.

i am using this but the file name parameter is static. i want to make in dynamic.

let me know . also can i pass a variable in to dts for source and target



Create   procedure ImportExcel
 as

Begin

--@Reportsheet varchar (255)
Delete from ReportingTemp
Insert into ReportingTemp
SELECT     *
FROM
--      
OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=C:\ReportsTemplate.xls', 'Select * from [Reporting_Table$]')


select * from ReportingTemp

--- ImportExcel 'C:\ReportsTemplate.xls'
End

OK, what you need instead then is

DECLARE @sSQL AS VARCHAR(1000)
SET @sSQL = "Delete from ReportingTemp" + CHR(13) + CHR(10) +
    "Insert into ReportingTemp" + CHR(13) + CHR(10) +
    "SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=" + @sFilename + "', 'Select * from [Reporting_Table$]')"
Execute sp_executeSQL @sSQL

Select allOpen in new window

As I wrote that away from any SQL environment CHR() may be CHAR() but I'm pretty sure I've got it write.

Oh, and you can move the DELETE statement out of the dynamic SQL to static since its always on the same table.

I am getting there two error

I am not good at  Dynamic sqls you wrote.

Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@sFilename'.
Server: Msg 195, Level 15, State 1, Line 4
'CHR' is not a recognized function name.


can i covert this into a stored proc

Now i ran this
DECLARE @sSQL AS VARCHAR(1000)
DECLARE  @sFilename AS VARCHAR(1000)
Set @sFilename = 'C:\ReportsTemplate.xls'

SET @sSQL ="Insert into ReportingTemp" + CHAR(13) + CHAR(10) +
    "SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=" + @sFilename + "', 'Select * from [Reporting_Table$]')"
Execute sp_executeSQL @sSQL



Error

Server: Msg 207, Level 16, State 3, Line 5
Invalid column name 'Insert into ReportingTemp'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name 'SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE='.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name '', 'Select * from [Reporting_Table$]')'.

Attached is the excel

ReportsTemplate.xls

Hmm, swear I added the code already... suggested you try the attached code, but you may have to fix it up slightly as I'm no where near my workstation so its all from my head :)

Create   procedure ImportExcel
(@Filename VARCHAR(255))
 as
 
Begin
 
DECLARE @sSQL varchar(4000)
 
--@Reportsheet varchar (255)
DELETE FROM ReportingTemp
SET @sSQL = "INSERT INTO ReportingTemp SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=" + @Filename = "', 'Select * from [Reporting_Table$]')"
 
spExecuteSQL @sSQL
SELECT * FROM ReportingTemp
 
--- ImportExcel 'C:\ReportsTemplate.xls'
End

Select allOpen in new window

OK, weird, now it showed up... I tried refreshing twice too... what I would now suggest is that you add a PRINT @sSQL before the spExecuteSQL and see what the full SQL statement is that it has produced.  Oh, and add some brackets around the select statement, forgot those!

I know i understand but somewhere there is syntex error

after running attached code i get
Server: Msg 170, Level 15, State 1, Procedure ImportExcel, Line 11
Line 11: Incorrect syntax near '='.

I changed =  to + at     + @Filename =


I reran

Server: Msg 170, Level 15, State 1, Procedure ImportExcel, Line 13
Line 13: Incorrect syntax near 'spExecuteSQL'.

Please help

ah spExecuteSQL is actually EXECUTE sp_ExecuteSQL @sSQL

I tried that already it stiil throws an error

I am sorry mark

Create   procedure ImportExcel
(@Filename VARCHAR(255))
 as
 
Begin
 
DECLARE @sSQL varchar(4000)
 
--@Reportsheet varchar (255)
DELETE FROM ReportingTemp
SET @sSQL = "INSERT INTO ReportingTemp SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=" + @Filename = "', 'Select * from [Reporting_Table$]')"
 
spExecuteSQL @sSQL
SELECT * FROM ReportingTemp
 
--- ImportExcel 'C:\ReportsTemplate.xls'
End



Server: Msg 207, Level 16, State 3, Procedure ImportExcel, Line 11
Invalid column name 'INSERT INTO ReportingTemp SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE='.
Server: Msg 207, Level 16, State 1, Procedure ImportExcel, Line 11
Invalid column name '', 'Select * from [Reporting_Table$]')'.

OK.  Took me a minute to figure out what was going on there.  This is basically because SQL uses single quotes not double quotes for strings.  So, the attached code should work, though you may need to modify the SELECT statement to retreive exactly what you need.

Create   procedure ImportExcel
(@Filename VARCHAR(255))
 as
 
Begin
 
DECLARE @sSQL varchar(4000)
 
--@Reportsheet varchar (255)
DELETE FROM ReportingTemp
SET @Filename = 'C:\TEMP.XLS'
SET @sSQL = 'INSERT INTO ReportingTemp (SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;DATABASE='
SET @sSQL = RTRIM(@sSQL) + @Filename + ''', ''Select * from [Reporting_Table$]''))'
 
PRINT @sSQL 
EXECUTE sp_ExecuteSQL @sSQL
SELECT * FROM ReportingTemp
 
--- ImportExcel 'C:\ReportsTemplate.xls'
End

Select allOpen in new window

Hi
    I appreciate your help . Still get some error

Alter   procedure ImportExcel2
 @Filename VARCHAR(255)
 as
--
Begin
DECLARE @sSQL varchar(4000)
 
--@Reportsheet varchar (255)
DELETE FROM ReportingTemp
SET @Filename = 'C:\ReportsTemplate.xls'
SET @sSQL = 'INSERT INTO ReportingTemp (SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;DATABASE='
SET @sSQL = RTRIM(@sSQL) + @Filename + ''', ''Select * from [Reporting_Table$]''))'
 
PRINT @sSQL
EXECUTE sp_ExecuteSQL @sSQL
SELECT * FROM ReportingTemp
 
 --ImportExcel2 'C:\ReportsTemplate.xls'
End




Error


(0 row(s) affected)

INSERT INTO ReportingTemp (SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=C:\ReportsTemplate.xls', 'Select * from [Reporting_Table$]'))
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 15
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.





I changed this DECLARE @sSQL varchar(4000) to DECLARE @sSQL nvarchar(4000)


I reran

error

INSERT INTO ReportingTemp (SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=C:\ReportsTemplate.xls', 'Select * from [Reporting_Table$]'))
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.

You are missing an opening ' from the before the filename....Add two single quotes

Alter   procedure ImportExcel2
 @Filename VARCHAR(255)
 as
--
Begin
DECLARE @sSQL varchar(4000)
 
--@Reportsheet varchar (255)
DELETE FROM ReportingTemp
SET @Filename = 'C:\ReportsTemplate.xls'
SET @sSQL = 'INSERT INTO ReportingTemp (SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;DATABASE='''
SET @sSQL = RTRIM(@sSQL) + @Filename + ''', ''Select * from [Reporting_Table$]''))'
 
PRINT @sSQL 
EXECUTE sp_ExecuteSQL @sSQL
SELECT * FROM ReportingTemp
 
 --ImportExcel2 'C:\ReportsTemplate.xls'
End

Select allOpen in new window

Still getting this error .

I have no idea whats going on

(0 row(s) affected)

INSERT INTO Reporting (SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE='C:\ReportsTemplate.xls', 'Select * from [Reporting_Table$]'))
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 15
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Change @sSQL back to being an NVARCHAR, I didn't change that in my copy, sorry.

I have tried that too i get this error


(0 row(s) affected)

INSERT INTO Reporting (SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE='C:\ReportsTemplate.xls', 'Select * from [Reporting_Table$]'))
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'C:'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string '))'.

(0 row(s) affected)

thats what i changed to nvarchar

Alter procedure ImportExcel2
 @Filename VARCHAR(255)
 as
--
Begin
DECLARE @sSQL nvarchar(4000)
 
--@Reportsheet varchar (255)
DELETE FROM Reporting
--SET @Filename = 'C:\ReportsTemplate.xls'
SET @sSQL = 'INSERT INTO Reporting (SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;DATABASE='''
SET @sSQL = RTRIM(@sSQL) + @Filename + ''', ''Select * from [Reporting_Table$]''))'
 
PRINT @sSQL
EXECUTE sp_ExecuteSQL @sSQL
SELECT * FROM Reporting
 
 --ImportExcel2 'C:\ReportsTemplate.xls'
End

go it

here is the syntax

Alter procedure ImportExcel2
 @Filename nVARCHAR(255)
 as
--
Begin
DECLARE @sSQL nvarchar(4000)
print  @Filename
--@Reportsheet varchar (255)
DELETE FROM Reporting
--SET @Filename = 'C:\ReportsTemplate.xls'
SET @sSQL = 'INSERT INTO Reporting SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;DATABASE='
PRINT @sSQL
SET @sSQL = RTRIM(@sSQL) + @Filename + ''', ''Select * from [Reporting_Table$]'')'
 
PRINT @sSQL
EXECUTE sp_ExecuteSQL @sSQL
SELECT * FROM Reporting
 
 --ImportExcel2 'C:\ReportsTemplate.xls'
End

One last question can i make pass parameter for sheet also  sheet .
[Reporting_Table$]'')'
thanks so much for everything