Proposal: a frictionless tool that creates a spec file that has inside the field separator

[aborruso]

Some introductory points, starting from an example I know:

• a lot of CSV files does not use , as separator. In example all opendata files of my city opendata portal (it's Palermo in Italy) use ;. These are nearby 1000 CSV files.
• if this website exposed the data as datapackage and some user wanted to use it, he/she will have a lot of great info about these files, but he/she will have nothing about CSV separator. So he/she will have some friction;
• the open data world is not only CSV files, but these are still great protagonists of it.

In the "CSV file requirements" of Tabular Data Resource you have that CSV files MUST follow RFC 4180 with the following important exceptions allowed:

• File encoding, that it's a default and direct output info in example of goodtables and datapackage (goodtables init input.csv, or datapackage infer input.csv)
• CSV Dialect. If the CSV differs from RFC in some way regarding dialect (e.g. line terminators, quote characters, field delimiters) the Tabular Data Resource MUST contain a dialect property describing its dialect. But no frictionless tools add dialect properties if these CSVs differ from the RFC. And the separator is often one these properties.

The great goodtables and datapackage (thank you very much for these tools) read properly the CSV separator. To add to the default output of CSV inferencing also the dialect would be a great thing, it would certainly remove friction, because a lot of CSVs differ from RFC.

To add to the standard output:

"dialect": {
    "delimiter": ";"
  }

Thank you

[rufuspollock]

@aborruso do you know of any existing tooling for guessing the delimiter. I believe csvkit does this e.g. wireservice/csvkit#1011 /cc @jpmckinney

[jpmckinney]

CSVKit just uses Python's sniffing: https://docs.python.org/3/library/csv.html#csv.Sniffer

[aborruso]

@rufuspollock goodtables and datapackage already do it, but they do not write it in the output. If you have this

field1;field2
0;a
1;b
3;a

and run datapackage infer input.csv you have the output below, then datapackage reads properly the separator and creates the JSON schema.

And you can use a python onliner cat input.csv|python3 -c "import sys; import csv; print(csv.Sniffer().sniff(sys.stdin.read()).delimiter)".

{
  "profile": "tabular-data-package",
  "resources": [
    {
      "path": "input.csv",
      "profile": "tabular-data-resource",
      "name": "input",
      "format": "csv",
      "mediatype": "text/csv",
      "encoding": "utf-8",
      "schema": {
        "fields": [
          {
            "name": "field1",
            "type": "integer",
            "format": "default"
          },
          {
            "name": "field2",
            "type": "string",
            "format": "default"
          }
        ],
        "missingValues": [
          ""
        ]
      }
    }
  ]
}

[rufuspollock]

@aborruso is the fix here to improve infer to include the inferred schema info?

[aborruso]

hi @rufuspollock I did not understand your question, I'm sorry.

I have updated datapackage-py, I have 1.15.2 and if I run

datapackage infer input.csv

I have no info about separator:

{
  "profile": "tabular-data-package",
  "resources": [
    {
      "path": "input.csv",
      "profile": "tabular-data-resource",
      "name": "input",
      "format": "csv",
      "mediatype": "text/csv",
      "encoding": "utf-8",
      "schema": {
        "fields": [
          {
            "name": "field1",
            "type": "integer",
            "format": "default"
          },
          {
            "name": "field2",
            "type": "string",
            "format": "default"
          }
        ],
        "missingValues": [
          ""
        ]
      }
    }
  ]
}

But if I run frictionless validate --json input.csv I have it:

"dialect": {
          "delimiter": ";"
        }

[roll]

Frictionless does it:

Note that the default delimiter ',' will be omitted

$ frictionless describe data/delimiter.csv --yaml

dialect:
  delimiter: ;
encoding: utf-8
format: csv
hashing: md5
name: delimiter
path: data/delimiter.csv
profile: tabular-data-resource
schema:
  fields:
    - name: id
      type: integer
    - name: name
      type: string
scheme: file

$ frictionless validate data/delimiter.csv --yaml

errors: []
stats:
  errors: 0
  tasks: 1
tasks:
  - errors: []
    partial: false
    resource:
      dialect:
        delimiter: ;
      encoding: utf-8
      format: csv
      hashing: md5
      name: delimiter
      path: data/delimiter.csv
      profile: tabular-data-resource
      schema:
        fields:
          - name: id
            type: integer
          - name: name
            type: string
      scheme: file
      stats:
        bytes: 30
        fields: 2
        hash: e5ee50a4b8d3a38f777599d70bebee96
        rows: 2
    scope:
      - hash-count-error
      - byte-count-error
      - field-count-error
      - row-count-error
      - blank-header
      - extra-label
      - missing-label
      - blank-label
      - duplicate-label
      - incorrect-label
      - blank-row
      - primary-key-error
      - foreign-key-error
      - extra-cell
      - missing-cell
      - type-error
      - constraint-error
      - unique-error
    stats:
      errors: 0
    time: 0.02
    valid: true
time: 0.02
valid: true
version: 4.5.1

[aborruso]

@roll I know it I have written in my message.

frictionless is really great tool and you are a magician

[roll]

Oops sorry I was answering the last message 😃

[roll]

@aborruso
So basically you'd like to be able to get delimiter even though it's a default one? There is the expand argument for the frictionless CLI but it seems it doesn't expand a dialect if it's empty (we might need to create a bug report).

Or please correct me as if got slightly lost in this thread 😃

[roll]

Just an aside note that in [email protected] the expand argument is fixed and now it includes an expanded dialect even though all the values are default:

frictionless describe data/table.csv --expand

compression: ''
control: {}
dialect:
  delimiter: ','
  doubleQuote: true
  lineTerminator: "\r\n"
  quoteChar: '"'
  skipInitialSpace: false
encoding: utf-8
format: csv
hashing: md5
innerpath: ''
layout:
  header: true
  headerCase: true
  headerJoin: ' '
  headerRows:
    - 1
name: table
path: data/table.csv
profile: tabular-data-resource
schema:
  fields:
    - bareNumber: true
      format: default
      name: id
      type: integer
    - format: default
      name: name
      type: string
  missingValues:
    - ''
scheme: file

[aborruso]

@roll for me, now it's really great.

Thank you