Solved

Need to export Excel data to Sql server via VB

Posted on 2008-06-25
23
852 Views
Last Modified: 2011-10-19
Need to export Excel data to Sql server via VB .

I shall be able to select Excel File  and sheet via vb and i want to export to  sql table
0
Comment
Question by:kshireesh
  • 12
  • 11
23 Comments
 
LVL 3

Expert Comment

by:GHCS_Mark
ID: 21870320
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.

0
 

Author Comment

by:kshireesh
ID: 21870498
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
0
 
LVL 3

Expert Comment

by:GHCS_Mark
ID: 21870621
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

Open in new window

0
 
LVL 3

Expert Comment

by:GHCS_Mark
ID: 21870627
As I wrote that away from any SQL environment CHR() may be CHAR() but I'm pretty sure I've got it write.
0
 
LVL 3

Expert Comment

by:GHCS_Mark
ID: 21870631
Oh, and you can move the DELETE statement out of the dynamic SQL to static since its always on the same table.
0
 

Author Comment

by:kshireesh
ID: 21870715
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
0
 

Author Comment

by:kshireesh
ID: 21870741
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
0
 
LVL 3

Expert Comment

by:GHCS_Mark
ID: 21870778
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

Open in new window

0
 
LVL 3

Expert Comment

by:GHCS_Mark
ID: 21870783
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!
0
 

Author Comment

by:kshireesh
ID: 21870884
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
0
 
LVL 3

Expert Comment

by:GHCS_Mark
ID: 21870909
ah spExecuteSQL is actually EXECUTE sp_ExecuteSQL @sSQL
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:kshireesh
ID: 21871131
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$]')'.
0
 
LVL 3

Expert Comment

by:GHCS_Mark
ID: 21872548
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

Open in new window

0
 

Author Comment

by:kshireesh
ID: 21874399
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
0
 

Author Comment

by:kshireesh
ID: 21874403

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 ')'.
0
 
LVL 3

Expert Comment

by:GHCS_Mark
ID: 21874891
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

Open in new window

0
 

Author Comment

by:kshireesh
ID: 21875670
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'.
0
 
LVL 3

Expert Comment

by:GHCS_Mark
ID: 21876173
Change @sSQL back to being an NVARCHAR, I didn't change that in my copy, sorry.
0
 

Author Comment

by:kshireesh
ID: 21876635
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)
0
 

Author Comment

by:kshireesh
ID: 21876639
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
0
 

Author Comment

by:kshireesh
ID: 21876960
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
0
 

Author Comment

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

0
 
LVL 3

Accepted Solution

by:
GHCS_Mark earned 300 total points
ID: 21877233
Yes, just add a second parameter and have that replace the [Reporting_Table$] bit just like the existing code does for the filename.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now