| Author |
Topic |
|
marthacmoore
Starting Member
5 Posts |
 Posted - 2012-10-10 : 13:07:42
|
| 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
15732 Posts |
Posted - 2012-10-10 : 13:49:46
|
| Why can't you use SSIS? It's the best tool for the job. |
 |
|
|
marthacmoore
Starting Member
5 Posts |
Posted - 2012-10-11 : 10:09:48
|
| 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 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-10-11 : 10:40:50
|
| 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. |
|
|
|
marthacmoore
Starting Member
5 Posts |
Posted - 2012-10-11 : 10:55:23
|
| 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 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-10-11 : 11:03:59
|
| 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? |
|
|
|
marthacmoore
Starting Member
5 Posts |
Posted - 2012-10-11 : 11:33:00
|
| 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 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-10-11 : 11:57:35
|
| Here's details on bcp: http://msdn.microsoft.com/en-us/library/aa337544.aspxIt 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? |
|
|
|
marthacmoore
Starting Member
5 Posts |
Posted - 2012-10-12 : 16:33:38
|
| 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 |
|
|
|
|
Create Excel Spreadsheet from SQL Server 2008