Saving entire sheet to SQL Server 2k: XML? values+formats.

[sebastienm]

Hi,

I am having a hard determining which technology to use to store these xl
files while maintaining flexibility of use.

Business Problem:
This is a continuing process.
Step 1
- 1 book for a each region - contains only 1 sheet to gather data.
- need to be able to keep several versions of a single regions over time
- only some users can edit these files (they are edited at any time).
Step 2
- when a file has been updated and reviewed by admin, he pushes it to
publication. At that point, files are broken into data pieces into a star
schema ms server 2k.
Step 3
- end users query the db to generate basic reports.

Basic Requirements:
1- need to store both data + formats of the centralized excel files in Step
1 & 2 .
2- if an admin decides to add a row, it has to be added in all files, all
regions, all versions (with a default value)

Once the data is published into a star schema db (Step2), everything is fine.
I have however some issues concerning Step1: what technology to use to store
the file while keeping required functionality (eg formatting, keeping
formulas, comments) and being able to control access and versioning, being
able to control global addition/deletion of rows and columns, tracking who is
editing it...
SharePoint is out of question unfortunatelly which i guess would have been
nice for the CHeckIn/Out feature and the versioning.

I was thinking about saving each excel file as XML and sending the XML
string to the MS Sql 2k db as 1 record into a blob column.
I am just not sure whether or not this would be the easiest and whether this
will end up being an issue later down the road.
There was a reason why we didn't want them on a shared drive, but can't
remember it right now.

Anybody has an idea or an advice.

 

[AA2e72E]

Just some thoughts:

If you are going to write anything to SQL Server, write the XLS file itself
rather than some other form it (e.g. CSV, XML etc). An IMAGE (i.e. BLOB)
field can hold 4GB file sizes.

SQL Server, subject to configuration that may be allowed at your site, can
read Excel files directly BUT you would lose the formatting, which you say is
one of your requirements.

Consider writing code in a Workbook to read/write the BLOB from SQL Server
on demand and save it as an AddIn; your users can checkin this Addin in any
workbook and call this functionality. You would need to handle the automatic
archiving of a workbook via Workbook events ... see this topic in the Excel
help files.

 

[sebastienm]

Thank you AA2e72E

As i received your reply, i was just finishing my testing(See bellow).
- my code is in an addin. I use classes to capture events. It works fine on
this side.
- I have more issues with Sql Server 2k as i just know the basics.
- how do you save an xl file in its original format to a sql server (IMAGE)?
I don't know how to do that. Do you have a quick sample code? (i'll google it
right away though)

What i have tested so far:
- i save the file as XML Spreadsheet (which keeps most formats)
- i read the file via FileSystemObject.FIle.OpenAsStream
- i sql it to sql server to ntext column.
I do the oposite process to load it into excel.

In terms of performance, loading the file from server or saving it to server
takes less than 3 seconds.
Well, the server is on my physical machine, (but not local), on a virtual
machine, so it will certainly be slower at work through vpn.

In term of size, my regular xl files are around 100k in size, the xml files
are 5 times larger, around 500k. Any idea on how to reduce this size.

If anybody has a better way of doing this, please let me know.

 

[AA2e72E]

You are almost there. I do not have a sample on this particular PC for BLOB.

Google will find plenty of references.

You need to read the file as a Byte() array in VBA, use AppenChunk of the
ADO.Recordset object.

I'll check the thread later: if you have not figured it out, I'll post some
code.

 

[AA2e72E]

Sample code link:

http://p2p.wrox.com/archive/visual_c_plus_plus/2002-12/39.asp

What applies to Oracle BLOB holds for SQL SERVER; XLS files can be read as
other file types used in the link.

 

[sebastienm]

Thank you so much AA2e72E. That's exactly what i was looking for. I'll try it
today.