Solved

Need to export Excel data to Sql server via VB

Posted on 2008-06-25
23
866 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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
 

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

739 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