Category: Microsoft SQL Server
-
Using Excel 2016 to unpivot data
Normal people, by which I mean those who don’t process data all day will often create data that is present in a crosstab table. This is a poor structure for storing and processing information. We say that its “denormalised” because the three different columns “Europe”, “USA”, “Asia” contain information of the same type – “budget…
-
Create the Union of two tables in Excel revisited
In 2013 I published a technique to create the union of two tables in Excel. This post is still very popular and so I am revisiting this topic. Excel 2016 (and possibly Excel 2013 but I have not tested) has this capability built-in and the new method should always be preferred to the one that…
-
Recreate views after changing datatypes in referenced tables
I recently came across a problem that was difficult to diagnose. After I had changed the datatype in a table from varchar(256) to varchar(512) my Access 2013 reports show data truncated to 256 characters. It turned out that the views that I’d created against the base table were truncating the field. After changing the datatype…
-
Humour: Excel and big data
As consultants our job is to help the user and unlike the poor second line support person we usually have the time and infrastructure to do it. Here is an amusing rant from one such Sys Admin in The Register. The scenario will be familar to anyone who has ever done a data warehouse project…
-
How to add a row number to an Excel table
Excel tables provide a lot of support for data processing and the use of structured references can improve readability. We want to number rows in an Excel table and we can do it really easily using structured references. =ROW(Table01[@])-ROW(Table01[[#Headers],[RowId]]) We simply find the row number of the current cell [@] using ROW and then subtract…
-
A T-SQL SPLIT function
When taking existing data to put into a data warehouse one hopes that it is nicely formatted. This is seldom the case and recently I was faced with the problem that single field contained a rather dirty list. First I cleaned the list using REPLACE to replace the variety of delimiters with comma. Then I…
-
SQL Server Import Wizard cannot import fields containing more than 255 characters
I have been importing data from Excel to SQL Server using the SQL Server Import Wizard. Usually there is no problem but I have found a bug. 1. To reproduce: Try to import a column containing more than 255 characters text, optimistically set the destination datatype to nvarchar(max) and the failure options to “ignore” on…
-
How to create a calendar table with hourly grain
Calendar Tables – Why You Need One. <credit: This section was copied from Made2Mentor> What is a Date (Calendar) Table? For the purposes of this article a Date Table is a dedicated table containing a single record for each day in a defined range. They include fields with descriptive attributes for each day such as…
Dr James Bayley 