• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1178
  • Last Modified:

Reading .xlsx file into ntext using SQL Server 2008 and ColdFusion 9

I am using MS SQL Server 2008 R2 and ColdFusion 9 update 2:

With CF 9 I create an .xlsx spreadsheet (openxml format).  I need to store this file as a string in a database so that another application can pull the file string from the database and use it as it were either an openxml string or .xlsx format.  Can anyone point me on how to go about doing this?  Presently confused about it.  The app that will be reading it is written in C#.
0
HyperBPP
Asked:
HyperBPP
  • 3
  • 2
1 Solution
 
_agx_Commented:
What do they mean by openxml string?  The.xlsx format isn't really a string.  It's more like a binary .zip file that contains a bunch of text/xml files inside it. (Open up any xlsx file with a tool like pkzip and you'll see the structure).  

You could do a readbinary on that file, and store it as a base64 string. But I suspect that's not what they're looking for.  You need to find out more about what they mean by "openxml" string ...
0
 
HyperBPPAuthor Commented:
No, I think storing it as a base64 string would probably work.  I guess I just readbinary and insert as varbinary(max)?  How do I know it's a base64 string?  What if I need to change the encoding?

Thanks!
0
 
_agx_Commented:
If you're storing it as base64 then it's a string. So use one of the string data types, either varchar(max) or text (for older db's).  

              >  How do I know it's a base64 string?  

You'll be using toBase64() or the BinaryEncode(binary, "base64") function to generate the string.  They return a standard format which consists of certain characters that are safe for internet transmission. Most all languages have functions for converting/from to base64.

           http://en.wikipedia.org/wiki/Base64

Change the encoding how? Base64 is just a safe way to store binary data that might be transmitted as a string over the net.  Once it's decoded back into bytes, it can re-encoded however you need. But I don't think that applies here.  Normally you'd just decode a base64 string back into binary. Then you have an xlsx file you can save to disk or manipulate in memory (like with cf's spreadsheet functions).
0
 
HyperBPPAuthor Commented:
I will not be transferring over the net but rather the other app and my app sit side by side in an "environment" and their app will read directly from my DB.  So in this case I should store as binary?  So as no need to encode or decode?

Thanks!
0
 
_agx_Commented:
It's also considered a safe format overall when transferring between systems. But yes binary is simpler.  The main reason for suggesting base64 is because the original requirement said "openxml string", suggesting binary was not acceptable. But if that's ok, then just do a readbinary and store as varbinary.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now