Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 878
  • Last Modified:

Need to export Excel data to Sql server via VB

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
kshireesh
Asked:
kshireesh
  • 12
  • 11
1 Solution
 
GHCS_MarkCommented:
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
 
kshireeshAuthor Commented:
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
 
GHCS_MarkCommented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
GHCS_MarkCommented:
As I wrote that away from any SQL environment CHR() may be CHAR() but I'm pretty sure I've got it write.
0
 
GHCS_MarkCommented:
Oh, and you can move the DELETE statement out of the dynamic SQL to static since its always on the same table.
0
 
kshireeshAuthor Commented:
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
 
kshireeshAuthor Commented:
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
 
GHCS_MarkCommented:
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
 
GHCS_MarkCommented:
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
 
kshireeshAuthor Commented:
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
 
GHCS_MarkCommented:
ah spExecuteSQL is actually EXECUTE sp_ExecuteSQL @sSQL
0
 
kshireeshAuthor Commented:
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
 
GHCS_MarkCommented:
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
 
kshireeshAuthor Commented:
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
 
kshireeshAuthor Commented:

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
 
GHCS_MarkCommented:
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
 
kshireeshAuthor Commented:
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
 
GHCS_MarkCommented:
Change @sSQL back to being an NVARCHAR, I didn't change that in my copy, sorry.
0
 
kshireeshAuthor Commented:
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
 
kshireeshAuthor Commented:
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
 
kshireeshAuthor Commented:
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
 
kshireeshAuthor Commented:
One last question can i make pass parameter for sheet also  sheet .
[Reporting_Table$]'')'
thanks so much for everything

0
 
GHCS_MarkCommented:
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 12
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now