skip to Main Content

Data Wrangling

Data Wrangling - Getting Your Data Into Shape

Moving data between systems can be a mess of incompatible files and inconsistent data layouts. Our data wrangling tools can automatically wrangle your data into a common format ready for importing into your systems (AKA Extract, Transform, Load or ETL). No more manual copying and pasting of data. No more spreadsheets full of formulas summarising data from multiple sources.

Get in touch
Data Wrangling (ETL)

Data Wrangling

Data Wrangling Tools to Get Your Data Into Shape

Data Wrangling (ETL)

Moving data between systems can be a mess of incompatible files and inconsistent data layouts. Our data wrangling tools can automatically wrangle your data into a common format ready for importing into your systems (AKA Extract, Transform, Load or ETL). No more manual copying and pasting of data. No more spreadsheets full of formulas summarising data from multiple sources.

Get in touch >

Wrangling Your Data into Shape

Getting data from one system to another can be a tricky process with countless problems to overcome (extracting data, overcoming data incompatibilities, data cleansing, importing the results etc). Getting these processes right and running smoothly, can save businesses fortunes and increase data accuracy.

Data wrangling is also known by its 3 main stages, Extraction, Transformation and Loading (ETL). And we can look after all 3 stages for you with automated tools.

Extraction

The first stage in the data wrangling (or ETL) process is the extraction of the source data. This could be from one source or multiple different sources of differing data types (spreadsheets, databases etc)

Sources of data we can automate the importing and extraction from include;

Data From Spreadsheets

This is a faster and far more accurate method than cutting and pasting data from one spreadsheet to another, or referencing external spreadsheets in your formulas. Queries are direct, fast and automated.

Data from Databases

We can use SQL queries to query databases either on your premises or in the cloud. This can then be integrated with any other data you have. Most databases can be queried this way.

Data from APIs

Many services (e.g. Companies House) allow you to query their data using their API tools. This can then be used to update your existing records (e.g. adding a company address to your data using the data held in Companies House).

Data from Other Files

A lot of data is provided in non-standard format and will have file extensions specific to the software that exported it. The vast majority of these are renamed csv or xml files which can be parsed and have data extracted from.

Web Scraping

Web scraping tools can visit and navigate a website to find and extract the data you need. If the data you need is on a site that needs a logon to access it can also automate the logging on process as well.

Transformation

The second stage is the key to the whole ETL process. This is where we take the data that has been extracted and transform it into something the receiving tool can import and/or use.

At this stage, any number of processes can be applied to the data to get it into shape. Including;

  • Data Validation – Check your data meets a given criteria and quarantine or flag any that don’t.
  • Data Cleansing – The data we extract may not be clean and need cleansing (fields commonizing, formatting correcting etc).
  • Correcting Formatting – Make sure everything is in the correct format throughout e.g. the same date format is used throughout.
  • Merging Fields – Combining multiple fields into a single field.
  • Splitting Fields – If a complete address is stored in one field, it can be split into its component parts and stored in different fields.
  • Unit Conversion – If two systems use a different unit of measurement, the transformation process can convert numbers into the right units (e.g. Inches into millimetres).
  • Currency Conversion – Conversions can be carried out using current exchange rates or historical exchange rates can be looked up and used.
  • Adding Calculated Figures – Calculating a new figure based on the other data provided. E.g. calculating an items volume if only its length, width and height are provided.

Loading

The final step of the process is loading the transformed data into its destination system. This uses the same processes as the extraction process but in reverse, so these include;

Putting Data Into Spreadsheets

Data can be exported to one or more spreadsheets or CSV files ready for processing or use in other tools.

Putting Data Into Databases

Similarly data can be inserted directly into your database (either on-premises or cloud-hosted).

Uploading Data via APIs

If you need to upload data to a third-party website we can make use of any API they have to automatically upload the data.

Putting Data Into Other Files

If your system needs data in a non-standard format that is based on text of xml format we can automate that as well.

Call 02921 29 2235

Outside UK Call +44 2921 29 2235

Your Extra Peace of Mind

To keep your spreadsheets up and running, Excel-Expert offers a unique and free after-sales support package. Any snags found in the first 60 days are fixed immediately and for free. Should you find any bugs in our programming in the first 3 years they are also fixed for free.

60 Days of Free Tweaking

When you buy a new house, you get to produce a snagging list for the builder to put right before you move in. We do the same. Once you receive your completed project, you have 60 days to report any snags and we will tweak/fix them for free.

3 Years of Free Bug Fixing

Our coding carries a three-year warranty, and any bugs will be fixed free during that period. Just get in touch and will fix any issue as quickly as we can. (Just so you know, no one has ever needed to use this warranty).

Back To Top