We have updated WebDataRocks EULA, effective as of April 18, 2024. Learn more about what's changed.
Download
Documentation menu
Getting started
Integration with frameworks
Data sources
    JSON
    CSV
    1. Supported CSV format
    2. Setting data types in CSV
    3. Connecting to CSV
    4. Data type prefixes
Configuring fields
Pivoting functionality
Formatting
Grid layout
Saving and loading reports
Add-ons
Customizing
Integration with charts
Technical specifications and licensing
API Reference

Setting data types in CSV

Using data type prefixes in CSV, you can set data types for fields in your data source.

Available data types

The following data types are available in CSV:

Some of these types can be auto-resolved by the component.

Automatic type resolution

If you omit the data type prefix, the component automatically assigns the data type based on the field’s first value. The following data types can be auto-resolved:

The results of the automatic resolution may be unexpected when:

  • The first field value is blank or not specified.
  • The field values are not formatted correctly. For example, a date was specified in a format different from the ISO 8601.
  • The field type cannot be resolved automatically. For example, time values are processed as numbers if the t+ prefix is not explicitly specified.
  • The first field value is formatted differently from other values. For example, the first value is a number while others are strings.

Learn more about each field data type in the sections below.

Number

This field type is used to store numbers.

Field values can contain digits, -, and + characters. Point (.) must be used as a decimal separator. Numbers in exponential notation are also supported. Examples of valid values: -20, 2.50, 1.0E+2.

To mark the field as a number, add the - data type prefix to the field name.

Can be auto-resolved: yes (learn more).

Available aggregations: all.

String

This field type is used to store strings.

If a field value contains line breaks or a field separator, it must be enclosed in double quotes ("). A double quote inside such values must be escaped using another double quote. Examples of valid values: Apple, "A-Z" section, "1, 2, 3", "The ""A, B, C"" magazine".

To mark the field as a string, add the + data type prefix to the field name.

Can be auto-resolved: yes (learn more).

Available aggregations: Count, Distinct Count.

Date

This field type is used to store dates.

Field values must be specified in the ISO 8601 format. Examples: 2018-01-10 (date), 2018-01-10T08:14:00 (date and time), 2018-01-10T06:14:00Z (date and time in UTC).

To mark the field as a date, add one of the following data type prefixes to the field name:

  • d+ – dates are divided into 3 subfields: Year, Month, Day.
    Can be auto-resolved: yes (learn more).
    Available aggregations: Count, Distinct Count.
  • ds+ – dates are displayed as strings in the "dd/MM/yyyy" format. The format can be changed using the datePattern option.
    Can be auto-resolved: no (learn more).
    Available aggregations: Min, Max, Count, Distinct Count.
  • D+ – dates are represented as the multilevel hierarchy: Year > Month > Day.
    Can be auto-resolved: no (learn more).
    Available aggregations: Count, Distinct Count.
    See the example.
  • D4+ – dates are represented as the multilevel hierarchy: Year > Quarter > Month > Day.
    Can be auto-resolved: no (learn more).
    Available aggregations: Count, Distinct Count.
    See the example.
  • dt+ – dates are displayed as strings in the "dd/MM/yyyy HH:mm:ss" format. The format can be changed using the dateTimePattern option.
    Can be auto-resolved: no (learn more).
    Available aggregations: Min, Max, Count, Distinct Count.

Here is an example of CSV data with the Invoice date date (dt+) field:

dt+Invoice Date, Price, Country
2018-05-15T18:30:00, 329, France
2018-05-16T06:20:00, 139, Italy
2018-05-17T13:45:00, 429, Spain
2018-05-12T04:50:00, 559, Japan

Time

This field type is used to store time intervals, such as duration.

Field values must be specified as a number of seconds. In the component, values are displayed in the "HH:mm:ss" format. Examples of valid values: 5400 (displayed as "01:30:00" in the component).

To mark the field as time, add the t+ data type prefix to the field name.

Can be auto-resolved: no (learn more).

Available aggregations: Min, Max, Count, Distinct Count. 

Here is an example of CSV data with the Duration time field:

t+Duration, Movie, ReleaseYears
7020, Blade Runner, 1980-1990
5220, The Lion King, 1990-2000
7560, Jurassic Park, 1990-2000
9120, The Dark Knight, 2000-2010

Month

This field type is used to store months. Natural sorting is applied to the field members: from January to December.

Field values must start with a capital letter. Full names and three-letter abbreviations of months are supported. Examples of valid values: October, Dec, May.

To mark the field as months, add the m+ data type prefix to the field name.

Can be auto-resolved: no (learn more).

Available aggregations: Count, Distinct Count.

Weekday

This field type is used to store the days of the week. Natural sorting is applied to the field members: from Sunday to Monday.

Field values must start with a capital letter. Full names and three-letter abbreviations of the days of the week are supported. Examples of valid values: Monday, Sun, Friday.

To mark the field as a day of the week, add the w+ data type prefix to the field name.

Can be auto-resolved: no (learn more).

Available aggregations: Count, Distinct Count.

See also