Unable to specify Output Delimiter for SDMX-CSV

[lazarpr]

A workflow is needed in which an SDMX service converts data from SDMX‑ML input into SDMX-CSV output. However, the requirement is that the output should not be restricted to comma‑delimited CSV. Instead, the delimiter should be configurable through the transformation service. As far as I can tell, the current specification does not provide a mechanism for this. The "response type" section of the Data Queries guide ( https://github.com/sdmx-twg/sdmx-rest/blob/master/doc/data.md ) does not specify how this could be achieved.

Should a new parameter of "delimiter" be added to the technical specification to support this capability?

[dosse]

Hi @lazarpr, please see the following text in the introduction of the SDMX-CSV message:

In RFC 4180, csv stands for "comma-separated values". However, while SDMX-CSV uses indeed the "comma" (%x2C) as the default field separator, it adopts the wider interpretation of csv as "character-separated values". It is recommended for implementers to provide SDMX-CSV messages according to the locale of the user (e.g. as indicated in the http Accept-Language header). It means that e.g. the semi-colon ‘;’ (as used typically in specific regions or countries) is acceptable as separator. See also the related example below. Note that the separator used in a message can be determined by retrieving the character that follows the fixed first column header term STRUCTURE (which may be extended by a squared bracket term).

Therefor the choice of the field and decimal separators is achieved through the http Accept-Language header in REST requests. Each locale has its predefined set of those separator characters. With this, users in the UK can retrieve the appropriate CSV flavour that allows them to immediately open the file in Excel. The same is the case for users in France even if their Excel version expects different separator characters.

[lazarpr]

@dosse Thanks for your quick response.

However could the use of "Language" have implications for the rest of a system? For example, if I want an output with semicolons, I could use the German language. The German format tends to use semicolons since the German number format uses comma instead of a period to indicate decimals. I fear that because a downstream process requires a different delimiter character, values could be affected through a transformation process. (e.g. 3.14 becomes 3,14). Personally I'd rather see another parameter on the API than have to rely on the Accept-Language header.

Also, what if a downstream process requires a separator character that is not supported by a Accept-Language, such as the Tab character?

[dosse]

Locales define both delimiters: the field/column separator and the decimal separator. Using the German locale would result in using a semicolon for the column separator and a comma for the decimal separator.

I'm not sure that adding another SDMX parameter is justified because (very few) downstream processes are unable to read standard CSV files built according to their system's (or any other pre-defined) locale. For tab character separators, an SDMX-TSV format would be needed, and here again, I'm not convinced that such a flexibility needs to be supported by SDMX.

In summary, rather than making the SDMX standard more complex by adding more parameters and file flavors, there is the alternative that fosters usability through simplicity and standardisation: The vast majority of client tools are perfectly fine with standard localised CSV files. The other ones should probably be upgraded.