7 ms·
Try opening a large CSV file in Excel, GDocs, LibreOffice. GDocs dies around 50k rows, LO around a few 100k, Excel can cope with a million easily. And yes, thi
by sz4kerto 10y ago
Try opening a large CSV file in Excel, GDocs, LibreOffice. GDocs dies around 50k rows, LO around a few 100k, Excel can cope with a million easily.
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).
- paldepind2 10y agoHave you tried Gnumeric? It's supposed to be fast at handling large files.
- aw3c2 10y agoIt is! Just imported a 3 million line csv with zero problems.
- neverminder 10y agoIndeed Libreoffice cannot handle large CSVs which is a shame, because otherwise it's an excellent tool.
- merb 10y agoBasically thats a lie. Yes Office is good in many ways and especially ease of use. But opening a CSV file in Office > 2013 vs LibreOffice that contains more than 100k is ways more smooth in the newer LibreOffice version than in newer Office versions. If you would've written anything about UX or usages or Format interoperbility, rendering their own format etc than yes Office is a clear winner, but when it comes to big files and their resource usage the newer office versions are just bad.
- reitanqild 10y agoAnd for many of my use cases (although not all) LO/OO was way ahead of Word at times (formatting using styles comes to mind as a place where I used to prefer old OO for years and I also think LO/OO is less prone to misread basically anything as an American formatted date.)
- merb 10y agoYeah if you have a document written in LO/OO, that's true however loading still some XLS files is just akward. I mean probably due to the spec and even microsoft sometimes had bugs in their program so that at some things the spec didn't helped. like some table bugs etc. But still I don't wanted to says XYZ is better overall or so. I just wanted to point out that resource usage and loading bigger files is definitly not the thing were office shines at least the newer versions and also don't expect office for mac to be good, it sucks.
- reitanqild 10y agoBack in 2005 ish I used to have access to both but still import to Writer to clean up after three other editors who didn't get (or care maybe) about styles, the export to Word again because Writer was missing some page numbering feature or something.
- sz4kerto 10y ago> Basically thats a lie. 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.
- DigitalJack 10y agoI've run into excel problems at 64k rows. It was a while ago, but this century at least.
- ygra 10y agoI think 2007 was the first version to support two million rows.
- fletchowns 10y agoMillion row Excel doc that you only need to do 2 mins worth of stuff to? I'm curious what your use case is, can it not be completely automated?
- continuational 10y agoThe task at hand could just be to answer a quick one-off question about the data. Not worth bothering the developers for that.
- sz4kerto 10y agoTaking a log file, grep, sed to extract some numbers and then create a graph that shows the distribution of those numbers (histogram). It was one-off, I could have used some command-line tools to do all of those but then I'd have spend an hour learning how gnuplot works, which I didn't have time for. I'm a developer, and that's exactly why my time is expensive -- automating everything doesn't always make sense.
- xapata 10y agoDepends on how often you do the task (and similar), of course. If it's frequently, then not learning a faster technique is getting stuck in a local optima.
- hendzen 10y agoFor this type of task I usually spin up an ipython/jupyter notebook and hammer something out with Pandas very quickly. It's an incredibly productive toolkit for quickly performing this type of task.
- tatoalo 10y agoExcel can(at this day) handle 1,048,576 rows which is 2^20. If you really need to work on bigger DBs than that I'd recommend using Power Pivot(natively included in Excel 2016) which is supposed to handle way more than 2^20 rows.
- cel1ne 10y agoTry creating a UTF8 CSV-file that will open correctly in Excel. 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-opens-csv-files-correctly-with-excel-on-both-mac-and-windows https://stackoverflow.com/questions/6588068/which-encoding-o...)
- glaberficken 10y agoI found this unbelievable when I first came across it a few years ago. 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.
- cel1ne 10y agoYes. 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.
- jhanschoo 10y agoHi, you should check out my shell script gist that helps you with it. Remember to change the path to your LibreOffice executable https://gist.github.com/jhanschoo/bcd6d08d1e0f4b5d52735287f39ca924 https://gist.github.com/jhanschoo/bcd6d08d1e0f4b5d52735287f3...
- douche 10y agoIt is amazing how badly Excel handles CSV files. My favorite thing is where it will decide for some goddamn reason that a numeric or string column is actually supposed to be a date, or a currency, or some other random thing, and automatically format the column. Cue the calls from customers that don't know how to turn this "feature" off, when they export something as CSV and think it is corrupted.
- philipov 10y agoFunny, I've always considered Excel (and all the others) equally bad at scaling because none of them can cope with csv files over 1 million rows. 50k or 1 million isn't a big difference. Where's the vi of spreedsheets for handling those 300 million row db dumps with a load error on line 253,452,624?
- shakna 10y agoCSV is just plain text, so vi can work... But as others have said, try it with Gnumeric.
- crispyambulance 10y agoI don't know when it became "easy" to handle a million rows in excel, but I do know that some time ago that was intractable. As late as a few months ago, I recall trying to work with ~6 million rows in excel and it flaked out miserably. 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.
- NetStrikeForce 10y agoNo joking, I find Sublime Text brilliant to work with humongous CSV files :-)
- winteriscoming 10y agoWhich version? The latest 3.x version, that I use, almost stalls trying to load a file of relatively small size of 20 odd MBs.
- NetStrikeForce 10y agoWith 2.x I've opened and edited with multiple cursors (my favourite feature) files around a GB in size. On Windows, if that makes a difference.
- hermitdev 10y agoExcel 2007. Prior it was impossible due to file format limits. Row limit was 64k, i think. Not sure what the Col limit was. Not sure what or if there limits are since the move to the xlsx format. Despite doing Excel addin development in finance, i never hit a limit in post 2007 excel.
- jenscow 10y agoI agree. GD/LO spreadsheets are an appalling database engine. 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)
- kweinber 10y agoIt is a common case in helpdesks I've collaborated with to fix corrupted Excel files by opening them in LibreOffice and resaving them to Excel. Also, Excel's practice of munging CSVs requires an initial read in LibreOffice sometimes and then saving to Excel when Excel simply can't handle the file format. 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.
- pstuart 10y agoIt's not clear what you're doing with your data, but sqlite may be a viable option as well. Using a million row sample from here: https://blog.majestic.com/development/majestic-million-csv-daily/ 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
- jcoffland 10y agoGnumeric works better for large files. Also, Open-Source.