Connecting to CSV

This article illustrates how to build a report based on a CSV data source.

In Flexmonster, you can:

Load CSV data from the local file system (see Connect > To local CSV in the Toolbar).
Refer to a remote CSV file.
Add data generated by a server-side script.

To load big CSV files (more than 100 MB), we recommend using Flexmonster Data Server. This server-side tool was designed to handle large datasets. Learn more here: Connecting to CSV using Flexmonster Data Server.

This guide contains the following sections:

Supported CSV format
- Supported input date formats
Connect to a sample CSV file
Display non-English characters correctly

Supported CSV format

Flexmonster supports a standard CSV format. According to this format, your CSV files should be structured like this:

The first record in the file contains field names.
Each record is located on a separate line.
Fields in records are separated by the same character: comma, semicolon, etc.
To specify which character separates fields in your CSV file, use the dataSource.fieldSeparator property.
Fields may be enclosed in double quotes if needed.
Fields containing line breaks or field separators (e.g., commas) should be enclosed in double quotes.
Note: if your CSV file has fields with line breaks, set the dataSource.ignoreQuotedLineBreaks property to false.
A double quote inside a field should be escaped using another double quote. For instance, Field "2" should be specified as Field ""2"" in CSV.

Here is an example of a valid CSV file:

Category,Color,Country,Price
Accessories,red,Australia,174
Components,blue,France,768
Clothing,green,Canada,512

Supported input date formats

Flexmonster supports the following input date formats:

ISO 8601. For example:
- "2021-04-25" – Date.
- "2021-04-25T21:30:05" – Date and time.
- "2021-04-25T21:30:05+03:00" – Date and time with a time zone.
Unix timestamp in milliseconds. For example, "2021-04-25" is 1619298000000 when converted to a Unix timestamp in milliseconds.
Note It is important to assign the date type to the Unix timestamp field explicitly. Otherwise, the component will treat this field as a numeric one.

Other formats aren’t officially supported and may have unexpected results.

Connect to a sample CSV file

Complete the following steps to use CSV data as a data source in the pivot table embedded in your project:

Step 1. Embed Flexmonster into your project.

If Flexmonster is not yet embedded, set up an empty component in your webpage:

In pure JavaScript

Complete the Integrating Flexmonster guide. Your code should look similar to the following example:

let pivot = new Flexmonster({
  container: "pivotContainer",
  componentFolder: "node_modules/flexmonster/",
  toolbar: true
});

In Angular

Complete the Integration with Angular guide. Your code should look similar to the following example:

<fm-pivot
 [toolbar]="true">
</fm-pivot>

In React

Complete the Integration with React guide. Your code should look similar to the following example:

<FlexmonsterReact.Pivot
 toolbar={true}
/>

In Vue

Complete the Integration with Vue guide. Your code should look similar to the following example:

<Pivot
 toolbar
/>

Step 2. For this example, we will use the data.csv file from our CDN. It has the following structure:

Category,Size,Color,Destination,Business Type,Country,Price,Quantity,Discount
Accessories,262 oz,red,Australia,Specialty Bike Shop,Australia,174,225,23
Accessories,214 oz,yellow,Canada,Specialty Bike Shop,Canada,502,90,17
Accessories,147 oz,white,France,Specialty Bike Shop,France,242,855,37
Accessories,112 oz,yellow,Germany,Specialty Bike Shop,Germany,102,897,42

Step 3. Add the dataSource.filename property to the ReportObject:

let pivot = new Flexmonster({
  container: "pivotContainer",
  componentFolder: "node_modules/flexmonster/",
  toolbar: true,
  report: {
    dataSource: {
      filename: "https://cdn.flexmonster.com/data/data.csv"
    }
  }
});

If CSV fields in your file are separated by anything other than , or ;, specify the dataSource.fieldSeparator property.

Step 4. Define a slice – fields that go to rows, go to columns and go to measures:

let pivot = new Flexmonster({
  container: "pivotContainer",
  componentFolder: "node_modules/flexmonster/",
  toolbar: true,
  report: {
    dataSource: {
      filename: "https://cdn.flexmonster.com/data/data.csv"
    },
    slice: {
      rows: [
        { uniqueName: "Color" },
        { uniqueName: "[Measures]" }
      ],
      columns: [
        { uniqueName: "Country" }
      ],
      measures: [
        { uniqueName: "Price", aggregation: "sum" }
      ]
    }
  }
});

Step 5. Launch the page from a browser — there you go! A pivot table based on CSV data is embedded into your project. Open this example on JSFiddle.

Now you know how to build a report based on our sample CSV. The next step for you is to visualize your CSV data.

Display non-English characters correctly

If you use an alphabet with special characters you must use UTF-8 encoding. There are two ways to do this:

Specify the encoding for your HTML page to UTF-8 using the content-type HTTP header or the corresponding meta tag:
```
<meta http-equiv="content-type" content="text/html; charset=utf-8"/>
```
Content from a database and static CSV files also must be encoded as UTF-8.
If you are not able to change the content of your HTML file, you can embed Flexmonster Pivot in a separate JS file with specified UTF-8 encoding.
```
<script src="yourfile.js" charset="UTF-8"></script>
```

What's next?

You may be interested in the following articles: