AdapTable Version:

Framework:

Importing Data into AdapTable

Summary

Data Import allows run-time users to update data in AdapTable in a single workflow
The imported data can update existing rows, provide new rows or populate the entire grid
The Data Import wizard facilitates the import process via a series of steps, including Column matching
Validation is also available to ensure that the data being imported meets custom business rules

AdapTable enables data to be imported into AG Grid at run-time.

This data can be imported in 3 forms:

JSON file
CSV file
Text (i.e. copy and paste)

The data can consist of either updates to existing rows in the Grid, or details of new rows.

Hint

The AdapTable-provided handlers for CSV and JSON files can be replaced, and / or extended, by custom file handlers

Updating Data

The most common use case is when the data being imported overrides that which is already in the Grid.

Data Import - Update Rows

Fork Fork

This example shows how to use Data Import to update existing rows in AdapTable
Download either updateframeworks.json or updateframeworks.csv files - which both contain (the same) changes to the first 2 rows (react and angular)
Then upload that file in the Data Import wizard
We have added Flashing Cells to the grid (and increased duration to 3 seconds) so you can see the changes to the first 2 rows

Try It Out

Click the Import Data button in the Dashboard (or use a menu option) to open the Wizard
In the first step load one of the 2 files and click "Import"

Adding Whole Rows

The imported data can also consist of new rows which AdapTable will add to the grid.

Note

It is possible to import data which contains both new rows and updates to existing rows

Data Import - Add Rows

Fork Fork

This example shows how to use Data Import to add new rows into AdapTable
Download either addframeworks.json or addframeworks.csv files - which both contain (the same) 2 rows to add
Then upload that file in the Data Import wizard
We have added a ROW_ADDED Row Change Alert with a highlight row behaviour to show the rows that were added

Try It Out

Click the Import Data button in the Dashboard (or use a menu option) to open the Wizard
In the first step load one of the 2 files and click "Import"

Importing All Data

Data Import can also be used to populate an empty Grid with a full set of data.

Data Import - Adding all data

Fork Fork

This example shows how to use Data Import to all data rows into AdapTable - the grid opens with no data provided
Download either allframeworks.json or allframeworks.csv files - which both contain the same data to add, and upload the file in the Data Import wizard
Note: the Grid is fully populated including 3 Calculated Columns which are evaluated using the imported data

Try It Out

Click the Import Data button in the Dashboard (or use a menu option) to open the Wizard
In the first step load one of the 2 files and click "Import"

import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Data Import - Import All Data',
  initialState: {
    Dashboard: {
      ModuleButtons: ['DataImport'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Export'],
        },
      ],
    },

    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'github_stars',
            'language',
            'license',
            'github_watchers',
            'has_projects',
            'open_issues',
            'closed_issues',
            'open_prs',
            'closed_prs',
            'subscribersRatio',
            'open-total-issue-ratio',
            'open-total-pr-ratio',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          FriendlyName: 'Subscribers Ratio',
          ColumnId: 'subscribersRatio',
          Query: {
            ScalarExpression: '[github_stars] / [github_watchers]',
          },
          CalculatedColumnSettings: {
            ColumnTypes: ['first'],
            Filterable: true,
            Groupable: true,
            DataType: 'number',
          },
        },
        {
          FriendlyName: 'Issues Open/Total Ratio',
          ColumnId: 'open-total-issue-ratio',
          Query: {
            ScalarExpression:
              '[open_issues_count] / ([open_issues_count] +[closed_issues_count])',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          FriendlyName: 'PR Open/Total Ratio',
          ColumnId: 'open-total-pr-ratio',
          Query: {
            ScalarExpression:
              '[open_pr_count] / ([open_pr_count] + [closed_pr_count])',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-subscribersRatio',
          Scope: {
            ColumnIds: [
              'subscribersRatio',
              'open-total-issue-ratio',
              'open-total-pr-ratio',
            ],
          },

          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              FractionDigits: 2,
            },
          },
        },
      ],
    },
  },
};

Column Matching

The Import Data wizard attempts to match all the columns in the inputted data against those in the Grid's Column definitions.

This is done by checking the column in the header of the input file against an existing ColumnId or FriendlyName.

Note

The logic is that first AdapTable will try to find a Column where the columnId matches that in the input source
If nothing is found, then it will try to match using a Column friendlyName (aka the Header)

If there are no matches AdapTable will display a dropdown with a list of all the current columns so the user can pick the correct one.

Data Import - Matching Columns

Fork Fork

This example shows how the Data Import will try to match Columns automatically, and allow the user to provide those which cannot be matched
Download either the columnmatchingframeworks.json or the columnmatchingframeworks.csv file
Both files contain the same 2 rows to update as in the Updating Rows demo but with some columns in the import file not using the columnId:
- Language and Licence are both Friendly Names / Headers
- Stars and Watchers dont match either Column Id or Friendly Name
In all 4 cases the Columns section of the Import Data wizard asks the user to supply the relevant column

Try It Out

Click the Import Data button in the Dashboard (or use a menu option) to open the Wizard
In the first step load one of the 2 files and click "Import"
In the Columns step of the Wizard, supply the names of the columns that could not be matched automatically

Validating Import Data

The data file can be validated before it is imported to make sure that it fits with business requirements.

Note

This is separate to any Data Change Alerts which fire after the data has been imported into the Grid

Data Validation is done using the validate function property in Data Import Options.

validate

DataImportValidationError[]

Function to validate the Imported Data

DataImportValidationError[]

Use this function to validate the data being imported into AG Grid via the Data Import wizard.

It allows for bad data to be rejected before it gets added to the Grid.

The function receives a DataImportValidateContext and returns a DataImportValidationError array.

The DataImportValidateContext object contains just the row data being imported:

Property	Description
rowData	Imported Row Data
adaptableContext	Custom application Context provided in `AdaptableOptions.adaptableContext`

The DataImportValidationError object includes the Column where validation fails and an error message:

Property	Description
columnId	Column which contains the Error
error	The validatoin error text

const adaptableOptions: AdaptableOptions = {
  dataImportOptions = {
    validate: (context: DataImportValidateContext) => {
      const rowData = context.rowData;
      return rowData.license !== 'MIT License' && rowData.license !== 'Other'
        ? [
            {
              columnId: 'license',
              error: 'License must be "MIT License" or "Other"',
            },
          ]
        : [];
    },
  },
};

Data Import - Validation

Fork Fork

This example shows how to validate the data in Data Import
Download either updateframeworks.json or updateframeworks.csv files - which both contain (the same) changes to the first 2 rows (react and angular)
But this time we have added a Validation rule that the License must be either MIT License or Other
Then upload that file in the Data Import wizard

Expand to the validate function

 dataImportOptions: {
  validate: (context: DataImportValidateContext) => {
    const rowData = context.rowData;
    return rowData.license !== 'MIT License' && rowData.license !== 'Other'
      ? [
          {
            columnId: 'license',
            error: 'License must be "MIT License" or "Other"',
          },
        ]
      : [];
  },
},

Try It Out

Click the Import Data button in the Dashboard (or use a menu option) to open the Wizard
In the first step load one of the 2 files and click "Import"
Go to the Validation step and note that the License column contains an error in each row
Change one of the values to "Other" and the other "MIT License" and note that this passes validation and you are able to import

Importing Data into AdapTable

(Recorded with AdapTable v16.2)

FAQ

Does the imported data need to be on contiguous rows? No, you can update any rows in the Grid; there is no need for them to form a "block"

PreviousTechnical Reference NextConfiguring Importing

Framework: