Monday, 31 December 2012 16:43

Roll your own business intelligence

By

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.zip gm-report.xlsx

blat -to manager@company.com -subject "Gross margin report" -attach gm-report.zip -body "Gross margin report attached" -debug -log blat.log

del gm-report.xlsx

del gm-report.zip

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.zip 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 https://7-zip.org/download.html

blat -to manager@company.com -subject "Gross margin report" -attach gm-report.zip -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 https://www.blat.net/

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

del gm-report.xlsx

del gm-report.zip

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\twww.itwire.com - The Wired CIO");

            Console.WriteLine();

            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");

                return;

            }

            var inputFile = args[0];

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

            var outputFile = args[1];

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

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

            var streamReader = new StreamReader(inputFile);

            var sqlStatement = streamReader.ReadToEnd();

            streamReader.Close();

            streamReader.Dispose();

            var d = new DataTable();

            var db = new SqlConnection(connStr);

            db.Open();

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

            var rdr = cmd.ExecuteReader();

            d.Load(rdr);

            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)

            {

                rowNumber++;

                Console.Write(".");

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

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

            }

            Console.WriteLine();

            package.Save();

            package.Dispose();

            d.Dispose();

            rdr.Close();

            cmd.Dispose();

            db.Close();

        }

    }

}

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 https://epplus.codeplex.com/. 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.

 


Subscribe to Newsletter here

NEW OFFER - ITWIRE LAUNCHES PROMOTIONAL NEWS & CONTENT

Recently iTWire remodelled and relaunched how we approach "Sponsored Content" and this is now referred to as "Promotional News and Content”.

This repositioning of our promotional stories has come about due to customer focus groups and their feedback from PR firms, bloggers and advertising firms.

Your Promotional story will be prominently displayed on the Home Page.

We will also provide you with a second post that will be displayed on every page on the right hand side for at least 6 weeks and also it will appear for 4 weeks in the newsletter every day that goes to 75,000 readers twice daily.

POST YOUR NEWS ON ITWIRE NOW!

PROMOTE YOUR WEBINAR ON ITWIRE

It's all about Webinars.

These days our customers Advertising & Marketing campaigns are mainly focussed on Webinars.

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

The iTWire campaign will include extensive adverts on our News Site itwire.com and prominent Newsletter promotion https://www.itwire.com/itwire-update.html and Promotional News & Editorial.

For covid-19 assistance we have extended terms, a Webinar Business Booster Pack and other supportive programs.

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

MORE INFO HERE!

BACK TO HOME PAGE
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.

BACK TO HOME PAGE

ZOOM WEBINARS & ONLINE EVENTS

GUEST ARTICLES

VENDOR NEWS

Guest Opinion

Guest Interviews

Guest Research & Case Studies

Channel News

Comments