asked on

OA Automation stored procedures to get excel

I saw the following example to extract excel records that were stored in a sql server 2014 table varbinary field

(just to make sure it was stored correctly)

sp_configure 'show advanced options', 1;   


 GO  


 RECONFIGURE;  


 GO  


  sp_configure 'Ole Automation Procedures', 1;   


 GO  


 RECONFIGURE 



Create table FileWarehouse(
id int identity (1,1)
,filetype nvarchar(20)
,storedfile varbinary(max))

insert into FileWarehouse select 'xlsx' as FileType, * from openrowset (bulk 'c:\test_oa.xlsx',single_blob) as x

select * from filewarehouse

DECLARE @SQLIMG VARCHAR(MAX),
    @FilePath VARBINARY(MAX),
    @OpPath VARCHAR(MAX),
    @ObjectToken INT


        SELECT @FilePath = StoredFile from filewarehouse where id =1

		--select @FileInTable

        SET @OpPath = 'c:\filestest_output.xls'
		--select @OPPath outputpath

        EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
        EXEC sp_OASetProperty @ObjectToken, 'Type', 1
        EXEC sp_OAMethod @ObjectToken, 'Open'
        EXEC sp_OAMethod @ObjectToken, 'Write', NULL,  @FilePath
        EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @OpPath, 2
        EXEC sp_OAMethod @ObjectToken, 'Close'
        EXEC sp_OADestroy @ObjectToken

Open in new window

I am not getting errors but not getting the output file either

checked both sql server and my local drive

Not sure what I'm doing wrong

The excel spread sheet just has 2 columns and rows

Test1 Test2
a b

Ryan Chong

I saw the following example to extract excel records that were stored in a sql server 2014 table varbinary field

perhaps few questions to ask to better understand your current issues.

1) Is your Excel data kept in "original" form or in binary format? But I saw your table: FileWarehouse has a varbinary field: storedfile

2) Have you successfully imported the data from Excel to table: FileWarehouse?

3) Do you have a sample Excel file which can share with us?

I recently wrote an article regarding How to load Excel's data into SQL Server in different ways. See if that can give you some insights?
(Referring to method # 2 : Using SQL Server Management Studio - OPENROWSET command)

ASKER CERTIFIED SOLUTION

Máté Farkas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial