Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need to export Excel data to Sql server via VB

Posted on 2008-06-25
23
Medium Priority
?
877 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 1200 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

688 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