CSV question

I wrote a PowerBasic program to evaluate 512 cases of a switched resistor matrix, a mess of resistors and 9 SSRs. Some other people can make choices (like, pick their favorite 32 values) so I figured I'd output as a CSV file, which they might like.

The whole CSV/spreadsheet thing is primitive.

Well, you can feed spreadsheets from databases :-D

Everybody knows how to do a spreadsheet, but not everybody knows how to do a database.

You may not have database software. I think that M$ Office did not always include it. And LO Base is not that intuitive.

Read this:

CSV doesn't have comments, just put it in as text.

The standard fir CSV is RFC 4180.

You can figure out a way to do that without columns ;-)

...

Infinite ways. As text, for example. As a huge number.

If you want to put some explanatory text info blurb on the front you can just do it as is provided that there are no commas in the text. If there are then enclose the thing in double quotes.

A leading single quote ' will act as a comment delimiter for the rest of the line (but beware of commas).

Better control can be had by including all strings in double quotes " and escaping in any double quotes as "".

CSV might not be the most user friendly choice though these days. Tab delimited is one of the Excel defaults and that is much more readable on a dumb terminal. That said I more often see space delimited files.

Having text and column headings in quotes also makes the CSV/tabbed files compatible with the likes of Gnu plot.

---sample of most common forms of text inclusion and mistakes ---

' Full line comment, and contains escaped double quotes "and works OK" "Heading1","heading2","heading3" '

1.2 2.4 5678 or 1.2,2.4,5678,"which would you prefer?" This is plain text on its own "This contains ""quoted"" text" This might not do what you expect, or then it might

--end--

Columns in tab delimited form rather than csv. I find it much easier for browsing data files. Size is the same but readability on a dumb terminal or in notepad or nano greatly enhanced.

One other useful trick is that in most cases ,, can be used as a shorthand for 0, although the cell will be blank rather than 0. (empty cells evaluate to numerical zero). It can be a huge saving in image photon counting files where the majority of cells are zero.

The cells of a spreadsheet are not "normally numeric" they are capable of taking on any value that the computer can represent.

A few lines of text at the top with no commas in will be fine. Your problem arises when something else expects the first line of the file to contains headings in some particular format (eg Gnu plot).

They are extremely powerful tools for mocking up test data and having a quick look see at modest amounts of experimental data.

The best thing about Excel or any other spreadsheet or scratchpad for testing algorithms is that mistakes made in a spreadsheet are completely orthogonal to those made in a procedural programming language.

You also have the oddity of Excel computing in FP and rounding to strictly 15 decimal digits for display. This can sometimes cause annoyance if you are working to machine precision. I have a work around found by trial and error when I noticed an unusual value one day...

An example to demonstrate this behaviour (staring in A1):

column A column B column C

=1-2^ROW() =1-A1 =(1-A1)

And drag them down to row 55

You will see that column B is rounded to 15 decimal digits but column C is full machine precision 53 mantissa. This can be very useful. But it is quite arcane and not so far as I know documented Excel behaviour.

But then MS the help system would define "Help" as "He" the masculine pronoun "LP" obsolete black vinyl music disk now making a comeback.

KISS or possibly RTFM?

(ducking)

John ;-#)#

Just write several lines.

A MAC is just a long hexadecimal number that is displayed differently. I'm sure it can be done if the sheet software can handle hex. Mine doesn't.

I keep my "list of books" as a database with several related tables. I'm tempted to migrate to an spreadsheet, because, for instance, I can thus carry it in my phone.

Or just several rows.

Just use a spreadsheet software that allows 12 hex digits, and have a validation rule or macro.

No, I don't know of such software, but it is not an intrinsic limitation, it can be done.

Maybe some spreadsheet software can be coaxed to display an integer as hex.

Yes, they can. Just write the MAC you wish. Of course they will clash if they see one another, but you can wrongly program them like that.

But you can have that validation in a macro.

If one "has" to use a spreadsheet, one will find a way to do things.

I need to carry my list of books when I go shopping. If I don't have a phone, I print it. I need the list or I can buy a repeated tittle by mistake.

Thus, I do have a phone. And it does have spreadsheet software.

Not so much typeless as can be of any valid data type.

The peculiar thing here is that for reasons best known to itself Excel DEC2HEX fails at 2^39 exactly). IOW

=DEC2HEX(2^39-1) works ok and shows 7FFFFFFFFF =DEC2HEX(2^39) fails #NUM!

This is very odd when the standard float type can exactly represent any power of two up to 2^(512+53) and the mantissa of a 64 bit FP can exactly represent all numbers up to 2^54 (there is a hidden in the hardware implicit leading 1 in all non-zero 64 bit FP values).

My pet hate on websites is having to put in a credit card number without the spaces which makes it much easier to have an undetected typo. Groups of four numbers separated by a space are trivial to flash read. It isn't like removing spaces from a string is rocket science!

My other pet hate is collecting UK train tickets where in addition to putting the card that you used to buy the train tickets you also have to enter a high entropy code of 24 characters which does its best to include one of all the ambiguos ones 1,I,l,0,O,5,S etc.

The delays this causes as people fight the stupid robotic ticket dispenser to get their tickets is crazy! Now you can at finally download them to a phone and avoid this queuing mess.

I have seen way too many spreadsheets used as databases causing chaos. It starts out OK with a smallish prototype project and the grows like topsy until one day no-one knows how it works or how to maintain it.

Upto a couple of thousand records it doesn't make much difference either way but go much beyond that and it is "here be dragons" territory.

+1

Spreadsheets are easier for accounting and management types to understand though - DBMS has a much steeper learning curve.

I rely on being able to remember what the cover looks like or now with google and wiki having so much online some key phrase I can recall.

LibreOffice calc, Linux:

-1 2 2

-3 4 4

-7 8 8

-15 16 16

-31 32 32

-63 64 64

-127 128 128

-255 256 256

-511 512 512

-1023 1024 1024

-2047 2048 2048

-4095 4096 4096

-8191 8192 8192

-16383 16384 16384

-32767 32768 32768

-65535 65536 65536

-131071 131072 131072

-262143 262144 262144

-524287 524288 524288

-1048575 1048576 1048576

-2097151 2097152 2097152

-4194303 4194304 4194304

-8388607 8388608 8388608

-16777215 16777216 16777216

-33554431 33554432 33554432

-67108863 67108864 67108864

-134217727 134217728 134217728

-268435455 268435456 268435456

-536870911 536870912 536870912

-1073741823 1073741824 1073741824

-2147483647 2147483648 2147483648

-4294967295 4294967296 4294967296

-8589934591 8589934592 8589934592

-17179869183 17179869184 17179869184

-34359738367 34359738368 34359738368

-68719476735 68719476736 68719476736

-137438953471 137438953472 137438953472

-274877906943 274877906944 274877906944

-549755813887 549755813888 549755813888

-1099511627775 1099511627776 1099511627776

-2199023255551 2199023255552 2199023255552

-4398046511103 4398046511104 4398046511104

-8796093022207 8796093022208 8796093022208

-17592186044415 17592186044416 17592186044416

-35184372088831 35184372088832 35184372088832

-70368744177663 70368744177664 70368744177664

-140737488355327 140737488355328 140737488355328

-281474976710655 281474976710656 281474976710656

-562949953421311 562949953421312 562949953421312

-1125899906842623 1125899906842624 1125899906842624

-2251799813685247 2251799813685248 2251799813685248

-4503599627370495 4503599627370496 4503599627370496

-9007199254740991 9,00719925474099E+015 9,00719925474099E+015

-1,8014398509482E+016 1,8014398509482E+016 1,8014398509482E+016

-3,6028797018964E+016 3,6028797018964E+016 3,6028797018964E+016

My typo! The first entry should read

=1-2^-ROW()

The object being to subtract powers of (1/2)^N from 1 until you hit the machine precision limit. I'd expect all Excel clones to do the same. (otherwise you get subtle errors in spreadsheets on other platforms)

+1

Oh. :-(

M$ Access had very good stats and reporting features, for writing reports for the bosses. At least when I used it by 1998.

Ah.

Now I get:

0,5 0,5 0,5 0,75 0,25 0,25 0,875 0,125 0,125 0,9375 0,0625 0,0625 0,96875 0,03125 0,03125 0,984375 0,015625 0,015625 0,9921875 0,0078125 0,0078125 0,99609375 0,00390625 0,00390625 0,998046875 0,001953125 0,001953125 0,9990234375 0,0009765625 0,0009765625 0,99951171875 0,00048828125 0,00048828125 0,999755859375 0,000244140625 0,000244140625 0,9998779296875 0,0001220703125 0,0001220703125 0,99993896484375 0,00006103515625 0,00006103515625 0,999969482421875 0,000030517578125 0,000030517578125 0,999984741210938 0,0000152587890625 0,0000152587890625 0,999992370605469 7,62939453125E-06 7,62939453125E-06 0,999996185302734 3,814697265625E-06 3,814697265625E-06 0,999998092651367 1,9073486328125E-06 1,9073486328125E-06 0,999999046325684 9,5367431640625E-07 9,5367431640625E-07 0,999999523162842 4,76837158203125E-07 4,76837158203125E-07 0,999999761581421 2,38418579101563E-07 2,38418579101563E-07 0,99999988079071 1,19209289550781E-07 1,19209289550781E-07 0,999999940395355 5,96046447753906E-08 5,96046447753906E-08 0,999999970197678 2,98023223876953E-08 2,98023223876953E-08 0,999999985098839 1,49011611938477E-08 1,49011611938477E-08 0,999999992549419 7,45058059692383E-09 7,45058059692383E-09 0,99999999627471 3,72529029846191E-09 3,72529029846191E-09 0,999999998137355 1,86264514923096E-09 1,86264514923096E-09 0,999999999068677 9,31322574615479E-10 9,31322574615479E-10 0,999999999534339 4,65661287307739E-10 4,65661287307739E-10 0,999999999767169 2,3283064365387E-10 2,3283064365387E-10 0,999999999883585 1,16415321826935E-10 1,16415321826935E-10 0,999999999941792 5,82076609134674E-11 5,82076609134674E-11 0,999999999970896 2,91038304567337E-11 2,91038304567337E-11 0,999999999985448 1,45519152283669E-11 1,45519152283669E-11 0,999999999992724 7,27595761418343E-12 7,27595761418343E-12 0,999999999996362 3,63797880709171E-12 3,63797880709171E-12 0,999999999998181 1,81898940354586E-12 1,81898940354586E-12 0,999999999999091 9,09494701772928E-13 9,09494701772928E-13 0,999999999999545 4,54747350886464E-13 4,54747350886464E-13 0,999999999999773 2,27373675443232E-13 2,27373675443232E-13 0,999999999999886 1,13686837721616E-13 1,13686837721616E-13 0,999999999999943 5,6843418860808E-14 5,6843418860808E-14 0,999999999999972 2,8421709430404E-14 2,8421709430404E-14 0,999999999999986 1,4210854715202E-14 1,4210854715202E-14 0,999999999999993 7,105427357601E-15 7,105427357601E-15 0,999999999999996 3,5527136788005E-15 3,5527136788005E-15 0,999999999999998 0 0 0,999999999999999 0 0 1 0 0 1 0 0 1 0 0 1 0 0 1 0 0 1 0 0

That's better.

:-)

:-)

No, I'm not arguing which is better, just that it can be done.

Well, as I don't use Windows I can't have M$ Access, and LO Base is not that good.

Well, my repeats are wasting space in my shelves, I have not been able to gift it. And they are a waste of my money, and a chagrin, not been able to enjoy a reading.

Caveat: they happen to be in English, and few people here enjoy reading books in English.

In fact, I want to donate many old books and I still have not found how/to whom.

Oh, I go to the library to find new tittles that I feel I might enjoy, then buy them online as epubs.

Pet hate: not being able to buy those epubs at the physical library that gave me an hour of pleasure looking around the aisles for new books. It is stupid of them.

Man-Kzin Wars XIV Cibola Burn By: James S. A. Corey Road Rage By: Ruth Rendell Simisola By: Ruth Rendell Sad Cypress (Poirot) By: Agatha Christie The Secret Adversary & And Then There We... By: Agatha Christie Ender's Game By: Orson Scott Card Last Seen Wearing By: Colin Dexter Abaddon's Gate By: James S. A. Corey Caliban's War De: James S. A. Corey Sidi, de Arturo Pérez-Reverte

Kobo is stupid.

When I open their site, they geolocalize me and offer me books in Spanish, despite me only buying from them books in English and having configured my preferences to English.

(Books in Spanish I buy from another provider, local).

LOL

...

[snip]

Actually no it's not.

There are multiple levels of non-compliance to the Excel de facto standard even in this very simple test. This is what the answer should look like in Excel 2010 which is what I happen to have on the machine I'm using right now. It is exactly the same on XL2021 too.

N x=1-2^-N =1-x =(1-x)

45 0.9999999999999720 2.84217E-14 2.84217E-14 46 0.9999999999999860 1.42109E-14 1.42109E-14 47 0.9999999999999930 7.10543E-15 7.10543E-15 48 0.9999999999999960 3.55271E-15 3.55271E-15 49 0.9999999999999980 1.77636E-15 1.77636E-15 50 0.9999999999999990 0 8.88178E-16 51 1.0000000000000000 0 4.44089E-16 52 1.0000000000000000 0 2.22045E-16 53 1.0000000000000000 0 1.11022E-16 54 1.0000000000000000 0 0

(I've added a few tabs to align columns)

Note that it should get 2^-49 OK with 15 digit rounding, and it should go right down to full machine precision when there are brackets around the expression. The latter is AFAIK undocumented but useful behaviour.

I don't like what it does to N=50. It clearly can represent 1-2^-50 OK and displays it as different to 1 but then says 1-x == 0. Inconsistent!! I'd have preferred them to put the threshold one factor of two lower. ie. so 8.88e-16 rounds up to 1e-15.

if you have Excel 2021 or 365, then the function to use is TEXTJOIN

put your strings in a contiguous range of cells say, A10:X10 in Z10 enter =TEXTJOIN(",",1,A1-:X10)

Oh, if you dont have Excel 2021 or 365, then Google Sheets will work too. regards, Rich S.

(I re-posted with a typo corrected)

if you have Excel 2021 or 365, then the function to use is TEXTJOIN

put your strings in a contiguous range of cells say, A10:X10 in Z10 enter =TEXTJOIN(",", 1, A1:X10)

Oh, if you dont have Excel 2021 or 365, then Google Sheets will work too.

The first line in the CSV file should be the header names of course. Must use database friendly header names, IME. The headers must use consist of only letters, numbers, and the underscore character. Must begin with a letter, typically.

also, check what file format the receiving system needs. e.g., in the databases I work with, it is "MacOS" CSV file format, with UTF-8 encoding -- not Windows default CSV -- (MacOS uses CR as EOL, and CR+LF as EOF).

regards, Rich S.