SQLServerCentral.com Articles tagged Excel

Low Profile Architecture

Tue, 02 Apr 2013 06:00:00 UT

The goal of this article to show how to get unique transactions per client per month from the data set containing multiple transactions per client per month.

QlikView for Beginners - An Introduction to QlikView

Mon, 17 Dec 2012 07:00:00 UT

A brief introduction to getting started with QlikView.

12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.

Stairway to PowerPivot and DAX - Level 2: The DAX COUNTROWS() and FILTER() Functions

Fri, 07 Dec 2012 07:00:00 UT

Bill Pearson, business intelligence architect and author, exposes the DAX COUNTROWS() and FILTER() functions, while generally exploring, comparing and contrasting the nature and operation of calculated columns and calculated measures, in the second Level of our Stairway to PowerPivot and DAX series.

Using SSIS to export data from a 64 bit server to Excel 2007

Tue, 04 Dec 2012 07:00:00 UT

Instructions on how to set up the connection string and SSIS package to allow a 64 bit server to export to a 32 bit version of Excel

Importing Data From Excel Using SSIS - Part 1

Mon, 08 Oct 2012 06:00:00 UT

Recently while working on a project to import data from an Excel worksheet using SSIS, I realized that sometimes the SSIS Package failed even though when there were no changes in the structure/schema of the Excel worksheet. I investigated it and I noticed that the SSIS Package succeeded for some set of files, but for others it failed. I found that the structure/schema of the worksheet from both these sets of Excel files were the same, the data was the only difference. How come just changing the data can make an SSIS Package fail? What actually causes this failure? What can we do to fix it?

CPU and Scheduler Performance Monitoring using SQL Server and Excel

Thu, 04 Oct 2012 06:00:00 UT

This article will demonstrate a method of creating an Excel-based CPU/scheduler performance dashboard for SQL Server 2005+.

Set Up Grouping Intervals for Excel (SQL Spackle)

Tue, 31 Jul 2012 06:00:00 UT

So you want to group your data for charting in Excel? Here's how, the easy way!

How to automate your .NET and SQL Server deployments
Deploy .NET code and SQL Server databases in a single repeatable process with Red Gate Deployment Manager. Start deploying with a 28-day trial

How to Name Sheets in Exported Excel Workbooks with SSRS 2008 R2

Mon, 22 Aug 2011 06:00:00 UT

A new feature of SSRS 2008 R2 is the ability to name sheets in exported Excel workbooks. This article explains how to utilize this feature.

XLSExport: A CLR procedure to export proc results to Excel

Thu, 23 Jun 2011 06:00:00 UT

A CLR procedure utilizing the NPOI library to export the results of a passed stored procedure to an Excel spreadsheet.

Excel in the Clouds

Tue, 21 Jun 2011 06:00:00 UT

Today Steve Jones talks about a new use for cloud computing with Excel to analyze large sets of data.

SQL Server Data Mining with Microsoft Office Excel

Fri, 11 Feb 2011 07:00:00 UT

This article demonstrates using SQL Server's data mining capabilities with the Excel.

Protect the Excel files generated by SSIS package-Part I

Thu, 28 Oct 2010 06:00:00 UT

It is not only the data within the database that requires protection but any report generated using SQL Server needs to be protected before being mailed

Add Some Spark to Your OData: Creating and Consuming Data Services with Visual Studio and Excel 2010

Wed, 20 Oct 2010 06:00:00 UT

The Open Data Protocol (OData) is an open REST-ful protocol for exposing and consuming data on the web. Also known as Astoria, ADO.NET Data Services, now officially called WCF Data Services in the .NET Framework. There are also SDKs available for other platforms like JavaScript and PHP.

12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.

Excel with Stored Procedures

Fri, 13 Aug 2010 06:00:00 UT

Longtime DBA David Poole brings us an interesting situation in involving setting up a user to allow Excel to query data from SQL Server.

SQL Server 2008,Windows 2008 x64 and Excel

Wed, 28 Jul 2010 06:00:00 UT

Learn about how you might solve some issues running SSIS packages using Excel on a Windows 2008 x64 Machine.

Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.

How to Read Excel Cells from SQL Server Integration Services

Wed, 28 Apr 2010 06:00:00 UT

We can get data in all sorts of formats in the real world. In this new article from Dinesh Asanka, learn how to use scripting to import data from individual Excel cells.

How to find unused Stored Procedures in SQL Server 2005/2008

Thu, 15 Apr 2010 06:00:00 UT

Use dynamic PivotTables to identify unused Stored Procedures in a SQL Server 2005/2008 database

Top 5 hard-earned Lessons of a DBA
New! Part 4, ‘Disturbing Development’ by Grant Fritchey, features the return of Joe Deebeeay and a server-threatening encounter with ORMs - read it here

Automating Excel from SQL Server

Fri, 19 Feb 2010 07:00:00 UT

How to have SQL Server drive Excel spreadsheets

Make working with SQL a breeze
SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.

Import Excel Spreadsheet to Database Tables

Wed, 17 Feb 2010 07:00:00 UT

Importing Excel Spreadsheets is something that we often do with SQL Server. However it's not as easy as you would like as we move to the 64 bit platform. Peter He brings us a method that will work on all platforms.

Use SSIS to import one cell of an Excel file into SQL Server

Mon, 15 Feb 2010 07:00:00 UT

Recently I needed to find a method to import one cell of an Excel sheet into SQL Server 2005 using a scheduled job on a 64 bit clustered environment. For example, I have a monthly sales report listing sales per product category, such as Books, Magazines, DVDs, etc. I only want to import the Total Sales amount into SQL Server and this number is located in an Excel file "sales.xls.sheet1.cell.B5". I used to use OPENROWSET to solve the problem, however OPENROWSET requires Microsoft Jet OLE DB Provider which is not available in 64 bit. In this tip I am going to talk about how to use SSIS to accomplish this task easily.

Self-Service BI with PowerPivot for Excel 2010

Fri, 22 Jan 2010 07:00:00 UT

Thiago kicks off a new series of articles, taking us through everything we need to know to use PowerPivot effectively. First off, he walks us through the prerequisites, and getting started with our first basic data set.

Exporting to Excel Using a CLR Stored Procedure

Wed, 02 Dec 2009 07:00:00 UT

The inclusion of the CLR in SQL Server has been controversial, but some people have come up with great ways to take advantage of this feature. New author Anders Pedersen brings us a technique to export data from SQL Server into Excel using the CLR.

Database defintion from an Excel Spec?

Thu, 12 Nov 2009 07:00:00 UT

Have you ever been required to create a database with nothing but a specification in Excel?

How to Dynamically and Iteratively Populate An Excel Workbook from SQL Server

Fri, 16 Oct 2009 06:00:00 UT

Integration Services is a great ETL tool, allowing you to build complex and dynamic transformations. New author Marie Deschene brings us a

Excel with Stored Procedures

Fri, 13 Aug 2010 06:00:00 UT

Longtime DBA David Poole brings us an interesting situation in involving setting up a user to allow Excel to query data from SQL Server.

Using SQL Server Data Mining Add-Ins for Office 2007 to Predict Database Growth Trends - Part 2

Wed, 22 Jul 2009 06:00:00 UT

I want to show you another easier way to pull information to help predict database growth again using the Data Mining Tools.

Tracking and Reporting Database Growth

Tue, 02 Jun 2009 06:00:00 UT

An article from new author Lokesh Gunjugnur that shows how you can set up tracking for database growth on multiple servers and creating custom reports in Excel.

Installing & Congfiguring DM Add-in for Excel - SQL School Video

Thu, 19 Feb 2009 07:00:00 UT

This SQL School video explains how to use the Data Mining add-in for Excel 2007. The installation and configuration are covered.

Data Mining in Excel 2007 Pt.2 - SQL School Video

Thu, 12 Feb 2009 07:00:00 UT

In this video MVP Brian Knight continues on with more advanced data mining using Excel.

Data Mining in Excel 2007 Pt.1 - SQL School Video

Tue, 10 Feb 2009 07:00:00 UT

Excel has greatly helped ease the process of data mining. In this video MVP Brian Knight shows how you can start using the data mining add-ins for Excel to get started.