Exporting to Excel Using a CLR Stored Procedure

Question

Post reply

Exporting to Excel Using a CLR Stored Procedure

Viewing 15 posts - 46 through 60 (of 124 total)

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

sang-lee SSC Enthusiast Points: 104 More actions · Answer 1

sang-lee

SSC Enthusiast

Points: 104

December 2, 2009 at 9:16 am

#1086604

Believe it or not, I'm one of many at my company. Just large datasets for what we do...I'm no SQL expert by any means, but I've had to learn and teach myself to manipulate a few hundred million rows of data... 500K + spreadsheet is small by comparison. 😉

Adrian Charles Chetwynd-Talbot SSC Enthusiast Points: 178 More actions · Answer 2

Adrian Charles Chetwynd-Talbot

SSC Enthusiast

Points: 178

December 2, 2009 at 9:19 am

#1086607

does anybody know of a full proof way of migrating data from excel to sql server using stored procedures especially where the data may contain hidden characters especially spaces

Ade

Anders Pedersen SSChampion Points: 11410 More actions · Answer 3

INCREDIBLEmouse (12/2/2009)

sang-lee (12/2/2009)

I haven't looked at the source code, but will this be limited to the 65K rows for excel or will it allow the 1 Million row limit for Excel 2007? I could use this for some datasets that are 500K+.

I want to meet the guy who receives, opens and reads the content within million row spreadsheets.

Obviously their job needs some serious efficiency review.

If you worked in the financial sector I think you would find that this is not uncommon. Especially actuaries like to have the raw data with the ability to manipulate it afterwards for what they need (which usually changes from day to day).

ross.cecil Mr or Mrs. 500 Points: 543 More actions · Answer 4

ross.cecil

Mr or Mrs. 500

Points: 543

December 2, 2009 at 9:35 am

#1086627

The same large data set situation occurs in my world of engineering data analysis. I have customers who want to look at every event over the last year that fit a particular pattern. They build and review trend sets will millions of individual items.

Rob Fisk SSCommitted Points: 1841 More actions · Answer 5

Adrian Charles Chetwynd-Talbot (12/2/2009)

does anybody know of a full proof way of migrating data from excel to sql server using stored procedures especially where the data may contain hidden characters especially spaces

Ade

There is an earlier post here about the OPENROWSET method: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Given Excel's anoying habit of using hidden prefixes (single quote at the start to identify text is my pet hate) then I doubt anything is 100% foolproof.

With the above though you at least get to muck about with the select and view it in a result set just like any other query and add in any replacements you may need before committing to anything. Maybe add LTRIM(RTRIM(col1)),......

P.S. Has anyone else noticed that the forum seems to be quoting the previous entry to that intended?

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

Idea Deadbeat SSC-Addicted Points: 416 More actions · Answer 6

I tested it successfully with SQL 2005. Two questions / statements - When I double click on the xls file I get a message that the file you are trying to open is in a different format than the extension. I say yes to continue and it opens fine (using Office 2007). Second question - I changed the extension in the C# to be xlsx and then when I try to open the file I get Excel cannot open the file because the format or the extension is invalid (it does not open). Ultimately I would like it to work with xlsx and without any message regarding format and extension. I thought if there were a quick answer - I could modify the C# accordingly.

Really appreciate this example - thank you.

edobrzel SSC Rookie Points: 29 More actions · Answer 7

XLSX is a different file format. The file format used in the code and article uses XML. It isn't a "real" Excel file. Excel has no problem with it, as do many other spreadsheet programs.

Save a normal XLS as "XML Spreadsheet". Open it in Notepad. Open the original XLS in Notepad. Quite different. 🙂 Office 2007 (XLSX) uses a new standard. It is actually a Zip file containing multiple XML files. Rename one to .zip and see.

Here's a link if people want more info.

http://msdn.microsoft.com/en-us/library/aa338205.aspx

There is also an SDK and development group.

http://openxmldeveloper.org/

John Ryle Valued Member Points: 65 More actions · Answer 8

Greetings,

this seems pretty cool, but I just tried running this and got the error

"The file you are trying to open, 'file.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

Might you know how I can avoid this message?

Thanks! I am running this on 64 bit.

Idea Deadbeat SSC-Addicted Points: 416 More actions · Answer 9

Idea Deadbeat

SSC-Addicted

Points: 416

December 2, 2009 at 11:26 am

#1086689

I found this link helpful - but a better solution would be that the document generated be in Office 2007 format.

http://www.itexperience.net/2008/03/17/excel-2007-error-different-format-than-specified-by-the-file-extension/

fthomas-735932 SSC Enthusiast Points: 170 More actions · Answer 10

I like the fact that Excel does not have to be installed on the Server and it does not use the Excel COM object. We use it here and it is always hanging up and I have to logon to the Server and kill the 'Excel' processes in order to get it working.

I will try to implement this here. This will save alot of headaches.....thanks

Anders Pedersen SSChampion Points: 11410 More actions · Answer 11

Brad Chapman-387295 (12/2/2009)

I found this link helpful - but a better solution would be that the document generated be in Office 2007 format.

http://www.itexperience.net/2008/03/17/excel-2007-error-different-format-than-specified-by-the-file-extension/

That can certainly be done, however our company is not on Office 2007 yet, so we decided to stick with this format. The concept should be the same, just a different output to the file.

The files open fine in Excel 2007, you just have to answer the pop-up that you want to open it.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 12

My only problem with this is the TRUSTWORTHY ON part. There are so many other ramifications associated with that beyond just allowing your External_Access CLR procedure to execute. This opens up cross database permissions based on the level of rights of the database owner, and can be really problemattic when restoring the database since TRUSTWORTHY is set to OFF by default when you restore or attach a database since it is untrusted. Its to easy to use a certificate signed assembly and do it the right way. Aside from that good work, I like the concept, especially that you used the OpenOffice XML format instead of trying to interop it with UNSAFE assembly.