SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Create Excel Spreadsheet from SQL Server 2008
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

marthacmoore
Starting Member

USA
5 Posts

Posted - 10/10/2012 :  13:07:42  Show Profile  Reply with Quote
We need to create Excel spreadsheets from a SQL Server 2008 database without using SSIS. What is the best way to do that? We have large amounts of data - average of 40 columns of various formats - mostly text and numeric. We have large quantities of pipe-delimted text files (our data contains a lot of commas) and need a way to automatically generate Excel spreadsheets from that data.

Does anyone out there know a good way to do this without using SSIS?

Martha C. Moore

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 10/10/2012 :  13:49:46  Show Profile  Visit robvolk's Homepage  Reply with Quote
Why can't you use SSIS? It's the best tool for the job.
Go to Top of Page

marthacmoore
Starting Member

USA
5 Posts

Posted - 10/11/2012 :  10:09:48  Show Profile  Reply with Quote
We are having issues using SSIS in that making a quick change to the package (inserting a space or any new information) breaks the package. It seems to be difficult to use. I am new to the tool, but have been told by others here that there is no way to recompile the package after a small change is made. If it was easy to make quick changes, we wouldn't have a problem. Is there a way to quickly recompile after making a quick change?

Martha C. Moore
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 10/11/2012 :  10:40:50  Show Profile  Visit robvolk's Homepage  Reply with Quote
How about copying the package to a new one and making the change? After testing that the change works, you can either point the process to use the new package, or save/rename the new one to the old one. At no point would you have a broken package.
Go to Top of Page

marthacmoore
Starting Member

USA
5 Posts

Posted - 10/11/2012 :  10:55:23  Show Profile  Reply with Quote
That is what they have been doing but are finding it very cumbersome and time-consuming, since we have many, many changes. They want to find a better way if one exsists - even a "low-tech" way would be preferable if we can think of one. Do you know of any other tools that can accomplish this or can you think of another way to create Excel from SQL Server 2008 tables?

Martha C. Moore
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 10/11/2012 :  11:03:59  Show Profile  Visit robvolk's Homepage  Reply with Quote
If I absolutely need an Excel .xls file I use SSIS. 99% of the time though I generate CSV or tab-delimited files using the bcp utility, and the users can open them in Excel. This doesn't allow formatting or multiple tabs though, so if that's a requirement it won't work for you.

I've done a lot of Excel macro programming to pull data from SQL Server into a spreadsheet, enough to know that it's not a preferable option for you if SSIS is already causing you problems.

Compiling the package happens every time you build the project or run it, so whoever told you otherwise is wrong. Make sure to check for warnings and errors during a build and investigate their cause.

Perhaps the package is too complicated? Is it a collection of multiple smaller packages, or one huge package that does everything?
Go to Top of Page

marthacmoore
Starting Member

USA
5 Posts

Posted - 10/11/2012 :  11:33:00  Show Profile  Reply with Quote
I am very new to this and not sure what the 'bcp' utility is. Can you give me a quick hint? Also, we are using pipe-delimited files and I'm assuming that would not be any different than the csv or tab-delimited files mentioned above. If I'm wrong on that, please let me know.

I will look into more details about the specific package but I believe it is one huge package that does everything.

We were discussing the possibility of using an Excel macro, so why do you say that is not preferable?

Martha C. Moore
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 10/11/2012 :  11:57:35  Show Profile  Visit robvolk's Homepage  Reply with Quote
Here's details on bcp: http://msdn.microsoft.com/en-us/library/aa337544.aspx

It can import and export pipe delimted files, you'd use the -t switch to specify | as the delimiter.

Excel macros are VBA code, and for what you're describing, are not likely to be something you can record and play back. If you're not fluent in VBA it's not worth the effort for something basic. Excel does have built-in data query functions that may not require macros to refresh.

I'm not clear where SQL Server comes in to this, are you importing pipe delimited files to SQL Server and then exporting them to Excel? If so, what is the need for SQL Server, rather than just importing it directly to Excel?
Go to Top of Page

marthacmoore
Starting Member

USA
5 Posts

Posted - 10/12/2012 :  16:33:38  Show Profile  Reply with Quote
Thank you for your help. I will look into bcp. We are currently using an SSIS package to extract data from SQL Server tables and create the Excel files, but I'm told we want to bypass having to use SSIS as the extract tool.

Thanks again for the helpful bcp link!!

Martha C. Moore
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000