Date Columns

You can add a column to your importer directly through the UI of your Fuse account, or you can do so in code using the documentation below.

The Date column type validates that input is of a specific date format.

addColumn Function

The addColumn function accepts an object as an argument.

internal_key (String) - required: The internal key used to access a column’s value.
label (String) - required: The user-facing column label that is shown in the interface.
column_type (String) - required: Should be “date”.
required (String) - required: Whether or not the column is required.
pattern (String) - required: Must be one of these patterns.
position (Optional): The position or order of the column.
unique (Optional): Whether values should be unique.
validations (Optional): An array of built-in data validations. See Built-in Date Validations.
transformations (Optional): An array of built-in data transformations. See Built-in Date Transformations.

Example Code

  const importer = new FuseImporter();

  importer.getSessionToken = ... // see https://fuse-docs.flatirons.com/getting-started/sessions

  const validations = [
    {
      validation_type: "before_date",
      message: "Must be before 01/01/2023",
      options: {
        before_date: "2023-01-01T00:00:00.000Z",
      },
    },
  ];

  const transformations = [
    {
      transformation_type: "autoformat",
    },
  ];

  importer.addColumn({
    internal_key: "date",
    label: "Date",
    column_type: "date",
    pattern: "MM/dd/yyyy",
    required: true,
    position: 1,
    unique: true,
    validations: validations,
    transformations: transformations,
  });

  importer.show();

  const importer = new FuseImporter();

  importer.getSessionToken = ... // see https://fuse-docs.flatirons.com/getting-started/sessions

  const validations = [
    {
      validation_type: "before_date",
      message: "Must be before 01/01/2023",
      options: {
        before_date: "2023-01-01T00:00:00.000Z",
      },
    },
  ];

  const transformations = [
    {
      transformation_type: "autoformat",
    },
  ];

  importer.addColumn({
    internal_key: "date",
    label: "Date",
    column_type: "date",
    pattern: "MM/dd/yyyy",
    required: true,
    position: 1,
    unique: true,
    validations: validations,
    transformations: transformations,
  });

  importer.show();

  const importer = new FuseImporter();

  importer.getSessionToken = ... // see https://fuse-docs.flatirons.com/getting-started/sessions

  // ColumnValidations should be imported from "fuse-importer"
  const validations: ColumnValidations<"date">[] = [
    {
      validation_type: "before_date",
      message: "Must be before 01/01/2023",
      options: {
        before_date: "2023-01-01T00:00:00.000Z",
      },
    },
  ];

  // ColumnTransformations should be imported from "fuse-importer"
  const transformations: ColumnTransformations<"date">[] = [
    {
      transformation_type: "autoformat",
    },
  ];

  importer.addColumn({
    internal_key: "date",
    label: "Date",
    column_type: "date",
    pattern: "MM/dd/yyyy",
    required: true,
    position: 1,
    unique: true,
    validations: validations,
    transformations: transformations,
  });

  importer.show();

Patterns

MM/dd/yyyy
Example: 08/31/2023; 12/15/2023

yyyy-MM-dd
Example: 2023-08-31; 2023-12-15

dd/MM/yyyy
Example: 31/08/2023; 15/12/2023

MM-dd-yyyy
Example: 08-31-2023; 12-15-2023

yyyy/MM/dd
Example: 2023/08/31; 2023/12/15

dd-MMMM-yyyy
Example: 31-August-2023; 15-December-2023

MMMM dd, yyyy
Example: August 31, 2023; December 15, 2023

dd/MM/yy
Example: 31/08/23; 15/12/23

MMMM dd, yy
Example: August 31, 23; December 15, 23

yy/MM/dd
Example: 23/08/31; 23/12/15

EEEE MMMM yyyy
Example: Tuesday August 2023; Thursday December 2023

EEEE dd MMMM yyyy
Example: Tuesday 08 August 2023; Thursday 15 December 2023

MM-dd-yy
Example: 01-15-90; 12-31-99

M-dd-yy
Example: 1-15-90; 12-31-99

dd-MM-yy
Example: 15-01-90; 31-12-99

dd-MM-yyyy
Example: 15-01-1990; 15-12-2023

dd-M-yy
Example: 15-1-90; 15-12-23

MM/dd/yy
Example: 01/15/90; 12/31/99

M/dd/yy
Example: 1/15/90; 12/31/99

MM.dd.yy
Example: 01.15.90; 12.31.99

M.dd.yy
Example: 1.15.90; 12.31.99

MMM-dd-yy
Example: Jan-15-90; Dec-31-99

MMMM-dd-yy
Example: January-15-90; December-31-99

dd-MMM-yy
Example: 15-Jan-90; 31-Dec-99

yyyy-M-dd
Example: 1990-1-15; 2023-12-31

M-dd-yyyy
Example: 1-15-1990; 12-31-2023

M/dd/yyyy
Example: 1/15/1990; 12/31/2023

dd/M/yyyy
Example: 15/1/1990; 31/12/2023

yyyy/M/dd
Example: 1990/1/15; 2023/12/31

dd.MM.yyyy
Example: 15.01.1990; 31.12.2023

dd.M.yyyy
Example: 15.1.1990; 31.12.2023

yyyy.MM.dd
Example: 1990.01.15; 2023.12.31

yyyy.M.dd
Example: 1990.1.15; 2023.12.31

MMM. dd, yyyy
Example: Jan. 15, 1990; Dec. 31, 2023

dd MMM. yyyy
Example: 15 Jan. 1990; 31 Dec. 2023

dd. MMM. yyyy
Example: 15. Jan. 1990; 31. Dec. 2023

EEE, MMM dd, yyyy
Example: Mon, Jan 15, 1990; Fri, Dec 31, 2023

Built-in Date Validations

The options property of addColumn accepts an array of validations. Each validation has three properties:

validation_type: the name of the validation
message: the message showed to the end user if the validation fails
options: options for the specific validation type

The validations property accepts an array of objects that contains these possible values: validation_type, message and options. As the example below:

validations: [
  {
    validation_type: "before_date",
    message: "Must be before 01/01/2023",
    options: {
      before_date: "2023-01-01T00:00:00.000Z",
    },
  },
];

before_date

Specifies that the value must be before a certain date.

validations: [
  {
    validation_type: "before_date",
    message: "Must be before 01/01/2023",
    options: {
      before_date: "2023-01-01T00:00:00.000Z",
    },
  },
];

after_date

Specifies that the value must be after a certain date.

validations: [
  {
    validation_type: "after_date",
    message: "Must be after 01/01/2023",
    options: {
      after_date: "2023-01-01T00:00:00.000Z",
    },
  },
];

unique_case_sensitive

Specifies that the value must be unique using case-sensitive comparison.

validations: [
  {
    validation_type: "unique_case_sensitive",
    message: "Must be unique (case sensitive)",
  },
];

unique_case_insensitive

Specifies that the value must be unique using case-insensitive comparison.

validations: [
  {
    validation_type: "unique_case_insensitive",
    message: "Must be unique (case insensitive)",
  },
];

Built-in Date Transformations

The options property of addColumn accepts an array of transformations. For the date transformations, you can specify one property:

transformation_type: the name of the transformation

The transformations property accepts an array of objects that contains these possible values: transformation_type. As the example below:

transformations: [
  {
    transformation_type: "autoformat",
  },
];

autoformat

The autoformat transformation automatically transforms the date in the column to a standardized format specified by the pattern on the column.

transformations: [
  {
    transformation_type: "autoformat",
  },
];

Date Handling by File Type

CSV Files

When importing CSV files, Fuse handles dates in a straightforward manner:

Dates are read directly as text strings
Fuse importer uses your date column’s pattern as a hint to parse and transform dates
No locale-specific formatting is applied during preview or processing

Excel Files (.xls/.xlsx)

Excel files require special handling due to their native date storage format. Excel can store dates in cells as date, datetime, or time types. This means that the way dates are stored internally in Excel may differ from how they are displayed, special care must be taken to correctly interpret and process these values during import:

Fuse Importer’s First Step Preview:
- For any Excel cells containing date, datetime, or time values, Fuse first converts them to raw date values
- These raw date values are then formatted according to your operating system’s locale/region settings
- The same Excel file may result in different date formats being displayed in the preview stage depending on the user’s region. For example the same date “December 31, 2023” may look like this in the preview stage for US and Europe:
  - US: “12/31/2023, 00:00:44”
  - Europe: “31/12/2023, 00:00:44”
- Excel uses a unique numbering system to store dates internally, where each date is represented as a count of days since January 1, 1900 (for more details, see Microsoft’s documentation on Excel date systems). Fuse converts these numerical values into familiar date formats during import. Here’s how Fuse handles Excel’s internal date representation versus what users see:
  - Excel display: “01/12/2024” (what users see in Excel)
  - Excel raw value: “2024-12-01 00:00:44” (the complete date and time information Excel actually stores)
  - Fuse extracts the complete raw value that Excel stores internally, including any time components that may be hidden by Excel’s date formatting options in order to preserve all date-time information for future processing.
Fuse Importer’s Processing Stage:
- In this stage, the text format of dates, already extracted from Excel’s native format during the first step, is used for further processing.
- Similar to date processing based on CSV files, text date values are parsed using your importer’s date pattern as a hint and later transformed to the format specified by the importer date column pattern.

Example: Excel Import Process

Take an Excel file with the date “January 15, 2023”:

Preview stage appearance:
- US locale: “1/15/2023”
- UK locale: “15/1/2023”
Parsing stage:
- The date is parsed according to the importer’s date pattern, converting it to raw date values for further processing.
Processing stage (with pattern “yyyy-MM-dd”):
- Final output: “2023-01-15”

Two-Digit Year Rules

For both CSV and Excel files, Fuse applies these rules when processing two-digit years:

Years 00-49 → 2000-2049
Years 50-99 → 1950-1999
Example: “23” becomes “2023”, “50” becomes “1950”

Best Practices

To ensure consistent results across file types and regions:

Use four-digit years in your data files
Test your setup with sample files, especially when working with two-digit years
Be cautious of uploading data with ambiguous dates, such as “01/02/03” or “04/05/06”, as it can lead to incorrect parsing and data inconsistencies
Consider using date patterns that reduce ambiguity, such as “yyyy-MM-dd” or “dd-MMMM-yyyy”. For example, “2023-01-15” or “15-January-2023” are less likely to be misinterpreted compared to “01/02/03”.

Importer Setup

Accessing Data

Use Cases

Using Columns

Appearance

Extending Functionality

JS Implementations

More to Love

addColumn Function

Example Code

Patterns

Built-in Date Validations

before_date

after_date

unique_case_sensitive

unique_case_insensitive

Built-in Date Transformations

autoformat

Date Handling by File Type

CSV Files

Excel Files (.xls/.xlsx)

Example: Excel Import Process

Two-Digit Year Rules

Best Practices

Importer Setup

Accessing Data

Use Cases

Using Columns

Appearance

Extending Functionality

JS Implementations

More to Love

​addColumn Function

​Example Code

​Patterns

​Built-in Date Validations

​before_date

​after_date

​unique_case_sensitive

​unique_case_insensitive

​Built-in Date Transformations

​autoformat

​Date Handling by File Type

​CSV Files

​Excel Files (.xls/.xlsx)

​Example: Excel Import Process

​Two-Digit Year Rules

​Best Practices

addColumn Function

Example Code

Patterns

Built-in Date Validations

before_date

after_date

unique_case_sensitive

unique_case_insensitive

Built-in Date Transformations

autoformat

Date Handling by File Type

CSV Files

Excel Files (.xls/.xlsx)

Example: Excel Import Process

Two-Digit Year Rules

Best Practices