mikeb
Posts: 1,053 
Likes: 463
|
Post by mikeb on Jan 3, 2015 19:32:28 GMT
CSV is a standard? Not in my experience, use and abuse of quotes, embedded quotes, commas, and embedded commas is rife, and I have never found a certified or universally agreed definition. For example, one can import a CSV file in to EXCEL and then export the same data to CSV and the files are different. Also, as the format incorporates no reliable formatting information one is often at the mercy of the client import/export "wizard". For example a column containing quoted string data will be interpreted by EXCEL as numbers and may be presented in exponential format. Nevertheless, CSV is compact, can be read in Notepad, and can usually be made to work satisfactorily. I used OpenOffice and later LibreOffice for some years but eventually returned to MS Office because MS EXCEL is just better, and MS Office is much more widely used and understood. Exchanging workbooks and documents is not straightforward and reliable either in ODS/ODF or DOC/XLS/DOCX/XLSX. I still have workbooks that lost all the formulae when transferred from one package to another and I never found a simple and reliable document protection system that I could use to transfer ODS/ODF files to other parties not using OO that did not require them to install software. CSV is a standard. It's more likely to be readable than .XLS etc. Just because you have experienced incompetent generation and processing of a very simple file format does not negate that  -- I'm sure somewhere Microsoft have created a program that can't even load and save a text file (plain ASCII) without changing it, adding a Microsoft tweak, or just corrupting it outright. Then there's the endless "I've written my own JPG/WAV/other loader it's really simple and always works" people, who have missed the subtleties of the file format spec and made (unknowingly) a Horlicks of it. It doesn't mean the file format is bad, just the implementation. If people are creating .CSV files with loose commas in the data, then that's a bad thing. The fact that you load into into Excel, it "interprets" it, and then saves changed data is something you should take up with Microsoft. It's not a failing of the file format. I'm aware of the effect though! "Formatting information" is just fluff around the raw data. If you really want to preserve that, CSV is not for you - I agree. It's a bit like people using plain ASCII email (as it was specified) and then spending hours tarting up the colours, fonts, sizes ... great, but that's never going to make it through to a real email client But for accessing the raw data from a P2P site, I don't need someone else's amazing colour scheme and font preferences thrust on me. There's a reason it's not easy to exchange documents between MS-Excel and OpenSource stuff. It's the same reason you can't guarantee to exchange data between MS-Excel and MS-Excel. Spot the common factor there. MS have a vested interest in keeping you locked into their software (as you have admitted you have ended up -- OpenSource stuff is trying to play catchup, and sometimes overtaking.
|
|
adrianc
Member of DD Central
Posts: 9,036
Likes: 4,838
|
Post by adrianc on Jan 4, 2015 9:11:07 GMT
There's a reason it's not easy to exchange documents between MS-Excel and OpenSource stuff. It's the same reason you can't guarantee to exchange data between MS-Excel and MS-Excel. Spot the common factor there. MS have a vested interest in keeping you locked into their software (as you have admitted you have ended up -- OpenSource stuff is trying to play catchup, and sometimes overtaking. It's true... SWMBO had a job application form some years back. .DOC, with protected fields and "stuff", and definitely created in an older version of MSWord. In a more recent version, it opened as somewhere in the region of FOUR HUNDRED pages... Utterly unusable. LibreOffice? Sure, the formatting was a bit out (largely because of fonts requested which weren't available, so were substituted for defaults), but it worked. Want formatting to be consistent? Use PDF.
|
|
Steerpike
Member of DD Central
Posts: 1,962
Likes: 1,680
|
Post by Steerpike on Jan 4, 2015 10:12:32 GMT
CSV is a standard? Not in my experience, use and abuse of quotes, embedded quotes, commas, and embedded commas is rife, and I have never found a certified or universally agreed definition. For example, one can import a CSV file in to EXCEL and then export the same data to CSV and the files are different. Also, as the format incorporates no reliable formatting information one is often at the mercy of the client import/export "wizard". For example a column containing quoted string data will be interpreted by EXCEL as numbers and may be presented in exponential format. CSV is a standard. It's more likely to be readable than .XLS etc. CSV is not a standard in the way that I define a standard. In many years of experience I have rarely found consistent handling of embedded quotes or embedded commas or embedded line feeds, and the format has no mechanism for defining the character set used. Whether anyone likes it or not many folk use EXCEL and so the way that EXCEL handles CSV really matters. The main reason that I returned to EXCEL after years of using OO/LO is that it is just better than the OO/LO alternative. Oh, and I have written code to read and write BIFF format so I know what a heap it is.
|
|
pikestaff
Member of DD Central
Posts: 2,139
Likes: 1,486
|
Post by pikestaff on Jan 4, 2015 10:34:44 GMT
|
|
|
|
Post by GSV3MIaC on Jan 4, 2015 11:20:17 GMT
I vote for .csv as it's easier (!?) to open programmatically, examine by eyeball, screw around with using wordpad, etc, however the comments about 'standard, NOT!' are quite correct .. you often have to know what the answer is supposed to be in order to deal with it properly .. if only there was a separator which was guaranteed not to turn up in the data items!! The various XML-style formats are, IMO, horrible, although they do compress nicely (<joke type="unfunny"> well they'd have to </joke>) and don't suffer from the same set of incompatibilities as .csv .. they come with new sets instead.
|
|
|
|
Post by tybalt on Jan 4, 2015 11:44:19 GMT
Prior to the software downgrade ThinCats had almost every report available to export as either text or top Excel. It worked did not require programing and was tested by their members.
I can and have worked with CSV from their current system and find it a pain. I suspect that without the £1000 arbitrary minimum I might end up needing CSV to track things but at the moment bar a variable rates on the Second Hand Market there is precious little more that I need.
|
|
shimself
Member of DD Central
Posts: 2,561
Likes: 1,170
|
Post by shimself on Jan 4, 2015 16:12:46 GMT
I've voted for CSV, mainly because I find that more-often-than-not spreadsheets in .xls format are rarely properly formatted in the colums/rows/cells. If I am going to have to mess around with the download to make it useful then I would prefer to start from CSV. For example consider the download for "MLIA Statement Summary" from AC, specifically the .xls version. It would be helpful if column A was formatted as a number. Similarly column E should be a number or currency to two decimal places. I'm not sure about column B. Format wise, it doesn't seem to be a date or a time. ... I've just taken a look at the AC download in question. It's actually html. extract "....html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><meta name=ProgId content=Excel.Sheet><meta name=Generator content="https://github.com/sonata-project/exporter"></head><body><table><tr><th>id</th><th>created_at</th><th>status</th><th>narrative</th><th>amount</th><th>for_assetz_capital_account</th></tr><tr><td>1184137</td><td>2014-07-30 07:38:50.455038+01</td><td>settyadda yadda"
So I'm amazed my Excel 97 reads it but it does just fine. It's generated by open source (see ref to github etc etc) It doesn't contain any data type formatting (number date etc) just simple html presentation formatting ( Bold Italic etc) My excel reads column A as number, as does my Open Office (mind you it's just a label so I don't see why you want it as a number but anyway). I agree column B is a pig but it does sort correctly. I disagree about truncating column E to 2dp, AC work to many dp so you might get rounding errors. The major advantage over csv is that everything will be in the right column first time every time. I think that's a big plus.
|
|
|
|
Post by longjohn on Jan 4, 2015 17:11:08 GMT
I've just taken a look at the AC download in question. It's actually html. extract "....html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><meta name=ProgId content=Excel.Sheet><meta name=Generator content="https://github.com/sonata-project/exporter"></head><body><table><tr><th>id</th><th>created_at</th><th>status</th><th>narrative</th><th>amount</th><th>for_assetz_capital_account</th></tr><tr><td>1184137</td><td>2014-07-30 07:38:50.455038+01</td><td>settyadda yadda"
So I'm amazed my Excel 97 reads it but it does just fine. It's generated by open source (see ref to github etc etc) It doesn't contain any data type formatting (number date etc) just simple html presentation formatting ( Bold Italic etc) My excel reads column A as number, as does my Open Office (mind you it's just a label so I don't see why you want it as a number but anyway). I agree column B is a pig but it does sort correctly. I disagree about truncating column E to 2dp, AC work to many dp so you might get rounding errors. The major advantage over csv is that everything will be in the right column first time every time. I think that's a big plus. I agree. However it would nice if it was advertised as a html download that Excel will open rather than as a native .xls file. That way no one would be expecting the bells, whistles and formatting of the real thing. It's quite common for downloads to be in csv or html format whilst pretending to be something else. Excel reads in the first 10 lines and uses the data to determine what format (General, Number, Date etc) will be applied to each column. Normally very handy but when faced with a list phone numbers or other data with leading zeros can make a huge mess. It can be useful to open a .csv into Notepad and add a few spaces into the top rows of numerical data then saving it so when opened in Excel the data is kept as 'text'. You can then remove the spaces and format the columns as you wish. John
|
|
|
|
Post by westcountryfunder on Jan 4, 2015 17:39:21 GMT
Well that's fascinating.
So I suggest it would be helpful if AC could actually create an Excel spreadsheet (or preferably .ods since they seem to be into open source) using the info in their database, tidy it up complete with some formatting, and THEN offer it to us as a download.
We surely shouldn't need to be computer experts to make use of information which is about our personal accounts with them.
|
|
Steerpike
Member of DD Central
Posts: 1,962
Likes: 1,680
|
Post by Steerpike on Jan 5, 2015 14:19:34 GMT
I've just taken a look at the AC download in question. It's actually html. The major advantage over csv is that everything will be in the right column first time every time. I think that's a big plus. Excel reads in the first 10 lines and uses the data to determine what format (General, Number, Date etc) will be applied to each column. Normally very handy but when faced with a list phone numbers or other data with leading zeros can make a huge mess. It can be useful to open a .csv into Notepad and add a few spaces into the top rows of numerical data then saving it so when opened in Excel the data is kept as 'text'. You can then remove the spaces and format the columns as you wish. John Another trick is to change the file type from CSV to something else before opening it in EXCEL, this avoids the "wizard" and allows one to manually control the text to columns process.
|
|
|
|
Post by GSV3MIaC on Jan 5, 2015 16:51:21 GMT
Of course the default for Vista, Win7, 8 etc. is to hide the file type (extension) from the user anyway. I expect pretty soon micro$oft will come up with some policy lock to stop you from changing it...
|
|
Steerpike
Member of DD Central
Posts: 1,962
Likes: 1,680
|
Post by Steerpike on Jan 5, 2015 17:11:10 GMT
To be fair, if you don't know how or can't work out how to make file types visible in Windows then probably you should not be provided with the tools to change them.
|
|
-- I'm sure somewhere Microsoft have created a program that can't even load and save a text file (plain ASCII) without changing it, adding a Microsoft tweak, or just corrupting it outright. Then there's the endless "I've written my own JPG/WAV/other loader it's really simple and always works" people, who have missed the subtleties of the file format spec and made (unknowingly) a Horlicks of it. It doesn't mean the file format is bad, just the implementation. If people are creating .CSV files with loose commas in the data, then that's a bad thing.
