Re: Get data out of excel file stored as an image

•

•

•

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 397,602 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,641 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser:

Get data out of excel file stored as an image

•

•

Join Date: Sep 2006

Location: San Diego, CA USA

Posts: 99

Reputation: M_K_Higa is an unknown quantity at this point

M_K_Higa is an unknown quantity at this point

Rep Power: 2

Solved Threads: 2

M_K_Higa is offline

Offline

Junior Poster in Training

Re: Get data out of excel file stored as an image

#7

Nov 2nd, 2007

Yes -- you can build the statement and then use the execute command to execute the statement.

I do something similar to the following in my actual code...

 Help with Code Tags 
 SQL Syntax (Toggle Plain Text) 
declare @text_01 AS varchar(1000) 
declare @text_02 AS varchar(500)
declare @spreadsheet AS varchar(250)
declare @worksheet AS varchar(250)
declare @sql_statement AS varchar(2000)
 
SET @spreadsheet = 'c:\path\test.xls'
SET @worksheet = 'Sheet1'
 
SET @text_01 = 'INSERT INTO SpreadsheetDataDump (f1,f2,f3,f4,f5,f6) SELECT f1,f2,f3,f4,f5,f6 '
 
SET @text_02 = ' FROM OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'',''Data Source="'
+  @spreadsheet + '";Extended Properties="Excel 8.0;IMEX=1;HDR=NO"'')...'
+  @worksheet + '$' 
 
 
SET @sql_statement =  @text_01 + @text_02
 
exec (@sql_statement )
declare @text_01 as varchar(1000) 
declare @text_02 as varchar(500)
declare @spreadsheet as varchar(250)
declare @worksheet as varchar(250)
declare @sql_statement as varchar(2000)

set @spreadsheet = 'c:\path\test.xls'
set @worksheet = 'Sheet1'

set @text_01 = 'INSERT INTO SpreadsheetDataDump (f1,f2,f3,f4,f5,f6) SELECT f1,f2,f3,f4,f5,f6 '

set @text_02 = ' FROM OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'',''Data Source="'
+  @spreadsheet + '";Extended Properties="Excel 8.0;IMEX=1;HDR=NO"'')...'
+  @worksheet + '$' 


set @sql_statement =  @text_01 + @text_02

exec (@sql_statement )

In my actual code, the @spreadsheet and @worksheet variables are passed in as parameters. I've declared them in this sample code for clarity.

Hope this helps.

-Mike

Reply With Quote

Forum Highlights

Related Features

DANIWEB FEATURE INDEX

Advertisement

Statistics

Members: 397,602

Users Online Now: 2,641

Active IRC Users: 25

Forum Threads: 129,438

Forum Posts: 664,075

Code Snippets: 883

Blogs: 146

Blog Entries: 2,109

Directory Links: 566