How to store Excel files In SQL Table

Question

How to store Excel files In SQL Table

Nisarg Upadhyay

SSC-Addicted

Points: 460
More actions
December 6, 2017 at 9:43 pm

#334037

Comments posted to this topic are about the item How to store Excel files In SQL Table

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

fregatepallada SSC-Addicted Points: 407 More actions · Answer 1

Complete LUNACY.
I highly recommend you to read this first https://www.microsoft.com/en-us/research/wp-content/uploads/2006/04/tr-2006-45.pdf
Are you aware of FILESTREAM capabilities (since SQL Server 2008).This IMHO would be much better approach than store BLOBs in table.

bimal Valued Member Points: 74 More actions · Answer 2

I Agree use the FileTable Feature much better and easier to maintain.

Joe O'Connor SSC Eights! Points: 904 More actions · Answer 3

fregatepallada - Thursday, December 7, 2017 1:17 AM
Complete LUNACY.
I highly recommend you to read this first https://www.microsoft.com/en-us/research/wp-content/uploads/2006/04/tr-2006-45.pdf
Are you aware of FILESTREAM capabilities (since SQL Server 2008).This IMHO would be much better approach than store BLOBs in table.

You're assuming all users have network access to the SQL server - that's not always the case.

gbritton1 SSCertifiable Points: 6522 More actions · Answer 4

Nisarg Upadhyay - Wednesday, December 6, 2017 9:43 PM
Comments posted to this topic are about the item How to store Excel files In SQL Table

I agree that FileTable was made for this. My main question though is, "why would I want to do this?" What will you do with the blobs once you get them into SQL? For me (and others, I suspect), shredding Excel workbooks into SQL Server tables is more interesting (and a lot more challenging).

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 5

fregatepallada - Thursday, December 7, 2017 1:17 AM
Complete LUNACY.
I highly recommend you to read this first https://www.microsoft.com/en-us/research/wp-content/uploads/2006/04/tr-2006-45.pdf
Are you aware of FILESTREAM capabilities (since SQL Server 2008).This IMHO would be much better approach than store BLOBs in table.

Heh... "Complete LUNACY". And, yet, the authors of that good document (and it IS well written by those with a high pedigree) say the following in the conclusion of their document.

We did not investigate the behavior of NTFS or SQL Server when multiple writes to multiple objects are interleaved. This may happen if objects are slowly appended to over long periods of time or in multithreaded systems that simultaneously create many objects.

Neither have the authors provided the code that they've actually done their testing with so that one could verify their methods.

Also, and to ask the question, have you actually done a performance comparison where you've methodically applied the scientific method and documented your findings or are you sharing the same anecdotal feelings that the authors of the document are trying to dispel?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

david.edwards 76768 Ten Centuries Points: 1246 More actions · Answer 6

Unfortunately not had the time to thoroughly read and digest the linked document, but I saw very frequent mention of fragmentation being a large factor. With the proliferation of cheap SSD storage it would be an extremely useful experiment to re-do their testing in a modern environment.

A bit mean to write this off as lunacy I think, thank you for providing the link however, it is of interest to me - I have an application that out of the box has been merrily storing document as blobs. A task I need to get to is determining whether it is worth the upheaval of getting this out of the database and onto the filesystem - luckily all small documents.

"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

Vlad-207446 Right there with Babe Points: 773 More actions · Answer 7

I can understand storing some types of files in DB.
like images or special needs files.
I had worked in the past on projects where we would store whole application distribution into the database to make updates and initial rollout simpler. that is we would make a start up DB , upload the distribution into the table, back it up and deploy.
than on site , we would setup SQL, restore our back. make sure all permission are right, including special user for deploy update, than go to any client PC, drop an install exe file onto it and run it.
when run the install.exe (a small app that only consisted of few dialog boxes to allow connection to SQL config and a module to get the app from database into predetermined folder and run full install ).
once we were done, we could just upload any updates into SQL(we had special tools to do that if we had to do it offsite.) and any client on start up would check for updates and install them as needed.
Yes , the main app exe file was only a wrapper. it start check the db for updates and present the user with option to load the update if it was optional or to inform user about update if it was mandatory.
But office or excel files? I am not sure what the use that would be.

huishi.ca SSChasing Mays Points: 600 More actions · Answer 8

Thanks author for sharing this unique technique. As a techie guy, it was always pleasure to know new idea to tackle some common problems. For this reason, it is not necessary to insult people who has spent time and resources documenting it up. It is actually an insult to the whole community by saying it is a complete lunacy. I actually felt offended by this comment.

There are always many solutions to one problem and there are also ways to express your opinions by not insulting others. Please be considerate.

Thanks

HS

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 9

Just to be sure there is no mistake, I'm not the one calling this "Complete Lunacy". I was citing what someone else said.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

fregatepallada SSC-Addicted Points: 407 More actions · Answer 10

My sincere apologies if I offended an author of the article.
IMHO the fact that you can do something it does not mean that you have to do it.
And I agree that there are multiple ways to skin the proverbial CAT.

Regards,