Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving entire sheet to SQL Server 2k: XML? values+formats.
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. -- Regards, Sébastien <http://www.ondemandanalysis.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving entire sheet to SQL Server 2k: XML? values+formats.
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving entire sheet to SQL Server 2k: XML? values+formats.
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. -- Regards, Sébastien <http://www.ondemandanalysis.com "AA2e72E" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving entire sheet to SQL Server 2k: XML? values+formats.
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving entire sheet to SQL Server 2k: XML? values+formats.
Sample code link:
http://p2p.wrox.com/archive/visual_c...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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving entire sheet to SQL Server 2k: XML? values+formats.
Thank you so much AA2e72E. That's exactly what i was looking for. I'll try it
today. -- Regards, Sébastien <http://www.ondemandanalysis.com "AA2e72E" wrote: Sample code link: http://p2p.wrox.com/archive/visual_c...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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving / emailing sinlge excel sheet, not entire workbook | Excel Discussion (Misc queries) | |||
Macro to copy values then delete row for entire sheet | New Users to Excel | |||
Using VB Copy Entire Row but formulas and formats only no values | Excel Discussion (Misc queries) | |||
Saving on the server | Excel Programming | |||
Saving listbox values to a sheet then repopulate | Excel Programming |