About Data Set Formatting Rules

This topic explains you the formatting rules that your files must comply with to be successfully imported into Metrics Reader.

This page discusses:

Common Rules for All Data Types

The file you want to import into Metrics Reader must comply with all the following rules:

  • Supported file formats: XLS, XLSX, CSV, ODS, TXT, TSV.
  • Maximum file size: 10 MB.
  • The first row of your data set must be the header of each column. The header must not span multiple rows. The subsequent rows must contain your data.
  • There must not be any blank rows between the header and the rows that contain your data.
  • Cells must not span multiple columns or rows.
  • For CSV and TSV files, each column must be separated by a semicolon, a comma, or a tabulation mark.
  • Decimal marks can be a decimal point or a comma. Only one kind of decimal mark can be used per data set.
  • Within the same data set, decimal marks, date delimiters and column delimiters must use different characters. For example, when commas are already used as decimal marks, they cannot be used as column delimiters.

Quantitative Time-Series Formatting Rules

This section provides additional formatting rules that are specific to qualitative time-series. Your file must also comply with the common rules explained above.

Formatting Rules

A quantitative time-series data set is a data set that contains temporal data, that is numerical values that are associated to a point in time. In your file, dates and values must be in separate columns.

If the date format of your file does not contain any year, the current year is added by default to the Metrics Reader chart.

You may have to specify which date format is in use in your data set and which column contains the dates during configuration of your Metrics Reader. For more information, see Select Range for Quantitative Time-Series.

Supported date formats are:

Date Format1 Example
Y 2003
F December
M Dec
Y m 2003-12
m d 12-31
d m 31-12
d M 31 Dec
M Y Dec 2003
M y Dec 03
F Y December 2003
F y December 03
d F 31 December
m d Y 12-31-2003
m d y 12-31-03
d m Y 31-12-2003
d m y 31-12-03
Y m d 2003-12-31
y m d 03-12-31
d F Y 31 December 2003
d F y 31 December 03
d M Y 31-12-2003
d M y 31-12-03
F d Y December 31 2003
F d y December 31 03
l d F Y Monday 31 December 2003
l d F y Monday 31 December 03
l d M Y Monday 31 12 2003
l d M y Monday 31 12 03
m d Y H i s 12-31-2003 22:50:23
m d Y h i s a 12-31-2003 10:50:23 pm
m d Y H i 12-31-2003 22:50
m d Y h i a 12-31-2003 10:50 pm
m d y H i s 12-31-03 22:50:23
d m y h i s a 31-12-03 10:50:23 pm
d m y H i 31-12-03 22:50
d m y h i a 31-12-03 10:50 pm
Y m d H i s 2003-12-31 22:50:23
Y m d h i s a 2003-12-31 10:50:23 pm
Y m d H i 2003-12-31 22:50
Y m d H i O 2003-12-31 22:50 +0000
Y m d h i a 2003-12-31 10:50 pm
Y m d H i s 2003-12-31 22:50:23
Y m d h i s a 2003-12-31 10:50:23 pm
Y m d H i 2003-12-31 22:50
Y m d H i 2003-12-31 22:50 pm
D d M Y H i s O Mon 31-12-2003 22:50:23+0000

1 Where:

Format Character Description Value Examples
a Lowercase Ante meridiem and Post meridiem. am or pm
d Day of the month, 2 digits with leading zeros. 01 to 31
D A textual representation of a day, three letters. Mon through Sun
F A full textual representation of a month. January through December
h 12-hour format of an hour with leading zeros. 01 through 12
H 24-hour format of an hour with leading zeros. 00 through 23
i Minutes with leading zeros. 00 to 59
l A full textual representation of the day of the week. Sunday through Saturday
m Numeric representation of a month, with leading zeros. 01 through 12
M A short textual representation of a month, three letters. Jan through Dec
O Difference to Greenwich time (GMT) in hours. +0000
s Seconds, with leading zeros. 00 through 59
Y A full numeric representation of a year, 4 digits. 2003
y A two-digit representation of a year. 03
Z Time zone offset in seconds. The offset for time zones west of UTC is always negative, and for those east of UTC is always positive. -43200 through 50400

Supported Date Delimiters

Date delimiters specified in the table below are supported. Only one kind of date delimiter can be used per data set.

Date Delimiters Description Examples
- Can be used to separate days, months, and years.

31-12

/ Can be used to separate days, months, and years.

31/12
. Can be used to separate days, months, and years.

31.12.2003
Blank space Can be used to separate days, months, and years.

31.12.2003
: Can be used to separate separate days, months, years, hours, minutes and seconds.

22:50:23

31:12:2003

31:12:2003:10:50:23

Example of a Well-Formed Data Set

Website visitors Date
2158 2013-06-01
8060 2013-06-02
4709 2013-06-03
1259 2013-06-04
1059 2013-06-05
2267 2013-06-06
4579 2013-06-07

Quantitative Series Formatting Rules

This section provides additional formatting rules that are specific to quantitative series. Your file must also comply with the common rules explained above.

Formatting Rules

A quantitative series data set is a data set that contains non-numerical values associated to numerical values. It does not change over time. Non-numerical values must have been stripped of any special characters, accents and diacritical mark, such as %, PM/AM, and currency symbols.

Example of a Well-Formed Data Set

Name Rate Hours Net Pay
Kent Clark 10.00 51.4 514
Rogers Steve 14.00 60.14 842
Osborn Harry 12.00 48.9 489
Parker Peter 12.00 38.16 458
Grey Jean 10.00 88.4 884

Qualitative Series Formatting Rules

This section provides additional formatting rules that are specific to qualitative series. Your file must also comply with the common rules explained above.

A qualitative series data set is a data set that does not include any numerical values. Metrics Reader will chart the occurrences of each value contained in the column you selected during configuration. For more information about column selection, see Select Range for Qualitative Series.

Example of a Well-Formed Quantitative Data Set

Category Tasks Importance Assessment
Day to Day Win/Loss Analysis LOW Not Done
Product Strategy Competitor Analysis LOW Not Done
Product Marketing Pricing MEDIUM Done
Plan and Design Risk Planning HIGH Done
Execution Scope HIGH Done