sql-csv

SQL like query on CSV and other tabular data

CSV files are ubiquitous in the programming world and most data interchange is done using the CSV format. One problem however is that you cannot quickly check for certain rows depending on a particular field criteria. As an example, say you need to find all the rows from a CSV file that has a particular value in a field. The most probable path would be to import the CSV in a spreadsheet and then process it to find the particular information.

In this post we will look into a command-line tool that allows you to query CSV data with SQL like syntax. q – Text as Data is a command line tool that allows direct execution of SQL-like queries on CSVs/TSVs (and any other tabular text files). You can look at q as a meta-tool which provides access to all the data-related tools that SQL provides (e.g. expressions, ordering, grouping, aggregation etc.), but on tabular data files.
(more…)

Read More

data-cleaning

Data cleaning in PHP applications

One of the important tasks in any web application is proper sanitization and standardization of data. Any data stored in a database should be in a standardized format, specially data that comes from a variety of sources.

Scrubbers or data cleaners are an important part of the data transformation process. Whenever you are involved in some data import or export process, data scrubbers can help you clean and standardize your data elements before storing.
(more…)

Read More

descriptive stats

Calculating descriptive statistics in MySQL

Descriptive statistics can be quite useful for simple analysis of records in a database. For example, to calculate average numbers of sales or products for a particular duration, or the Variance of sales for a month etc.

We can easily calculate standard descriptive statistic measures in MySQL such as MEAN, SUM, STANDARD DEVIATION, VARIANCE, MIN and MAX using built-in functions. Below is a sample student table with student id and scores which we will use for calculating descriptive statistical measures.
(more…)

Read More

Migrating legacy data to WordPress

With an increasing number of users migrating to WordPress, moving data from an existing CMS to WordPress has become an essential process in the transition. Although migrating small databases from another CMS can be done in a few hours, large databases can take days or even weeks.

In one of my recent endeavors of the same, I had to migrate a relatively small user database from an old CMS to WordPress. Although a small task, some points tripped me and took a little longer than planned. Below are some of the points and recommended tools to consider when migrating existing databases to WordPress.
(more…)

Read More