Hacker News Reader

Oh gosh yes. Companies love their CSV exports! I think the reason is that it lets them make glue - they can take data from something, play with it, analyse it, produce graphs, using tools they understand (almost invariably Excel), and sometimes then import into another system.

Yes, my employer's customers like their CSV imports too. If you're looking at a big data entry feature and they ask you how the interface works and you say "you give it a CSV file with these columns", they're happy.

You're lucky. We always try to explain that CSV is a mostly-clean format while XLS and XLSX are a mess.

Yet, most of the time customers insist on XLS export, and we are happy if they at least acccept XLSX instead.

But CSV isn't a mostly clean format. It's a collection of dozens of formats, some of them clean, some of them not, and with no way of labeling which one of the dozens you have in the file itself. Whereas XLS and XLSX, for all their binary cruft, are at least capable of unambiguously (AFAIK) representing, say, a string with a quote and a newline in it. (If there are encoding issues, well, same with CSV.)

I suspect you're thinking of something like "CSV as outputted by Excel with the default settings", which may be clean and unambiguous, but is far from the only CSV in the world.

For that reason (and I'm just saying this generally, not targeted at the comment I'm replying to), always use the most mature CSV output library you can find for your stack. Never write your own CSV export code by bashing strings together unless you have no other choice because you're working in some stack that doesn't have or can't use existing CSV libraries.

(You can ignore the RFC for CSV too; it's not "wrong", it's just one particular flavor that was declared long after the horses had left the barn, learned to form a horse civilization, discovered the atom bomb and bombed themselves back to being plain horses again. It barely rises to the level of being "advisory" in practice.)

XLS and XLSX are neither clear nor unambiguous and I would take 12 CSV formats over either any day. I actually haven't ever met anyone who seriously had to extract data from XLS/X frequently that didn't revile it for its ambiguities. Not to mention the ways that the application munges the user input prior to saving it, leaving the biggest ambiguity of all, what the heck is ACTUALLY in that cell.

EDIT: I will add that EXPORTING to XLS/X is usually quite straightforward and makes users happier than getting a CSV often times. Until they start wanting to get crazy with template design and then you get into Excel styling application and you're back to hating it all over again.

Actually, there are some commercial libraries that make working with even complex Excel spreadsheets pretty straightforward. I've used Aspose.Cells a lot and was very happy with it:

http://www.aspose.com/products/cells

[NB I have no connection with Aspose, just been a happy customer for many years].

The single biggest problem is date / time formats.

Excel encodes dates and times as floating point numbers. The date / time-ness of a cell is encoded in its format. Some of the formats are hard-coded, some float depending on locale, and some use explicit format-pictures embedded in the styles.

But of course there's no guarantee that the user has formatted a cell with a date as a date. Sometimes the user has pasted a US-format date into a copy of Excel running in a UK locale, so dates that fit have day and month swapped, while dates that don't fit show up as strings. This is non-trivial to handle in the general case. I've had to add customer-specific workarounds for this case in particular.

You've unleashed a flood of horrible memories of reading XLS and XLSX files with dates in. Thanks a bunch.

You'd think when Microsoft came up with their new shiny file formats they'd actually make them new and shiny and good.

But nope.

Let me create a headache for you: there is no "CSV as created by Excel with the default settings". There are a lot of different ones for each language setting of Excel, and they are incompatible. So if you're going to work anywhere but the US or even have just some foreigners in the US as your users you're in for a total nightmare doing "Excel CSV"

The worst thing Microsoft ever did to Excel was even translating the formula functions in different languages. So SUM() isn't actually called SUM() in half of Europe...

I don't talk about XLS or XLSX, I just say Excel and give them SpreadsheetML files.

Evil pro-tip : you can output a csv file and name it "foo.xls". Excel will open it fine, with just a click-thru complaint dialog that no one will read anyway.

Israeli bank websites export an HTML table and call the file "foo.xls", and it works quite well.

> Another thing which I don't personally love, but I do because I understand industries have differences is just exporting whatever can be exported into .csv files or .xls files where applicable.

Working on an in-house application, this has been one of the most common types of requests. "Can we add this field here to the CSV export?" It's a three-minute task for me, but a huge time-saver for the manager of the team using the application and thus highly appreciated.

But then exporting the CSV into Excel and doing something becomes part of the workflow. That could possibly be better done in the application.

Often it already is. Export from here, import to there. SaaS is more and more common, so the glue needs to get better.

You can't stop it. If you don't have export then you will hear about your clients copying and pasting to Excel. Much better to add a csv export and high-res download button for all charts and graphs.

By the way, I've had lots of success with chart downloading by simply copying the svg element into a request to an svg to png converter.

Not unless your application aims to encompass the entirety of running a business. Otherwise it's just plain user-hostile greed.

Personally, I do love whenever applications allow to export data and lack of such feature is IMO strong negative.

You are right I don't want to be user-hostile greedy. Its a fine line.

It's also much more efficient for me as a developer to offer an export interface instead of having to implement all the reporting functionality myself.

I'm thinking about starting a consulting company next year. I've been in an engineering role for over 10 years, but I really want to move into a role where I'm not selling my time in exchange for code, but rather selling the value my code creates.

I'm curious how you got your first few "gigs". What should the sales process actually look like? Did you cold call, or did you have enough established relationships first? How do you typically agree on a contract, has the customer come to you with needs, or did you ask them for problems first and propose some solutions, and they agreed to them?

If you really want to reap the value your code creates start an internet-like business with the code that you write. All the value is yours! :)

The first gig came to me. The second was a result of the first. And it goes on from there. If you network then someone will approach you to help them with some code. Do a great job and the second will start. After you do a few, then worry about sales and marketing.

Can you say a little-more about the "sockets"? How do you implement this? I'm not even sure exactly what you mean, do you have an example? It sounds like a fun feature to implement so I'd love to hear more. Thanks

Have a look into websockets. They are used to create an always open connection between the client and server. Meaning your application will not need to continually poll the server for updates.

For notification type stuff (i.e. predominantly one way, server > client) Server Sent Events/EventSource is IMO a simpler, more appropriate solution:

It works over regular HTTP and has a built in method to tell the server the last seen message (i.e. to allow replaying old messages), and exposes the event type (i.e. the event: field in the event block sent from the server) for use with addEventListener in the browser.

It's supported by all non-MS browsers, and because it works over regular HTTP, it can be polyfilled in IE/Edge using long polling XHR.

How would you export it to xls? ActiveX Excel controller ("in my time this was called COM", etc)? Open source library?

For anyone using Ruby I'd recommend the (open-source) axlsx gem, I've had great results being able to treat Excel generation like I'm rendering a view.

I've had luck with that one too for the times I actually need a real Excel file.

tablib[0] is one option for Python. There are quite a few libraries that handle export. Excel files (and other Office) file formats are just zip files containing XML. For a while now, this has been an open standard[1].

[0] https://github.com/kennethreitz/tablib

[1] https://en.wikipedia.org/wiki/Office_Open_XML

I've found the Python pyexcel library to be the most flexible option to read/write Excel files. https://pypi.python.org/pypi/pyexcel/0.0.9

Curious if you've tried tablib. I haven't used either, but knowing kenneth_reitz tablib is quality :)

For Python, there's XlsxWriter[0] (creation of new excel files) and OpenPyXL[1] (creation of new and manipulation of existing files).

[0]: http://xlsxwriter.readthedocs.io/ https://pypi.python.org/pypi/XlsxWriter

[1]: https://openpyxl.readthedocs.io/ https://pypi.python.org/pypi/openpyxl

Create it as a report in SSRS, then they can export to XLSX, PDF, CSV, etc

Most reasonably popular languages will have platform-independent open source read-write libraries for Excel. I've used the Python version of this with minimal pain.

HTML table in a file with a .xls extension.

One of my favorite old tricks. Works with renaming a .csv to .xls.

Newer versions of Excel will warn the user of possible file corruption if they try to open an .html file that has been renamed to .xls. The file can still be opened but the warning is bothersome.

An alternative is to make a .xlsx file with Excel, then unzip it. You'll get a folder with text files and then you'll see how to generate your own.

I've used openpyxl a lot for these kinds of tasks, and found it really useful.

One nice feature with openpyxl is that it can work together with excel template files. So I can create a template in excel with whatever looks and features I want and then use openpyxl to populate those templates with data.

For Java there are at least two good solutions: https://poi.apache.org/ and https://github.com/plutext/docx4j/ (or JExcelAPI)

+1 for Apache POI; we actually used that at Google for exporting XLS reports for ads.

One useful trick is that you can rely on the headless version of LibreOffice to do the conversion, here on Mac for instance (but also work on Ubuntu with a slightly different call):

    /Applications/LibreOffice.app/Contents/MacOS/soffice --headless --convert-to xlsx file.csv

Note, though, that some conversion errors can still occur (I've seen one personally a long time back).

This works for many other formats, too: anything to PDF, for example.

A resource hog, though - had to close it off into its own container, lest it gobble up all available RAM.

PHPExcel for, you guessed it, PHP. An order of magnitude less hassle than CSV ("the files you generate are wrong, but only on some computers! Fixitfixitfixit!!!"), and surprisingly powerful.

Note: Last time we used it for large data report generation results were slow and consumed huge amount of memory.

Depends on the amount of data, calculations, and formatting. For "output this array of arrays as XLSX instead of CSV", I find its current performance acceptable.

I've used this a couple of times: http://papaparse.com/

> implementing sockets into their apps.

are you talking about websockets for web apps? It's not always easy to implement and it requires some extra libraries and deployment setups. Is it really that easy to add for you as giving client extra candy in post order?

quite nice yes, you always have to think people that will monitor statistics etc and how long it will take them to do so. If you can make their life a lot easier thats a + and of course they'll appreciate it.

+1 for CSV export.

For large datasets I've switched to using HDF5 files. They are easy to use from Matlab and a few other applications engineers typically have already.