|
|
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:
|
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.
