Excel VBA merging rows

I’ve been given an excel spread sheet full of data to be loaded into a new table, however rather than being send as a multi column table the data was split into multiple key value pairs.

A simple bit of copy and pasting got it all into a multi column table, but left thousands of duplicate rows. The simplest way to fix it was a quick bit of VBA to merge the rows and remove the duplicates, before importing it into SQL. Example Code below:

Sub MergeRows()
Dim r As Long
Dim p As Long
Dim duplicates As Long
Dim keyCell As Variant
Dim rng As Range
Dim col_key As Long
Dim col_title As Long
Dim col_author As Long
Dim col_publisher As Long
Dim col_price As Long
' set columns
col_key = 1
col_title = 2
col_author = 3
col_publisher = 4
col_price = 5

On Error GoTo EndMacro

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' Select Cell A1 and set range to table
Cells(1, 1).Select
Set rng = Application.Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(ActiveCell.Column))

Application.StatusBar = "Processing Row: " + Format(rng.Row, "#,##0")

' initialise duplicate count
duplicates = 0

' step from end of range to the start
For r = rng.Rows.Count To 2 Step -1

If r Mod 500 = 0 Then
Application.StatusBar = "Processing Row: " + Format(r, "#,##0")
End If

' get key value from current row
keyCell = rng.Cells(r, col_key)

' set previous row
p = r - 1

' check if key value = key value on previous row
If rng.Cells(p, col_key) = keyCell Then
' keys match
duplicates = duplicates + 1

' check each column and merge cell to previous row if previous row/cell is blank
If (IsEmpty(rng.Cells(p, col_title)) And Not IsEmpty(rng.Cells(r, col_title))) Then
rng.Cells(p, col_title) = rng.Cells(r, col_title)
End If

If (IsEmpty(rng.Cells(p, col_author)) And Not IsEmpty(rng.Cells(r, col_author))) Then
rng.Cells(p, col_author) = rng.Cells(r, col_author)
End If

If (IsEmpty(rng.Cells(p, col_publisher)) And Not IsEmpty(rng.Cells(r, col_publisher))) Then
rng.Cells(p, col_publisher) = rng.Cells(r, col_publisher)
End If

If (IsEmpty(rng.Cells(p, col_price)) And Not IsEmpty(rng.Cells(r, col_price))) Then
rng.Cells(p, col_price) = rng.Cells(r, col_price)
End If

'delete current row
rng.Rows(r).EntireRow.Delete
End If
Next r

EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.StatusBar = "Duplicates merged: " + Format(duplicates, "#,##0")
End Sub
Advertisements
Posted in Development | Tagged , , | Leave a comment

Facebook Spam

Facebook are filling my phone app with so many updated from pages I haven’t liked, that the app is becoming useless. They obviously know I’m a software developer, but this is terrible spamming. So much that I don’t see updates that I actually want to see from friends and pages that I have liked.

Today’s updates from unliked pages:
O’Reilly Media
opensource.com
at&t developer program *13
appCoda
Atmel Corporation
Smashing Magazine * 4
Lean startup circle
OMG! Chrome * 2
The Hacker News * 4
SQL Server Geeks *
Media Temple
SQL Authority

Posted in Uncategorized | Tagged | Leave a comment

Web Dev Gotchas–Personal

This post is just for me, because I’ve forgotten more than once.

1. Until I update my scaffolding to work with VS2103, it doesn’t. Create the projects in Vs2012

2. WebApi – Json camelCase and serialising child objects :

var jsonFormatter = GlobalConfiguration.Configuration.Formatters.OfType<JsonMediaTypeFormatter>().First();
jsonFormatter.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();
jsonFormatter.SerializerSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;

3. Repositories – including child objects

using System.Collections.Generic;
using System.Linq;
using System.Data.Objects;
public override ICollection<Book> GetAll()
{     BookReviewsContext context = (BookReviewsContext)DataContext;     var books = context         .Books         .Include("Authors")         .Where(x => !x.IsDeleted)         .ToList<Book>();     return books;
 
}
Posted in Development, Json, Repositories, Web Development, WebApi | Tagged , , , | Leave a comment

Entity Frameworks, WebApi, JSON.Net and Serializing Circular References – Better fix

As an improvement on yesterday problem and solution, there is a simple change to the JSON.Net settings which solves this issue, just add this into Global.asax:

JsonSerializerSettings settings = new JsonSerializerSettings
{
    PreserveReferencesHandling = PreserveReferencesHandling.Objects,
    Formatting = Formatting.Indented
};
 
HttpConfiguration config = GlobalConfiguration.Configuration;
config.Formatters.JsonFormatter.SerializerSettings = settings;
Posted in Uncategorized | Leave a comment

Entity Frameworks, WebApi, JSON.Net and Serializing Circular References

While Entity Frameworks handles circular references between models very well, putting them through a WebApi web service and serializing them, Json.Net starts throwing errors.

The quickest way around this is to rebuild the objects and miss out the references from the child back to the parent.

 
public virtual HttpResponseMessage Get()
{
var data = ADRepository.GetAllocatedApplications();

// do this to get around the bloody annoying Json Circular References Issue
List result = data.Select(x =&gt; new ActiveDirectoryGroup
{
Id = x.Id,
Name = x.Name,
IsDeleted = x.IsDeleted,
LastUpdated = x.LastUpdated,
LastUpdatedBy = x.LastUpdatedBy,
Applications = x.Applications.Select(y =&gt; new Application
{
Id = y.Id,
Name = y.Name,
CommandLine = y.CommandLine,
IsDeleted = y.IsDeleted,
LastUpdated = y.LastUpdated,
LastUpdatedBy = y.LastUpdatedBy
}).ToList()
}).ToList();

return Request.CreateResponse&lt;List&gt;(System.Net.HttpStatusCode.OK, result);
}

Posted in Uncategorized | Leave a comment

NuGet Package Restore problems

I was having some trouble with a new solution, I had added a few NuGet packages to various projects, and everything was working. I checked it into TFS and asked a colleague to review the code.
He got the code out of TFS and tried to run it, but ran into some problems, because not all of the NuGet packages had been restored. I had enabled the “Restore NuGet Packages on build” option, so I was a bit stumped.

After some investigation I discovered the problem. I had checked in the packages folder in the route of the solution. This obviously becomes write-protected, and interferes with the package restore. I closed the solution, removed the packages folder from source control, and reopened the solution and rebuilt. All the packages were downloaded and the solution ran without issue.

Posted in Development | Tagged , | Leave a comment

Windows Phone 8.1 Backup Issue

I have a Lumia 1020, running the 8.1 developers preview with GDR1. I’ve been having an issue trying to backup the the phone. The backup runs to about 97% and then stops with an error, this has happened multiple times.

To fix this, delete any previous backups, which apparently are not compatible with the new backup routine, then backup from scratch.

Posted in Windows Phone | Tagged , , , , | Leave a comment

MVC Scaffolding

MVC Scaffolding is a based on T4 Templating. It’s goal is to generate the framework for your code,  it’s extensible and customisable. The scaffolds included with the MVC build Controllers and Views, but it can do so much more.

How to install MvcScaffolding from NuGet

First create a new MVC project.

In the Package Manager Console, type install-package MvcScaffolding. You should see results similar to this:

PM> install-package MvcScaffolding
Attempting to resolve dependency 'T4Scaffolding'.
Attempting to resolve dependency 'T4Scaffolding.Core'.
Attempting to resolve dependency 'EntityFramework'.
Installing 'T4Scaffolding.Core 1.0.0'.
Successfully installed 'T4Scaffolding.Core 1.0.0'.
Installing 'T4Scaffolding 1.0.8'.
Successfully installed 'T4Scaffolding 1.0.8'.
Installing 'MvcScaffolding 1.0.9'.
Successfully installed 'MvcScaffolding 1.0.9'.
Adding 'T4Scaffolding.Core 1.0.0' to TestWeb2.
Successfully added 'T4Scaffolding.Core 1.0.0' to TestWeb2.
Adding 'T4Scaffolding 1.0.8' to TestWeb2.
Successfully added 'T4Scaffolding 1.0.8' to TestWeb2.
Adding 'MvcScaffolding 1.0.9' to TestWeb2.
Successfully added 'MvcScaffolding 1.0.9' to TestWeb2.

As you can see it resolves dependancies for T4Scaffolding and EntityFramework and will install them if necessary.

Creating a controller and views

Create a data model called Book :

public class Book
{
    [Key]
    public int Id { get; set; }
    [Required]
    public string Title { get; set; }
    public decimal Price { get; set; }
    public string Publisher { get; set; }
    public string ISBN { get; set; }
}

In the Package Manager Console type scaffold controller Book

PM> scaffold controller book
Scaffolding BooksController...
Added database context 'Models\TestWeb2Context.cs'
Added 'Books' to database context 'TestWeb2.Models.TestWeb2Context'
Added controller Controllers\BooksController.cs
Added Create view at 'Views\Books\Create.cshtml'
Added Edit view at 'Views\Books\Edit.cshtml'
Added Delete view at 'Views\Books\Delete.cshtml'
Added Details view at 'Views\Books\Details.cshtml'
Added Index view at 'Views\Books\Index.cshtml'
Added _CreateOrEdit view at 'Views\Books\_CreateOrEdit.cshtml'

This will create a new database context for the project, if one doesn’t already exist, and add a DbSet<> for the Book Model. It will then create a controller, and views for the standard CRUD operations.

Using Switches

Because we are doing this from the command line, we have more power to change things, using switches.

There are two types of command line switches:

  • Boolean Switches,  which for the  controller scaffold include Repository, Force, ForceMode, ReferenceScriptLibaries and NoChildItems. These default to false, and including them on a command line switches them on.
  • String Switches, which for the  controller scaffold include ControllerName, ModelType, DbContextType, Project, Layout, CodeLanguage, Area, ViewScaffolder, Layout, PrimarySectionName, SectionNames, TemplateFolders. These are values that are applied to the command like DbContextType to change the name of the DbContext class.

If you delete the existing context file from the Models folder and then in the Package Manager Console type:

scaffold controller Book –DbContextType LibraryDbContext

You should see this:

PM> scaffold controller Book –DbContextType LibraryDbContext 
Scaffolding BooksController...
Added database context 'Models\LibraryDbContext.cs'
Added 'Books' to database context 'TestWeb2.Models.LibraryDbContext'
Controllers\BooksController.cs already exists! Pass -Force to overwrite. Skipping...
Views\Books\Create.cshtml already exists! Pass -Force to overwrite. Skipping...
Views\Books\Edit.cshtml already exists! Pass -Force to overwrite. Skipping...
Views\Books\Delete.cshtml already exists! Pass -Force to overwrite. Skipping...
Views\Books\Details.cshtml already exists! Pass -Force to overwrite. Skipping...
Views\Books\Index.cshtml already exists! Pass -Force to overwrite. Skipping...
Views\Books\_CreateOrEdit.cshtml already exists! Pass -Force to overwrite. Skipping...
PM>

This will create the DbContext file with the name LibraryDbContext, and skip over the already created controller and views.

If rather than have the controller go straight to the database we would use the Repository pattern, then we can add the –repository switch. Also to force the controller to change to use the new repository we should use the –force switch.

If we want to use a repository add the switch –Repository

scaffold controller Book –DbContextType LibraryDbContext –repository –force

PM> scaffold controller Book –DbContextType LibraryDbContext –repository –force
Scaffolding BooksController...
LibraryDbContext already has a member called 'Books'. Skipping...
Added repository 'Models\BookRepository.cs'
Added controller Controllers\BooksController.cs
Added Create view at 'Views\Books\Create.cshtml'
Added Edit view at 'Views\Books\Edit.cshtml'
Added Delete view at 'Views\Books\Delete.cshtml'
Added Details view at 'Views\Books\Details.cshtml'
Added Index view at 'Views\Books\Index.cshtml'
Added _CreateOrEdit view at 'Views\Books\_CreateOrEdit.cshtml'
PM>

This will create the controller, views and DbContext file as before but will also create an interface IBookRepository and concrete implementation BookRepository.

    public class BookRepository : IBookRepository
    {
        LibraryDbContext context = new LibraryDbContext();

        public IQueryable<Book> All
        {
            get { return context.Books; }
        }

        public IQueryable<Book> AllIncluding(params Expression<Func<Book, object>>[] includeProperties)
        {
            IQueryable<Book> query = context.Books;
            foreach (var includeProperty in includeProperties) {
                query = query.Include(includeProperty);
            }
            return query;
        }

        public Book Find(int id)
        {
            return context.Books.Find(id);
        }

        public void InsertOrUpdate(Book book)
        {
            if (book.Id == default(int)) {
                // New entity
                context.Books.Add(book);
            } else {
                // Existing entity
                context.Entry(book).State = EntityState.Modified;
            }
        }

        public void Delete(int id)
        {
            var book = context.Books.Find(id);
            context.Books.Remove(book);
        }

        public void Save()
        {
            context.SaveChanges();
        }

        public void Dispose() 
        {
            context.Dispose();
        }
    }

    public interface IBookRepository : IDisposable
    {
        IQueryable<Book> All { get; }
        IQueryable<Book> AllIncluding(params Expression<Func<Book, object>>[] includeProperties);
        Book Find(int id);
        void InsertOrUpdate(Book book);
        void Delete(int id);
        void Save();
    }

It will also change the controller to use the repository:

 

   public class BooksController : Controller
    {
        private readonly IBookRepository bookRepository;

        // If you are using Dependency Injection, you can delete the following constructor
        public BooksController() : this(new BookRepository())
        {
        }

        public BooksController(IBookRepository bookRepository)
        {
            this.bookRepository = bookRepository;
        }

        //
        // GET: /Books/

        public ViewResult Index()
        {
            return View(bookRepository.AllIncluding(book => book.Authors));
        }
.
.
.
}

 

Posted in Development | Tagged , , | Leave a comment

Getting Started with Angular.js

I’ve been playing with angular.js for a couple of hours, and I’m pretty impressed. I’ve quickly put together a very simple trading screen, with only 80 lines of HTML and 57 of JavaScript.

The HTML contains a table for the pricefeed, a simple Ticket and a table for the trades, all are databound through Angular to Javascript objects. An angular directive “ng-show” causes the ticket to appear when a row is selected in the price feed. Another “ng-show” causes the list of trades to appear when a trade is made. “ng-click” is used to bind events to functions.
<!DOCTYPE html>
<html ng-app="">

<head>
  <link data-require="bootstrap-css@3.1.1" data-semver="3.1.1" rel="stylesheet" href="//netdna.bootstrapcdn.com/bootstrap/3.1.1/css/bootstrap.min.css" />
  <script data-require="angular.js@*" data-semver="1.3.0-beta.5" src="https://code.angularjs.org/1.3.0-beta.5/angular.js"></script>
  <script data-require="bootstrap@*" data-semver="3.1.1" src="//netdna.bootstrapcdn.com/bootstrap/3.1.1/js/bootstrap.min.js"></script>
  <link rel="stylesheet" href="style.css" />
  <script src="stockTrader.js"></script>
</head>

<body class="container">
  <div ng-controller="stockTraderController">
    <div class="panel panel-default">
      <div class="panel-heading">
        <h2>Price Feed</h2>
      </div>
        <table class="table table-striped table-bordered table-hover">
          <thead>
            <tr>
              <th>Symbol</th>
              <th>Company</th>
              <th>Bid Price</th>
              <th>Ask Price</th>
            </tr>
          </thead>
          <tbody>
            <tr ng-repeat="stock in stocks" ng-click="selectStock(stock)">
              <td>{{stock.symbol}}</td>
              <td>{{stock.name}}</td>
              <td>{{stock.bidPrice}}</td>
              <td>{{stock.askPrice}}</td>
            </tr>
          </tbody>
        </table>

    </div>
    <div class="panel panel-primary" ng-show="selectedStock">
      <div class="panel-heading">
        <h2>Ticket: {{selectedStock.name}}</h2>
      </div>
      <div class="panel-body">
        <div class="col-md-3">
          Bid Price: {{selectedStock.bidPrice}}
        </div>
        <div class="col-md-3">
          AskPrice : {{selectedStock.askPrice}}
        </div>

        <div class="col-xs-3">
          <button class="btn btn-success" ng-click="buy()">Buy</button>
        </div>
        <div class="col-xs-3">
          <button class="btn btn-danger" ng-click="sell()">Sell</button>
        </div>
      </div>
    </div>

    <div class="panel panel-success" ng-show="hasTrades()">
      <div class="panel-heading">
        <h2>Trades <span><button class="btn btn-success btn-small" ng-click="clearTrades()">Clear</button></span></h2>
        
      </div>
        <table class="table table-striped table-bordered table-hover">
          <thead>
            <tr>
              <th>Symbol</th>
              <th>Price</th>
              <th>Side</th>
            </tr>
          </thead>
          <tbody>
            <tr ng-repeat="trade in trades">
              <td>{{trade.symbol}}</td>
              <td>{{trade.price}}</td>
              <td>{{trade.side}}</td>
            </tr>
          </tbody>
        </table>
    </div>

  </div>

</body>

</html>

The JavaScript:

function stockTraderController($scope) {
  $scope.stocks = [
    new stock("MSFT", "Microsoft", 50, 49, 0),
    new stock("GOOG", "Google", 40, 39, 0),
    new stock("APPL", "Apple", 30, 29, 0)
  ];

  $scope.trades = [];

  $scope.selectedStock = null;

  $scope.buy = function() {
    var symbol = $scope.selectedStock.symbol;
    var price = $scope.selectedStock.bidPrice;
    var side = "Buy";
    $scope.trades.push(new trade(symbol, price, side));
  };

  $scope.sell = function() {
    var symbol = $scope.selectedStock.symbol;
    var price = $scope.selectedStock.askPrice;
    var side = "Sell";
    $scope.trades.push(new trade(symbol, price, side));
  };

  $scope.hasTrades = function() {
    var result = $scope.trades.length > 0;
    return result;
  };

  $scope.clearTrades = function() {
    $scope.trades = [];
  }

  $scope.selectStock = function(stock) {
    $scope.selectedStock = stock;
  };
}

function stock(symbol, name, bidPrice, askPrice, quantity) {
  this.symbol = symbol;
  this.name = name;
  this.bidPrice = bidPrice;
  this.askPrice = askPrice;
  this.quantity = 0;
  return this;
}

function trade(symbol, price, side) {
  this.symbol = symbol;
  this.price = price;
  this.side = side;
  return this;
}

 

 

image

A working version is at http://plnkr.co/VOa8x5

Posted in Development | Tagged | Leave a comment

Entity Framework and recreating the database when the model changes.

I’ve just started a new MVC5 project, and just couldn’t remember exactly how to do this.

So:

  1. Create a new project
  2. Create your models
  3. Create your DbContext
  4. Create a Database Initializer
  5. Add Database Initializer into Global.asax
public class ApplicationDbContextInitializer : DropCreateDatabaseIfModelChanges<ApplicationDbContext>
{
    protected override void Seed(ApplicationDbContext context)
    {
        base.Seed(context);
    }
}
Database.SetInitializer<ApplicationDbContext>(new ApplicationDbContextInitializer());

 

Posted in Development | Tagged , | Leave a comment