Date Columns

​

addColumn Function

• 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();

​

Patterns

​

Built-in Date Validations

• 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

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

​

before_date

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

​

after_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

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

​

unique_case_insensitive

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

​

Built-in Date Transformations

• transformation_type: the name of the transformation

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

​

autoformat

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

​

Date Handling by File Type

​

CSV Files

• 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)

1. 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.
2. 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

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

​

Two-Digit Year Rules

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

​

Best Practices

• 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”.