Re: [[email protected]: Re: to csv or not to csv]
Emmanuel Pacaud <[email protected]> Tue, 06 Oct 2020 07:18:14 +0200
| Newsgroups | gmane.comp.gnome.apps.gnumeric |
|---|---|
| Message-ID | <[email protected]> |
Damn! Obviously I didn't read the article up to the end, there is an
update that says the issue may not be related to CSV. That sounded
plausible though.
Cheers,
Emmanuel.
Le mar. 6 oct. 2020 à 7:12, Emmanuel Pacaud
<[email protected]> a écrit :
> Hi,
>
> While reading this message exchange, I was a bit on the side "CSV is
> helpfull, but troubles are not far away". As a french locale user, my
> favorite issue is number internationalization.
>
> And I have stumbled upon this article:
> <https://arstechnica.com/tech-policy/2020/10/excel-glitch-may-have-caused-uk-to-underreport-covid-19-cases-by-15841/>
>
> Cheers,
>
> Emmanuel.
>
> Le lun. 5 oct. 2020 à 12:09, User Hayden via gnumeric-list
> <[email protected]> a écrit :
>>
>> I'm in statistics and grab CSV files from all over the world. Like
>> John, I have experienced very few problems. I usually open these in
>> a
>> spreadsheet, clean as needed, then save as CSV and open in R. Any
>> problems are usually because the creator did not follow the rules,
>> vague tho they may be. I also find it a huge advantage that most CSV
>> files can be opened in a plain text editor and are not full of
>> cryptic
>> markup or, worse, are binary files. To me CSV files seem much less
>> troublesome than any spreadsheet file format.
>>
>>
>>
>> ----- Forwarded message from John Denker via gnumeric-list
>> <[email protected] <mailto:[email protected]>> -----
>>
>> Date: Mon, 5 Oct 2020 08:19:11 -0700
>> From: John Denker via gnumeric-list <[email protected]
>> <mailto:[email protected]>>
>> To: gnumeric-list <[email protected]
>> <mailto:[email protected]>>
>> Subject: Re: to csv or not to csv
>> User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
>> Thunderbird/68.10.0
>>
>> On 10/4/20 8:34 PM, aguelzow wrote:
>>>
>>> The issue is that csv is not very well defined.
>>
>> That has never been a problem in the work that I do.
>> I can send and receive .csv with no problems.
>>
>>> So define your own text file format
>>
>> 1) As previously stated, literally every day I receive CSV files
>> from outside sources. The format is their choice, not mine.
>>
>> 2) So I hereby define my own personal text file format to be
>> comma separated values, as defined in RFC 4180 with a couple
>> of well-known relaxations(*).
>>
>> <https://tools.ietf.org/html/rfc4180>
>>
>> I do not see any advantage to choosing any other text-file format.
>> Any other choice would be *less* standardized and *less* portable.
>>
>> *) The relaxations are:
>> -- <CR> at end of line is optional. That is, unquoted <LF> is
>> accepted in lieu of the RFC <CR><LF>. Every CSV parser I've
>> ever seen accepts this.
>> -- The RFC requirement that all lines have the same number of fields
>> is not enforced.
>>
>> ==========================================
>>
>>
>> On 10/4/20 8:26 PM, Tim Chase wrote:
>>>
>>> CSV files come with lots of potential issues, mostly revolving
>>> around
>>> a lack of standardization:
>>>
>>> - encoding may or may not be specified (is this UTF8? UTF16? UTF32?
>>> Latin1? Windows-1252? any of a gazillion other encodings?)
>>
>> That has never been a problem for me. I doubt it is much of a
>> problem
>> for anybody else. CSV is transparent and agnostic to any of the
>> byte-
>> based encodings you mention, because the only characters it cares
>> about
>> are <comma>, <doubleQuote>, and unquoted <CR><LF> ... which are the
>> same
>> in UTF-8, Latin1, Windows-1252, and a great many other encodings.
>>
>>> - how do you quote the quote character (doubling it, escaping with
>>> a
>>> backslash, encoded with some other escape method, ...)
>>
>> You double it, as specified in the RFC.
>> This has never been a problem in the work that I do.
>>
>>> - does it distinguish between an empty value and an empty quoted
>>> value? (sometimes the former means Null while the latter means an
>>> empty string; other times they're the same)
>>
>> a) In accordance with the RFC, CSV does not distinguish.
>>
>> b) FWIW I have a C++ version of the parser that is templated. You
>> can
>> ask it to return strings *or* a derived class (derived from string)
>> that
>> has an extra bit that says whether the value was quoted. This
>> allows me
>> to write a program that performs the identity transformation on CSV
>> files,
>> not just semantically identical but verbatim and literatim
>> identical. I
>> have never used this feature except for the exceedingly narrow
>> purpose of
>> testing the parser. So again I say, this has never been a problem
>> in the
>> work that I do.
>>
>>> - should one expect headers? If so, does case matter? Does order
>>> matter? (I often have columns move around but if accessed by
>>> header, they're adequately consistent)
>>
>> Sometimes there are headers. Sometimes not. Sometimes there are
>> headers
>> that can be ignored. This has never been a problem in the work that
>> I do.
>>
>>> - can more than one column have the same header?
>>
>> The RFC doesn't say. This is a higher-level issue, above the CSV
>> layer.
>>
>>> - what should happen if a row has fewer entries than the header
>>> row?
>>
>> The RFC says the array should be rectangular. However, world does
>> not end
>> if the array is non-rectangular:
>>
>> An,old,silent,pond
>> A,frog,jumps,into,the,pond???
>> Splash!,Silence,again.
>>
>> The lack of headers and the non-rectangularness is not an issue for
>> the CSV
>> parser.
>>
>>> - what should happen if a row has *more* entries than the header
>>> row?
>>
>> See previous answer.
>>
>> Also note that I have a utility that operates at the aforementioned
>> higher level, taking a rectangular CSV file with headers and converts
>> it to SQL. This higher level requires the headers to be well behaved,
>> but again this is not within the remit of the CSV parser.
>>
>>> - what should happen if there's no header row, but rows don't have
>>> the same number of columns?
>>
>> See above.
>>
>>> - parsing with some tools like awk(1) can become tedious when the
>>> comma-delimiter can appear within the data (so you have to
>>> special-case the quoting)
>>
>> That is one of the eleventeen reasons why I don't use awk to parse
>> CSV
>> files. I use perl and C++. I have libraries that parse CSV properly.
>>
>>> - is the end-of-line character a Unix "LF", a DOS "CR/LF", an old
>>> Mac
>>> "CR", or the largely-unused Record Separator (RS=0x30)
>>
>> The RFC mandates <CR><LF> but plain <LF> is AFAICT universally
>> accepted
>> also.
>>
>>> - what happens if data contains newlines in it? does odd quoting
>>> mean that the row is continued on the next line?
>>
>> The RFC makes it clear that newlines can appear in quoted strings.
>> This is not an issue in the work that I do. I have never encountered
>> a quoted newline except when testing my parser.
>>
>>> - sometimes things are called CSV when they use alternate
>>> delimiters
>>> such as tab (though often called TSV files), pipe, colon, or
>>> whatever other delimiter character that comes up on a whim
>>
>> This is not a problem in the work that I do.
>> It is not a problem with CSV.
>> Garbage in, garbage out.
>>
>>> - the data is largely 2d only, so there's no mechanism for
>>> including
>>> multiple sheets of data other than multiple files
>>
>> That brings us back to my original question: Suppose I have multi-
>> dimensional data. What format should I use for exporting it from
>> gnumeric?
>>
>>> If you know the answers to those questions above for your data in
>>> question or haven't hit any of those issues, and you know that the
>>> file-format is predictable, then I would treat the "don't use CSV
>>> files" as more of an admonition to know what you're doing.
>>
>> OK.
>>
>> Please consider the possibility that I sometimes know what I'm doing.
>>
>>> if something breaks, you get to keep all the pieces. It's an
>>> unfortunately underdefined (but common) means for transmitting
>>> data.
>>> There are better ways, but <opinion class=controversial>like PHP,
>>> JavaScript, and MySQL, they are used because they're popular, not
>>> because they're particularly good; I use PHP, JavaScript,
>>> MySQL, and CSV files for their ubiquity, not their
>>> excellence.</opinion> So use guilt-free, but use with caution.
>>
>> Is there a way to export SQL from gnumeric? I don't see it mentioned
>> in the documentation.
>>
>> By javascript I assume you mean JSON. Have you had good luck parsing
>> that using awk? Is there a way to export JSON from gnumeric?
>> _______________________________________________
>> gnumeric-list mailing list
>> [email protected] <mailto:[email protected]>
>> <https://mail.gnome.org/mailman/listinfo/gnumeric-list>
>>
>> ----- End forwarded message -----
>>
>> --
>>
>> _
>> | | Robert W. Hayden
>> | | 5 Howard Street, Apartment 206
>> / | Wilton, New Hampshire 03086 USA
>> | |
>> | | email: bob@ the site below
>> / | website: http://statland.org <http://statland.org/>
>> | x /
>> ''''''
>>
>>
>> _______________________________________________
>> gnumeric-list mailing list
>> [email protected] <mailto:[email protected]>
>> <https://mail.gnome.org/mailman/listinfo/gnumeric-list>
_______________________________________________
gnumeric-list mailing list
[email protected]
https://mail.gnome.org/mailman/listinfo/gnumeric-list