Get Free Trial
Get Free Trial
  1. API reference
  2. Welcome
    1. Component overview
    2. Quick start
    3. System requirements
    4. Troubleshooting
    5. Managing license keys
    6. Migrating from WebDataRocks to Flexmonster
  3. Connecting to Data Source
    1. JSON
      1. Connecting to JSON
      2. Connecting to JSON using the Data Compressor
      3. Data types in JSON
    2. CSV
      1. Connecting to CSV
      2. Connecting to CSV using the Data Compressor
      3. Data types in CSV
    3. Database
      1. Connecting to SQL databases
      2. Connecting to other databases
      3. Connecting to a database with Node.js
      4. Connecting to a database with .NET
      5. Connecting to a database with .NET Core
      6. Connecting to a database with Java
      7. Connecting to a database with PHP
    4. Microsoft Analysis Services
      1. Connecting to Microsoft Analysis Services
      2. Getting started with the Accelerator
      3. Installing the Accelerator as a Windows service
      4. Referring the Accelerator as a DLL
      5. Configuring the authentication process
      6. Configuring a secure HTTPS connection
      7. Troubleshooting
    5. Pentaho Mondrian
      1. Connecting to Pentaho Mondrian
      2. Getting started with the Accelerator
      3. Configuring Mondrian roles
      4. Configuring username/password protection
      5. Configuring a secure HTTPS connection
      6. Troubleshooting
    6. Elasticsearch
      1. Connecting to Elasticsearch
      2. Configuring the mapping
  4. Security
    1. Security in Flexmonster
    2. Security aspects when connecting to an OLAP cube
      1. The data transfer process
      2. Data security
      3. Data access management
    3. Security aspects when connecting to a database
      1. The data transfer process
      2. Data access management
  5. Configuring report
    1. What is a report
    2. Data source
    3. Slice
    4. Options
    5. Number formatting
    6. Conditional formatting
    7. Set the report for the component
    8. Get the report from the component
    9. Date and time formatting
    10. Configuring global options
    11. Export and print
    12. Calculated values
    13. Custom sorting
  6. Integration with frameworks
    1. Available tutorials
    2. Integration with AngularJS (v1.x)
    3. Integration with Angular
    4. Integration with React
    5. Integration with Webpack
    6. Integration with ASP.NET
    7. Integration with jQuery
    8. Integration with JSP
    9. Integration with TypeScript
    10. Integration with RequireJS
    11. Integration with PhoneGap
  7. Integration with charts
    1. Integration with Highcharts
    2. Integration with Google Charts
    3. Integration with FusionCharts
    4. Integration with any charting library
  8. Customizing
    1. Customizing toolbar
    2. Customizing appearance
    3. Customizing context menu
    4. Localizing component
  9. Updating to the latest version
    1. Updating to the latest version
    2. Release notes
    3. Migration guide from 2.6 to 2.7
    4. Migration guide from 2.5 to 2.6
    5. Migration guide from 2.4 to 2.5
    6. Migration guide from 2.3 to 2.4
    7. Migration guide from 2.2 to 2.3
    8. Documentation for older versions
Table of contents

Data types in CSV

When using a CSV data source, use special prefixes for column names to indicate how data should be interpreted by Flexmonster Pivot. The component has the following prefixes for CSV data:

  • + – the field is a dimension.
  • - – the field is a value.
  • m+ – the field is a month.
  • w+ – the field is a day of the week.
  • d+ – the field is a date. Such fields will be split into 3 different fields: Year, Month, and Day. Date formats that are supported by Flexmonster Pivot are described below.
  • D+ – the field is a date. You will see these dates as a hierarchy: Year > Month > Day.
  • D4+ – the field is a date. You will see these dates as a hierarchy: Year > Quarter > Month > Day.
  • ds+ – the field is a date. Such fields will be formatted using a date pattern (default is "dd/MM/yyyy").
  • t+ – the field is a time (measure). Such fields will be formatted using "HH:mm:ss" pattern.
  • dt+ – the field is a date (measure). Such fields will be formatted using "dd/MM/yyyy HH:mm:ss" pattern.
  • id+ – the field is an id of the fact.

Here is the minimal CSV data that will treat Year as a dimension, rather than a numeric measure:

Country, +Year, Sales 
US, 2010, 200 
UK, 2010, 100

Supported date formats

To make date column be interpreted as a date, use prefixes d+, D+, and D4+ for CSV columns. Additionally, data from these columns should have a special date format to be understood properly. The pivot table component supports the ISO 8601 date format, for example: "2016-03-20" (just date) or "2016-03-20T14:48:00" (date and time). Other formats aren’t officially supported and may have unexpected results.

Here is an example of CSV data with date columns – Date1 and Date2:

Size, Discount, d+Date1, D+Date2
214 oz, 14, 2009-11-01, 2009-11-09
214 oz, 12, 2010-12-09, 2009-12-09
212 oz, 36, 2009-09-01, 2009-12-01
212 oz, 27, 2009-09-01, 2010-12-02
212 oz, 18, 2010-11-09, 2009-12-11
212 oz, 16, 2009-09-01, 2009-12-20

The pivot table based on this CSV will look as follows:

date data types from CSV

As you can see, the Date1 column with prefix d+ is split into three separate fields — Year, Month, and Day. In the Field List, the Date1 column will look as follows:

date data types from CSV in the Field List

The Date2 column with D+ prefix is interpreted as a hierarchy that can be drilled down to months and days.