csv vs xls ?

[stevensfo]

Over the holidays I've decided to start going through my AJBell and ii ISAs to plan a bit of slow pruning and consolidation. Over 30 years, I've collected more holdings than the stamps in my old album and it's getting embarrassing, as well as complicated and just plain silly.

I downloaded all the info but it's in csv format. No problem, since I tidied up the columns, added an extra for dividends and resaved as xls.

But what is the difference? I've heard of csv before but I think I've only ever used xls.


Steve

[doolally]

XLS files are generally limited to Excel (though may be usable by some other applications such as LibreOffice)

CSV (Comma Separated Values) can be opened by any application (excel, LibreOffice, Lotus123, text editors, etc)
doolally
edit....oh, and CSV files only contain the data values. No charts, pivots and whatever

[stevensfo]

XLS files are generally limited to Excel (though may be usable by some other applications such as LibreOffice)

CSV (Comma Separated Values) can be opened by any application (excel, LibreOffice, Lotus123, text editors, etc)
doolally
edit....oh, and CSV files only contain the data values. No charts, pivots and whatever

Ah, okay, thanks!

No need for charts or stuff like that. Yes, LibreOffice opens xls and word docs with no problem.

Lotus 123?? Crikey, that takes me back! 1995? The good old days when a PC with a 1GB hard drive was considered humungous.

Steve

[kiloran]

XLS files are generally limited to Excel (though may be usable by some other applications such as LibreOffice)

CSV (Comma Separated Values) can be opened by any application (excel, LibreOffice, Lotus123, text editors, etc)
doolally
edit....oh, and CSV files only contain the data values. No charts, pivots and whatever

Ah, okay, thanks!

No need for charts or stuff like that. Yes, LibreOffice opens xls and word docs with no problem.

Lotus 123?? Crikey, that takes me back! 1995? The good old days when a PC with a 1GB hard drive was considered humungous.

Steve

I would add the proviso that LibreOffice opens xls with no problem in many, but not all, cases. But the more xls functions you throw in (fancy charts, pivots, data sources, etc) the less chance of LibreOffice handling it correctly. It does a good job, but it does have limits.
Yes, I remember Lotus 1-2-3 from back in the 80s when we first started using PCs instead of terminals on the mainframe. It seemed magical at the time.

--kiloran

[the0ni0nking]

One of the big differences is file size.

In a former role, I had responsibility for the provision of huge data sets (multi billion securitsations on mortgages) where we had to provide data on each individual mortgages such as loan size, repayment amount, DIA, MIA, PIIs, seasoning and a whole raft of other "stuff" such as whether it was a PDH or BTL as well as exposure to individuals/cos both in terms of value and number .... also probably a raft of other acronyms I've since forgotten.

They were all contractual requirements - not sure who to whether the originator, issuer or servicer - but it was ultimately provided in a csv file for them to do as they saw fit.

Ultimately, we also needed it for invoicing purposes as it also contained data on some specific activity based fees. So many commas ..... I need a drink!

[stevensfo]

Ah, okay, thanks!

No need for charts or stuff like that. Yes, LibreOffice opens xls and word docs with no problem.

Lotus 123?? Crikey, that takes me back! 1995? The good old days when a PC with a 1GB hard drive was considered humungous.

Steve

I would add the proviso that LibreOffice opens xls with no problem in many, but not all, cases. But the more xls functions you throw in (fancy charts, pivots, data sources, etc) the less chance of LibreOffice handling it correctly. It does a good job, but it does have limits.
Yes, I remember Lotus 1-2-3 from back in the 80s when we first started using PCs instead of terminals on the mainframe. It seemed magical at the time.

--kiloran

Funnily enough, it was Lotus that opened my eyes to privacy/security issues.

Around 2000, somebody sent me a letter in Word but I didn't have Word at that time, so tried opening it with Lotus.

I not only opened his letter but loads of other stuff as well, remnants of other letters, some rather personal!

From what I remember of explanations at the time, he had deleted text and used the same doc to write his letter. Lotus for some reason, was able to read the deleted text as well.

So these days, I always open a clean Word doc, or LibreOffice.

Steve

[mc2fool]

XLS files are generally limited to Excel (though may be usable by some other applications such as LibreOffice)

CSV (Comma Separated Values) can be opened by any application (excel, LibreOffice, Lotus123, text editors, etc)
doolally
edit....oh, and CSV files only contain the data values. No charts, pivots and whatever

Ah, okay, thanks!

No need for charts or stuff like that. Yes, LibreOffice opens xls and word docs with no problem.

And, more basically, no formatting. At all.

[Breelander]

I downloaded all the info but it's in csv format. ...But what is the difference? I've heard of csv before but I think I've only ever used xls.

If offered the choice, I go for csv every time. It opens fine in Excel, but as a 'lowest common denominator' format it can be opened, read, and even edited in almost anything else, even Notepad.

[ReformedCharacter]

Funnily enough, it was Lotus that opened my eyes to privacy/security issues.

Around 2000, somebody sent me a letter in Word but I didn't have Word at that time, so tried opening it with Lotus.

I not only opened his letter but loads of other stuff as well, remnants of other letters, some rather personal!

From what I remember of explanations at the time, he had deleted text and used the same doc to write his letter. Lotus for some reason, was able to read the deleted text as well.

So these days, I always open a clean Word doc, or LibreOffice.

Steve

I think you should be OK with Word now

IIRC early versions of Word had an odd and non-standard way of marking the end of the file, I can't remember the details but it would explain why Lotus showed a lot of other information that you weren't expecting.

RC

[Watis]

XLS files are generally limited to Excel (though may be usable by some other applications such as LibreOffice)

CSV (Comma Separated Values) can be opened by any application (excel, LibreOffice, Lotus123, text editors, etc)
doolally
edit....oh, and CSV files only contain the data values. No charts, pivots and whatever

Ah, okay, thanks!

No need for charts or stuff like that. Yes, LibreOffice opens xls and word docs with no problem.

Lotus 123?? Crikey, that takes me back! 1995? The good old days when a PC with a 1GB hard drive was considered humungous.

Steve

1GB hard drive! You were lucky, etc.

At the time, my PC had an 80MB hard drive - compressed to 120MB - well, Windows 98 took up 25MB of that - and 4MB of RAM.

It ran Lotus SmartSuite very well!

Watis

[MuddyBoots]

One of the big differences is file size.

Yes, and there's a difference in file size between.xls (the older Excel file type) and.xlsx (the current one). Xlsx compresses the data so files are generally smaller.

Although you can get a good compression of csv files with proprietary zip software.

[Dicky99]

Lotus 123?? Crikey, that takes me back! 1995? The good old days when a PC with a 1GB hard drive was considered humungous.

Steve

I remember in the early 90s my dad bringing a computer home from work to learn how to use Wordstar if you remember that one.

[tjh290633]

Ah, okay, thanks!

No need for charts or stuff like that. Yes, LibreOffice opens xls and word docs with no problem.

And, more basically, no formatting. At all.

That doesn't make sense to me. All the Excel files that I open with LibreOffice are properly formatted. Charts go missing, but formatting is fine. Likewise with Word documents.

TJH

[GoSeigen]

And, more basically, no formatting. At all.

That doesn't make sense to me. All the Excel files that I open with LibreOffice are properly formatted. Charts go missing, but formatting is fine. Likewise with Word documents.

TJH

csv files encode no formatting. At all.

GS

[Urbandreamer]

And, more basically, no formatting. At all.

That doesn't make sense to me. All the Excel files that I open with LibreOffice are properly formatted. Charts go missing, but formatting is fine. Likewise with Word documents.

TJH

I think that mc2fool was saying that cvs does not provide these features, rather than that there is an issue with LibreOffice.
On your subject it depends upon how much formatting you use, what you regard as formatting and what OS you are using.

One issue is that truetype fonts supplied with windows are not supplied with Linux. I would believe that it could be claimed that the format is changed if an entirely different font is substituted. In particular arial, which most people on windows default to using is substituted.

A quick search will show that you can get some of the old windows font's and install them on Linux if desired.

HOWEVER, back to the topic. The xls files supplied by the likes of banks and brokers will be deliberately minimal. No fancy font's, pivot tables or I believe graphs. In any case it's the data that people are interested in.

I've taken to using xls most of the time, as I have had issues with dates and currency symbols from time to time using cvs. It's usually an issue with ticking the right boxes when processing the file.

As has been said though, cvs does have very many advantages. Being text you can cope with files that are bigger than your version of excel or LibreCalc by using a script file to extract only the information that you are interested in. Note I'm talking huge data sets like those provided by the ONS.
You can also use them in proper math packagers like Matlab, Octave or programs other than a spreadsheet.

Each format has it's own advantage and which to chose depends upon what you want to do.

[tjh290633]

That doesn't make sense to me. All the Excel files that I open with LibreOffice are properly formatted. Charts go missing, but formatting is fine. Likewise with Word documents.

TJH

csv files encode no formatting. At all.

GS

Agreed about that. I had the wrong end of the stick.

TJH

[gryffron]

1GB hard drive! You were lucky, etc.
At the time, my PC had an 80MB hard drive - compressed to 120MB - well, Windows 98 took up 25MB of that - and 4MB of RAM.

First PC I used at work was a genuine IBM XT with a 10Mb "Winchester" drive. £4,800 in 1985. £19k in todays money.

Gryff

[UncleEbenezer]

CSV (Comma-Separated Values) is a very simple generic format.
XLS is a format developed by Microsoft for spreadsheet data, incorporating not just the values but also whatever else MS may have deemed relevant.

Reading or writing CSV is trivial: anyone can do it. XLS is a higher hurdle: it requires excel or compatible software. Though there are doubtless partial solutions that can decipher the data while discarding more esoteric elements.