Monday, 31 December 2012 16:43

Roll your own business intelligence


It is a truism that what gets measured gets improved. One important function of the CIO is to ensure strategic information is delivered to executive management as a basis for steering the company. Here is a simple C# app to easily publish results of SQL queries as Excel spreadsheets.


What gets measured gets improved; conversely what is not measured is not improved. In my view, one of the important roles of a Chief Information Officer relates to the "information" component of the title; information being meaningful rather than just a dump of data.

Yet, extracting information and publishing it are two separate endeavours. A small enterprise in particular can be hampered if it does not have the funds or in-house skills for dedicated business intelligence tools. Not to worry - with just a small collection of free online tools and compiling a simple application you can make scheduled reports on demand.

Consider this BAT file:

@echo off

sql2xls gm-report.sql gm-report.xlsx

"c:\program Files\7-zip\7z.exe" a -tzip gm-report.xlsx

blat -to -subject "Gross margin report" -attach -body "Gross margin report attached" -debug -log blat.log

del gm-report.xlsx


This is a standard BAT file; that means it executes a bunch of commands to achieve an overall objective. More than this, as a batch file it can be scheduled via Windows' task scheduler, ie Start/Programs/Accessories/System/Task Scheduler to run every morning or every week.

The commands perform these individual functions:

sql2xls gm-report.sql gm-report.xlsx

sql2xls is a very simple C# application I wrote, with source code in this article. It is a console application created within Visual Studio. You can easily edit and compile this code yourself using Visual Studio Express or any command-line C# compiler. You do not need to purchase the full retail edition of Visual Studio.

The app is so-named because it takes a SQL query, contained in the filename specified in the first parameter, executes it, then outputs all the results into an Excel spreadsheet, whose name is specified in the second parameter.

It is not unusual to find report creation taking far more time than strictly necessary due to the inherit slowness of drag-and-drop reporting tools, or by striving for perfect presentation. For internal reporting Excel is generally the best format rather than a PDF'd report with a specific layout.

Having an app like this means that your SQL-savvy users can create their own reports without needing intervention from IT.

I wrote sql2xls simply to fill a need. I had previously tried the SQL Server bcp command to create CSV files but if output included line breaks or quote marks or commas the output, understandably, broke.

So, to use sql2xls simply create a text file with an SQL query in it, no matter how simple or complex. The output will be an Excel spreadsheet with a single worksheet containing all rows and columns from the query output.

"c:\program Files\7-zip\7z.exe" a -tzip gm-report.xlsx

The second command in my BAT file uses 7-Zip, a popular and free compression tool, to zip the Spreadsheet up. You can ignore this step if your report is less than 10Mb in size; for sizes larger than that you may find mail servers either refuse to send or receive it.

Download the 7-Zip command-line edition from

blat -to -subject "Gross margin report" -attach -body "Gross margin report attached" -debug -log blat.log

The third command runs "blat", a lesser known but highly useful, program which e-mails from the command line. Thus, in this line blat is employed to e-mail the zipped-up spreadsheet to a specific recipient, and with a specific subject line and body text. Other options exist to customise the e-mail further.

Download blat from

Finally, the batch file tidies up by removing the intermediate files.

del gm-report.xlsx


With creativity and a little knowledge of DOS BAT commands and options you can create filenames programatically, incorporating the date, for example.

As to sql2xls, the source code follows. In Visual Studio, create a new project of type Windows console application.

Copy-and-paste the source code over the page into Program.cs:

Source code for sql2xls:

using System;

using System.Globalization;

using System.IO;

using OfficeOpenXml;

using System.Data;

using System.Data.SqlClient;


namespace sql2xls


    class Program


        static void Main(string[] args)


            Console.WriteLine("sql2xls\t\tv1.0\t\ - The Wired CIO");


            if (args.Length != 2)


                Console.WriteLine("Usage: sql2xls <input> <output> <DB>");

                Console.WriteLine("\tinput\tinput file with SQL query");

                Console.WriteLine("\toutput\toutput file for XLSX data");



            var inputFile = args[0];

            Console.WriteLine("Input:\t" + inputFile);

            var outputFile = args[1];

            Console.WriteLine("Output:\t" + outputFile);

            var connStr = "Server=;Database=Finance;user id=reader;password=reader";

            var streamReader = new StreamReader(inputFile);

            var sqlStatement = streamReader.ReadToEnd();



            var d = new DataTable();

            var db = new SqlConnection(connStr);


            var cmd = new SqlCommand(sqlStatement, db) {CommandTimeout = 0};

            var rdr = cmd.ExecuteReader();


            Console.WriteLine("Number of rows: " + d.Rows.Count.ToString());

            var package = new ExcelPackage(new FileInfo(outputFile));

            var worksheet = package.Workbook.Worksheets.Add(inputFile);

            var rowNumber = 1;

            var numColumns = d.Columns.Count;

            for (var c = 0; c < numColumns; c++)

                worksheet.Cells[1, c + 1].Value = d.Columns[c].ColumnName;

            foreach (DataRow r in d.Rows)




                for (var c = 0; c < numColumns; c++)

                    worksheet.Cells[rowNumber, c + 1].Value = r[c].ToString();












This won't compile just yet! The app depends on a library to manage Excel spreadsheets from dot NET code, EPPlus. Download EPPlus.dll from In your Visual Studio project, right-click on "References" in the project navigator, click "Add Reference" and browse to EPPlus.dll.

The code will now compile but there is one final step: change the value of the string connStr to a connection string relevant to your own database server. This typically will be the server IP address, database name, and a user name and password which has sufficient access to run the query, or a paramater specifying a trusted connection.

It is possible to modify the program code so the connection string is a parameter but or uses an ODBC data source. There is no end of extensibility available.

Compile the program code and you now have a command-line tool which you can run queries against at will.

With this code and these freely-acquired tools we have gone from a SQL query through to an e- mailed spreadsheet.

Now you can focus more on the construction of meaningful database interrogation and far less on the presentation and distribution of such queries.


Read 6816 times

Please join our community here and become a VIP.

Subscribe to ITWIRE UPDATE Newsletter here
JOIN our iTWireTV our YouTube Community here


Thoughtworks presents XConf Australia, back in-person in three cities, bringing together people who care deeply about software and its impact on the world.

In its fifth year, XConf is our annual technology event created by technologists for technologists.

Participate in a robust agenda of talks as local thought leaders and Thoughtworks technologists share first-hand experiences and exchange new ways to empower teams, deliver quality software and drive innovation for responsible tech.

Explore how at Thoughtworks, we are making tech better, together.

Tickets are now available and all proceeds will be donated to Indigitek, a not-for-profit organisation that aims to create technology employment pathways for First Nations Peoples.

Click the button below to register and get your ticket for the Melbourne, Sydney or Brisbane event



It's all about Webinars.

Marketing budgets are now focused on Webinars combined with Lead Generation.

If you wish to promote a Webinar we recommend at least a 3 to 4 week campaign prior to your event.

The iTWire campaign will include extensive adverts on our News Site and prominent Newsletter promotion and Promotional News & Editorial. Plus a video interview of the key speaker on iTWire TV which will be used in Promotional Posts on the iTWire Home Page.

Now we are coming out of Lockdown iTWire will be focussed to assisting with your webinars and campaigns and assistance via part payments and extended terms, a Webinar Business Booster Pack and other supportive programs. We can also create your adverts and written content plus coordinate your video interview.

We look forward to discussing your campaign goals with you. Please click the button below.


David M Williams

David has been computing since 1984 where he instantly gravitated to the family Commodore 64. He completed a Bachelor of Computer Science degree from 1990 to 1992, commencing full-time employment as a systems analyst at the end of that year. David subsequently worked as a UNIX Systems Manager, Asia-Pacific technical specialist for an international software company, Business Analyst, IT Manager, and other roles. David has been the Chief Information Officer for national public companies since 2007, delivering IT knowledge and business acumen, seeking to transform the industries within which he works. David is also involved in the user group community, the Australian Computer Society technical advisory boards, and education.

Share News tips for the iTWire Journalists? Your tip will be anonymous




Guest Opinion

Guest Interviews

Guest Reviews

Guest Research

Guest Research & Case Studies

Channel News