SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to store Excel files In SQL Table


How to store Excel files In SQL Table

Author
Message
Nisarg Upadhyay
Nisarg Upadhyay
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 47
Comments posted to this topic are about the item How to store Excel files In SQL Table
fregatepallada
fregatepallada
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 63
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
bimal
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 254
I Agree use the FileTable Feature much better and easier to maintain.



Joe O'Connor
Joe O'Connor
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 277
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
gbritton1
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3077 Visits: 936
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
Jeff Moden
SSC Guru
SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)

Group: General Forum Members
Points: 463756 Visits: 43803
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
david.edwards 76768
david.edwards 76768
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 548
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.
Vlad-207446
Vlad-207446
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 329
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
huishi.ca
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 447
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search