Manage Messy Data With Wijmo’s JavaScript DataGrid, FlexGrid

There are many situations where we encounter messy or inconsistent data, whether it's being merged from legacy systems, entered incorrectly by users, or exported, imported, and passed through multiple spreadsheets, accumulating multiple formatting changes along the way.

Regardless of its source, messy data forces developers to do extra cleanup work, makes it harder to build clean user interfaces, and complicates the delivery of polished features.

That's where Wijmo's FlexGrid really shines.

FlexGrid isn't just another JavaScript data grid; it's a fast, flexible, and highly customizable tool built to handle the quirks and imperfections of real-world datasets. From formatting and validation to editing and data shaping, FlexGrid helps you transform messy data into clean, consistent, and easy-to-work-with information.

In this article, we'll cover the following topics:

• Why Messy Data Is So Hard to Work With
• How FlexGrid Helps You Make Sense of Messy Data
• Automatically Bind Data to Any JSON Structure
• Clean Up Dirty Data With Formatters and CellTemplates
• Validate Data Directly in the Grid

Ready to get started? Download Wijmo Today!

Why Messy Data Is So Hard to Work With

You've most likely experienced a moment when you've been given access to a dataset and see that there was little to no data validation when collecting the data. Some fields are neatly filled in, while others are in the incorrect format; some have additional whitespace, and some sections appear to have been merged from another dataset. 

Over the life of a project, these inconsistencies add up and make it more complicated to build what should be simple front-end components. Instead of a straightforward API call to attach the data to your component, you're stuck trying to write functions to organize the data so you can display it to your users in a readable format.

This problem can extend beyond the visual aspect. Messy data can disrupt sorting and filtering, skew analytics, and slow down development, as you may now need to modify your grid functionality to accommodate these anomalies.

Thankfully, FlexGrid helps you visualize, clean, and standardize messy data with ease.

How FlexGrid Helps You Make Sense of Messy Data

Below are some of the best FlexGrid features for cleaning and understanding chaotic datasets.

Automatically Bind Data to Any JSON Structure

Not everyone is lucky enough to have a consistent data structure; fortunately, Wijmo's FlexGrid doesn't require one. The JavaScript DataGrid can bind to:

• Arrays of loosely structured objects
• Nested or optional fields
• Variant data types (numbers, strings, nulls)

Let's take the following data sample as an example:

[
  { "id": 1, "name": "  Alice Johnson ", "age": "27", "signup_date": "2024-01-05", "email": "alice@@example.com", "status": "Active  " },
  { "id": "002", "fullName": "Bob    Smith", "age": 31, "signupDate": "1/7/24", "email": "bob.smith@example", "status": "active" },
  { "ID": 3, "Name": "carol O'Conner", "Age": "32", "signup_date": "07-12-2024", "Email": "carol.oconnor@example.com", "Status": " inactive" },
  { "id": 4, "name": "David\tLee", "age": 29, "signup_date": "2024/02/25", email: "david.lee@example.com", "status": "Inactive" }
]

As you can see, there are several issues with the sample data: property names vary across data points, fields contain extra whitespace, and dates, numbers, and email addresses are formatted either differently or incorrectly.

FlexGrid allows you to map incorrect or inconsistent property names into clean, normalized columns using column binding during FlexGrid initialization:

var theGrid = new FlexGrid('#theGrid', {
  autoGenerateColumns: false,
  itemsSource: data,
  columns: [
    { header: "ID", binding: "id", cellTemplate: (ctx) => {
        if(ctx.item.id) {
            return ctx.item.id;
        } else {
            return ctx.item.ID;
        }
    }},
    { header: "Name", binding: "name", cellTemplate: (ctx) => {
        if(ctx.item.name) {
            return ctx.item.name;
        } else if(ctx.item.fullName) {
            return ctx.item.fullName;
        } else {
            return ctx.item.Name;
        }
    }},
    { header: "Age", binding: "age", cellTemplate: (ctx) => {
        if(ctx.item.age) {
            return ctx.item.age;
        } else {
            return ctx.item.Age;
        }
    }},
    { header: "Signup Date", binding: "signup_date", cellTemplate: (ctx) => {
        if(ctx.item.signup_date) {
            return ctx.item.signup_date;
        } else {
            return ctx.item.signupDate;
        }
    }},
    { header: "Email", binding: "email", cellTemplate: (ctx) => {
        if(ctx.item.email) {
            return ctx.item.email;
        } else {
            return ctx.item.Email;
        }
    }},
    { header: "Status", binding: "status", cellTemplate: (ctx) => {
        if(ctx.item.status) {
            return ctx.item.status;
        } else {
            return ctx.item.Status;
        }
    }}
  ]
});

You can see that, for each column, we're binding to a single value and then initializing a method called cellTemplate, which gets called whenever a column in a cell is created. We then check whether, for example, the current cell being created in the ID column has a property on the element called id. If it does not, we assume the property is instead named ID and apply that value to the cell.

We do this for each of the columns and each of the multiple field values that should appear in that column. Now, when we run the application, we should see the following FlexGrid in the browser:

And just like that, we're displaying data properly that has mixed field names. Another thing you'll notice is that Wijmo's FlexGrid has also removed all the additional whitespace that was included with the names automatically, without requiring any developer implementation.

Clean Up Dirty Data With Formatters and CellTemplates

FlexGrid's formatting and itemFormatter method capabilities enable you to perform tasks such as trimming whitespace, standardizing casing, correcting dates, and highlighting any inconsistencies that may appear in the data.

For example, in our data, the Status column displays both Active and Inactive with different capitalization (a formatting issue you typically want to avoid). So, we can use Wijmo's itemFormatter to fix this in the grid automatically:

var theGrid = new FlexGrid('#theGrid', {
  ...,
  itemFormatter: (panel, r, c, cell) => {
    if(panel.columns[c].binding === "status") {
      cell.textContent = cell.textContent.trim().toUpperCase();
    }
  }
});

Now, when the FlexGrid loads data, each element in the Status column will be examined, have excess whitespace trimmed, and the string will be set to uppercase, ensuring each cell in that column is formatted consistently, even though the data itself was not fixed before being loaded into the grid.

Next, we'll address the dates. As you can see, the dates are provided to us in various formats. To resolve this formatting issue, we'll jump into the cellTemplate method that we set up for signup dates and make some changes:

{ header: "Signup Date", binding: "signup_date", cellTemplate: (ctx) => {
    if(ctx.item.signup_date) {
        return new Date(ctx.item.signup_date).toLocaleDateString();
    } else {
        return new Date(ctx.item.signupDate).toLocaleDateString();
    }
}}

Since these are in different formats, we will pass in the value to a new Date() object and then apply the toLocaleDateString() method so that they're all in the same readable format.

Now, running the sample will produce the following grid:

As you can see, all the dates are properly formatted and display what we expect.

The next task that we'll complete at this point is to fix the ID column. As you can see, one of the fields is a string, with two leading zeros ahead of the two. This can be fixed by simply converting all of those fields to numbers:

{ header: "ID", binding: "id", cellTemplate: (ctx) => {
    if(ctx.item.id) {
        return Number(ctx.item.id);
    } else {
        return Number(ctx.item.ID);
    }
}}

And with that, when we run the application, our grid should appear as displayed below:

Now there's one more change to be made before moving on. As you can see, there is a name without the first letter capitalized, and we need to remedy that. Add the following code to the cellTemplate function for the Name column:

{ header: "Name", binding: "name", cellTemplate: (ctx) => {
    if(ctx.item.name) {
        return String(ctx.item.name).charAt(0).toUpperCase() + String(ctx.item.name).slice(1);
    } else if(ctx.item.fullName) {
        return String(ctx.item.fullName).charAt(0).toUpperCase() + String(ctx.item.fullName).slice(1);
    } else {
        return String(ctx.item.Name).charAt(0).toUpperCase() + String(ctx.item.Name).slice(1);
    }
}}

This evaluates each element in the Name column, ensures the first letter is capitalized, and then rebuilds the string.

When we run the application, we should see the following in the browser:

Validate Data Directly in the Grid

We've cleaned up most of our messy data, but the Email column remains improperly formatted. In some cases, however, you may not want to modify the values directly, but instead flag them so that the user can see that there is an issue with the data. That's what we'll do now.

The first thing that we'll need to do is set up a function to properly validate our email addresses, which we'll call validateEmail():

function validateEmail(email) {
  return String(email).toLowerCase().match(/^[^\s@]+@[^\s@]+\.[^\s@]+$/);
}

This checks the email addresses against the regular expression that we've set up. Note: this regular expression simply checks that there are no duplicate @ symbols. If you require more complex validation, you'll need to edit this regular expression.

Next, inside of itemFormatter, we'll add another if statement to check against the email-bound column using our validateEmail function:

itemFormatter: (panel, r, c, cell) => {
    ...
    if(panel.columns[c].binding === "email") {
        console.log(cell.textContent);
        if(!validateEmail(cell.textContent)) {
            cell.classList.add("invalid-cell");
        }
    }
}

Now, if the validateEmail expression flags a cell value as invalid, it will add the invalid-cell class to that cell.

The final thing we need to do is set up our CSS class:

.invalid-cell {
  background-color: gray;
}

Now, when we run the application, we should see the following:

Conclusion

Just like that, we've used Wijmo's JavaScript DataGrid, FlexGrid, to automatically clean up any of the messy data that was loaded. From trimming strings to mutating data and performing validation checks, Wijmo makes it easy for developers to manage their data and display it in a way that is helpful and beneficial to users.

Ready to check it out? Download Wijmo Today!

Happy coding!