And yes, this is normal use, I don't want to use a database for doing a job that's 2 mins in Excel. We use GDocs at the company, but it's a toy (a nice toy though).
And yes, this is normal use, I don't want to use a database for doing a job that's 2 mins in Excel. We use GDocs at the company, but it's a toy (a nice toy though).
Hint: It's not possible. My favourite chart about excel insufficiencies:
Encoding BOM Win Mac
-------- --- ---------------------------- ------------
utf-8 scrambled scrambled
utf-8 BOM WORKS scrambled
utf-16 file not recognized file not recognized
utf-16 BOM file not recognized Chinese gibberish
utf-16LE file not recognized file not recognized
utf-16LE BOM characters OK, same as Win
row data all in first field
(https://stackoverflow.com/questions/6588068/which-encoding-o...)More frustrating is when you need produce an UTF-8 CSV from excel. I always used open office calc for this as a workaround.
To import an UTF-8 csv to excel you just run the text import wizzard and specify the encoding.
My relief in this area was discovering that most (western) non-software companies dealing will Excel-CSV know that encodings are a problem and many have settled on using CP-1252 instead of UTF-8.
The first practical lesson of CSV is CSV WILL fail miserably when changing character encoding, using non ASCII characters, having quoted string, varying end of line characters...
I generally agree with the idiom "If you write your own cav parser, you're doing it wrong", but sometimes you have no choice to work around the idiocy of others.
Wrong: CSV is standardized in RFC 4180:
For quick, ad-hoc tasks on huge files I recommend R/RStudio.
I also use Notepad++, of all things, for cleaning-up csv data because its "operating envelope" in terms of file-size and sheer number of files is unlike anything else. The regexp search/replace combined with zoomed-out views allow for easy clean-up of text data.
Despite doing Excel addin development in finance, i never hit a limit in post 2007 excel.
Well, that is how it worked for me, I used Excel 2016, the most recent LO (5?), and Google Spreadsheets. Around two months ago. I wanted to create a chart (histogram) from data in a CSV, with around 900k lines. Calling this a 'lie' is just bad manners, sorry.
LibreOffice is slow but it is often more correct.
Update: Over the years, it seems to me that the MS formats are incented to be as incompatible as possible with other formats to lock folks into their format. It enables them to say that they are the most "compatible". . . but it really means the most "ability to read their own format" even though compatibility is better across the board with alternatives.
I don't want to tell you how to do your own job, but if it's normal to handle millions of lines in a spreadsheet, you might be doing it wrong. (2 mins in excel; 2 seconds in a db)
Using a million row sample from here:
https://blog.majestic.com/development/majestic-million-csv-d...
Taking a summary of the data:
> cat csvtest.sql
.mode csv
.import majestic_million.csv rankings
select TLD,count(TLD) as cnt from rankings group by TLD order by cnt desc limit 10;
> time sqlite3 < csvtest.sql
com,402802
top,96734
cn,72029
net,55131
org,45948
gdn,40246
ru,29746
cc,27117
de,19291
uk,14971
real 0m6.295s
user 0m6.089s
sys 0m0.156s