SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Create a binary from an Excel Sheet
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wydyCH
Starting Member

5 Posts

Posted - 06/20/2013 :  02:23:33  Show Profile  Reply with Quote
Moin,

I have a little Problem with a binary Column in my Database. I have a table named docs with the columns doc_id(PK) and doc_image(image, null).

I want upload an Excel file in this table with an SQL Script. First of all I tried
SELECT BulkColumn
FROM OPENROWSET( BULK 'C:\test.xls',
SINGLE_BLOB) as ExternalFile
It worked on my local machine, but the updater for our clients doesn't have the permission for bulk. Also the Excel file is on the local machine and the script works on a server.
In a next step i tried to upload the binary from the bulk command directly with an INSERT statement. But it doesn't worked. The generated Excel Sheet contains only cryptic symbols like:
®à0ïð``0ïÿÿðÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿßÿÿòP0@P`p€ °ÀÐàð 0@P`p€ °ÀÐàð 0@ïÿÿóÐ0€@ °ÀÐàð 0ïÿÿóP`p€ °Ààð€ @pP`p` °ÀÐàð 0@P€ïÿÿÿïÿÿõïÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿõ ðð@Pà@ `Pÿÿÿÿÿÿÿð € `
À=ûçRüà°<ì–Ìàp0pð ° ðð° pÿÿÿÿÿÿÿð @ð` ð ð P0@ð0P  P0 ŸK

Here the insert statement:
Insert into doc
(doc_id, doc_image)
values('8CDA1EFB-733A-425E-90A6-BF492937B740',
0xD0CF11E0A1B11AE1000000000000000000000000000000003E000300FEFF09000600000000000..)

The binary has about 44k characters. What is the problem? I tried different ways for the upload such as CONVERT, but nothing worked. Is the binary too big for the upload? Is there another way to upload this binary?

I apologize for my mistakes in the question. English isn't my first language.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/20/2013 :  02:43:27  Show Profile  Reply with Quote
whats the datatype of field as recognized by excel?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wydyCH
Starting Member

5 Posts

Posted - 06/20/2013 :  05:04:37  Show Profile  Reply with Quote
Sry I don't unterstand the question.
I will save the whole excel in the database as a binary.
The table in the database is:
CREATE TABLE doc(
doc_id uniqueidentifier ROWGUIDCOL NOT NULL,
dok_image image NULL)
The excel file is a template without any data. There are only a title, some column headings, 2 datefields and a bit layout.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/20/2013 :  05:14:37  Show Profile  Reply with Quote
one way is you can use SSIS. put a data flow task in ssis with excel source and your table as destination and you'll be able to transfer excel binary data to table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wydyCH
Starting Member

5 Posts

Posted - 06/20/2013 :  05:52:53  Show Profile  Reply with Quote
I don't want to transfer data between my excel and the database.

In our solution you can create a report. The program take the excel sheet, put some data in this excel and save it local on the pc.
And the developer of this function had a special idea. Why we don't save the excel file as an image in the database.
Now I have to change this report with some new information. Also I have to change this excel file for the new information. But the file is saved as an image on the server.
Now I have the update the excel on every client of us and thats the problem. The only way for this is a sql skript, which replace the old excel file.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/20/2013 :  06:03:30  Show Profile  Reply with Quote
then why dont you use same datasource the earlier report used, change the source statement (or procedure) to add your new changes and make it save in image(or excel) format so that it overwrites current version? you can do this in reporting tools like SQL Reporting Services.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wydyCH
Starting Member

5 Posts

Posted - 06/20/2013 :  06:49:01  Show Profile  Reply with Quote
I can change the file in the "administration" tool, where the excelfile will replace. This function works with a streamwriter.
But we have about 50 customer, which use our solution. Every customer has his own database, where I have to change the excel file. We created an "autoupdater" which can execute sql Scripts on the different servers/databases of our customers. My idea was to create an sql script, which replace every excelfile and put it on the "autoupdater".
Go to Top of Page

wydyCH
Starting Member

5 Posts

Posted - 06/20/2013 :  11:03:52  Show Profile  Reply with Quote
I found a solution:
I create a base64 string
Then i update this string and convert it in a image.
How to update it: http://www.codemeit.com/sql/sql-2005-base64-decode-query-syntax.html

Here is the solution
-- Code
SET ANSI_WARNINGS ON

--base64 Daten vom Excel
DECLARE @data varchar(max), @XmlData xml
SET @data = '...'
SET @XmlData = CAST('<data>' + @data + '</data>' as xml)
insert into doc
(doc_id, doc_image)
values('8CDA1EFB-733A-425E-90A6-BF492937B740'
, CONVERT(image, @XmlData.value('(data)[1]', 'varbinary(max)'))
)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000