Create pivot table by CSV

CSV (or TSV) file is a simplest data source in SeekTable: it is enough to click on Upload Data, choose a local file, and wait until file is uploaded. That's all: now you can create summary report (pivot table, chart) or view/filter/sort CSV data rows with usual grid-view ('flat table').

All parameters are detemined automatically:

• CSV separator: comma, semicolon, pipe or tab.
• File encoding: UTF-16, UTF-8, Windows-1252, Windows-1251/KOI8-R (Cyrillic), Windows-1253 (Greek), ISO-2022-JP, ISO-2022-KR, ISO-2022-CN and others.
• First row should contain column names. If your CSV file doesn't contain headers row you have to add it.
• Data types: string, number, date. Other types may be parsed with custom expressions.
• For number columns sum/average/min/max measures are suggested.
• For date columns 3 derived dimensions (year, month, day) are configured automatically.
• Columns with many unique values (like IDs) may be excluded from dimensions. You can add them explicitely if needed.

CSV file may be compressed with zip or gzip, and this is only option if you want to upload large CSV file that is greater than upload limit (50Mb). Uncompressed CSV size might be up to 500Mb; SeekTable works fine with files of this size and pivot table generation should not take more than 5-10 seconds.

It is possible to define special expressions for custom handling of CSV values (parse timespans, apply replace rules etc).

Refresh CSV data

In some cases you might need to refresh saved pivot table reports by uploading newer version of the dataset. This is possible in one of the following ways:

• If CSV file name is the same as previously uploaded file: just upload new file and choose Refresh Existing Cube in the confirmation dialog. Otherwise:
  1. Determine ID of CSV-based cube you want to refresh; it is present in the address bar, for example:

     https://www.seektable.com/cube/feb3828c57474791a5ee92b83d4195e0

     In this case cube ID is feb3828c57474791a5ee92b83d4195e0.
  2. Rename new CSV file and use cube ID as file name. For example:
     mydata.csv → feb3828c57474791a5ee92b83d4195e0.csv
mydata.zip → feb3828c57474791a5ee92b83d4195e0.zip (if CSV file is zipped)
  3. Upload renamed file in a usual way (with Upload Data button).
  4. Instead of creating new cube SeekTable will replace old CSV file with a new version. All saved reports will be refreshed.
• Use Zapier and create a task to automate CSV data refresh. For example, you can refresh reports automatically each time when you change Google Sheets document.
• Use Microsoft Flow for the same purpose.

Note: data refresh is possible only if new CSV file has the same columns as in old version of CSV file (with the same names, new columns are OK).

Calculated columns row-level expressions

It is possible to define expression-based (calculated) dimensions that are evaluated before aggregation step (on row-level). For example, if you have date column (say, "Some Date") it is possible to calculate quarter or day-of-week in the following way:

1. add new dimension with Type=Field
2. fill Name with some unique value: say, "Some Date (Quarter)" or "Some Date (DayOfWeek)"
3. add one Parameter which should be an expression (syntax is the same as for calculated members):
   • Date.Quarter(Column["Some Date"]) (for quarter)
   • Date.DayOfWeek(Column["Some Date"]) (for day-of-week)
   • Date.Week(Column["Some Date"]) (for week number)

Column is a special variable that provides access to the context CSV row values; you can use column name directly if it doesn't contain spaces or special symbols - for example, SomeDate. More details about expression syntax you can found here.

Report parameters setup

You can define report parameters to specify report-specific filtering condition by CSV column(s) in Filter Expression:

Name

Unique (for this cube) parameter identifier.

Label

User-friendly parameter caption for UI (optional).

Data Type

String: text-based value.
Int32: 32-bit integer (max value is 2,147,483,647).
Int64: 64-bit integer (max value is 9,223,372,036,854,775,807).
Decimal: Fixed-point number with max 28 significant digits. Decimal point is '.' character.
DateTime: date or datetime value (in case of date this will be datetime value with 0:00:00 time). Date value should be specified as string in YYYY-MM-DD format.
Boolean: accepts only 'True' or 'False' value.

Multivalue

If checked parameter can accept several values (as array, in UI user can enter them as comma-separated string). Multivalue parameter can be used only with IN condition.

Default Value

Defines default value of this parameter. Empty means 'not defined'.

Expression

Custom expression to evaluate final parameter value. Expression syntax is the same as in calculated cube members; you can access user-entered values with Parameter["param_name"].
For example: "%"+Parameter["param_name"]+"%" (wrap with '%' for 'like' condition).

When parameter is defined it can be used in Filter Expression as following:

"1"="1" @paramName[ and "csv_column_name":field="{0}":var ]

In this sample parameter name is paramName and CSV column to filter is csv_column_name.

Filtering expression syntax:

• condition syntax: "csv_column_name":field <condition> "value"[:datatype]
  

  csv_column_name

  Name of the CSV column to filer. Expression-type dimension name cannot be used here.

  condition

  <, <=, >, >=, =, !=, like, in. 'Like' works like in SQL: prepend/append '%' for 'contains' (you can add '%' automatically with parameter Expression).

  value

  hardcoded constant, or parameter name with 'var' datatype: "param_name":var

  datatype

  string, int32, int64, decimal, double, datetime

• boolean and/or operators: C1 and C2, C1 or C2, C1 or (C2 and C3)

Parameter placeholder syntax:

@

identifies that this is a placeholder for the parameter

paramName

parameter's Name

[ ]

expression between square brackets is added to Filter Expression when parameter is defined.

"{0}":var

placeholder for the parameter value.

Analyze very large CSV file

In case if you want to create a pivot table by CSV file that exceeds upload limit (even zipped) you still can do that in one of the following ways:

• Use self-hosted SeekTable that doesn't have any limitations on file upload and run it on your computer (or server). Pivot table generation by 1GB file might take up to 10-15 seconds.
• Import your CSV data into a database (cloud or on-premise) that is supported by SeekTable. If you interested in real-time analytics by many millions of rows you can try Amazon Redshift, MemSql or Yandex ClickHouse. If your dataset has unstructured text and you want to filter by this content consider usage of ElasticSearch.