5 ms·

The closest I've come was a Rails project I inherited from a star developer who had just left the company. It was a B2B project that involved importing large Ex

by tboyd47 10y ago

The closest I've come was a Rails project I inherited from a star developer who had just left the company. It was a B2B project that involved importing large Excel spreadsheets of various different formats into a standardized database for itemized review. The code was pretty sloppy, but didn't deviate much from standard Rails idioms. Not many people on the team understood Rails well enough to read it, but I did. Bug reports were constantly flooding in. I suggested taking a sprint to build up an integration test suite and then letting loose on the backlog. We did build up a sufficient test suite in one sprint. But the bug reports never slowed. By the time we had the confidence to truly start tackling bugs at speed, the battle had been lost. We had been so busy writing tests that we forgot to manage the bug tracker. The impression was that we were overwhelmed and unable to make progress. The project was swiftly closed. People remembered that codebase as an exemplar of sloppy code and technical debt, but that's not the lesson I took from it. I had seen, and others would see later, much worse. The lesson I took was that perceptions are as important to manage as results.

byroot 10y ago
I don't think I ever seen an Excel / CSV import implementation that wasn't a huge mess.
- samwilliams 10y ago
  Do you mean Excel to CSV? Or a CSV importer? I totally agree about Excel importing, but CSV is trivial, no? Here is an Erlang version I happened to write yesterday: lists:map( fun(Row) -> string:tokens(Row, [SepChar]) end, string:tokens(InputStr, "\n") ). EDIT: I know this version won't support escaped separator/newline characters, but I made it for a specific use case in which I knew that would not occur. Adding that functionality would make it a little messier, but still not too bad. EDIT2: Thanks for the interesting comments! Not so trivial after all! Perhaps a more accurate version of what I was attempting to say above is that 'it is often (not always) easy to build a CSV parser to interact with one specific program'. The four line version above works perfectly for reading the type of files I designed it for. If you want to work with human created, or more complex variants of CSV, all bets are off.
  - acdha 10y ago
    You need a lot more than that to handle CSV in the wild (quoting, Unicode, line termination, etc.) but the real killer I see is when it's edited by humans. The special cases for errors and inconsistencies will add up quickly; in some cases you may be able to reject invalid data but you may not have that option or an easy way to tell whether any particular value is wrong. Excel takes that, adds some fun things like people using color and formatting to store data, and things like Excel auto-corrupting values which look like dates and may not have been noticed before you do something with the data.
    - samwilliams 10y ago
      That is very interesting, thanks! I hadn't thought about Unicode or tolerating human error. Although the times I have worked with it have been when it is a transport medium between two computer programs.
      - acdha 10y ago
        That's definitely a less-aggravating situation by far. I've had a lot of cases where a significant amount of specialist human time was in a spreadsheet and it's really made me wish there was an Excel-for-data which acknowledges how many people are using it for semi-structured data like this.
        nicostouch 10y ago
        Like Airtable?
    - Bartweiss 10y ago
      I know of at least one company whose entire business is handling this stuff. They find growing companies as they hit critical mass and need to move their Excel data into a real database. The product is just "Your data is hideous and was entered by hand without validation or formatting; it'll never convert and it'll be wrong when it does. We can help." They handle all kinds of theory and technical stuff, like normalization and processing Excel-corrupted dates. But they also handle a lot of easy-but-agonizing tasks like regularizing single quotes into apostrophes, which crop as soon as you let humans enter free-form data.
- tboyd47 10y ago
  Ruby has had good ETL libraries for a long time. In my opinion, our product team was too lenient concerning the format of the Excel files. Asking customers to fill out a template spreadsheet to submit to our system, rather than letting them submit any old XLS file they happen to have on their computer, would have gone a long way towards simplifying the problem space.
- protomyth 10y ago
  Excel imports with Perl did pretty well for me. I was pretty careful on insisting on some rules for the sheet data and enforced them strictly with decent debugging info for the users. I still think Robustness principle[1] is a croc and strictly controlling inputs is one key to happiness. It also, frankly, helps your users in the long run by giving them exactly what they want and it actually cuts down on the amount of thought they have to put into it. Chaos and disappointment do not make a good user experience. 1) https://en.wikipedia.org/wiki/Robustness_principle https://en.wikipedia.org/wiki/Robustness_principle

by tboyd47 10y ago

byroot 10y ago

I don't think I ever seen an Excel / CSV import implementation that wasn't a huge mess.

samwilliams 10y ago
Do you mean Excel to CSV? Or a CSV importer? I totally agree about Excel importing, but CSV is trivial, no? Here is an Erlang version I happened to write yesterday: lists:map( fun(Row) -> string:tokens(Row, [SepChar]) end, string:tokens(InputStr, "\n") ). EDIT: I know this version won't support escaped separator/newline characters, but I made it for a specific use case in which I knew that would not occur. Adding that functionality would make it a little messier, but still not too bad. EDIT2: Thanks for the interesting comments! Not so trivial after all! Perhaps a more accurate version of what I was attempting to say above is that 'it is often (not always) easy to build a CSV parser to interact with one specific program'. The four line version above works perfectly for reading the type of files I designed it for. If you want to work with human created, or more complex variants of CSV, all bets are off.
- acdha 10y ago
  You need a lot more than that to handle CSV in the wild (quoting, Unicode, line termination, etc.) but the real killer I see is when it's edited by humans. The special cases for errors and inconsistencies will add up quickly; in some cases you may be able to reject invalid data but you may not have that option or an easy way to tell whether any particular value is wrong. Excel takes that, adds some fun things like people using color and formatting to store data, and things like Excel auto-corrupting values which look like dates and may not have been noticed before you do something with the data.
  - samwilliams 10y ago
    That is very interesting, thanks! I hadn't thought about Unicode or tolerating human error. Although the times I have worked with it have been when it is a transport medium between two computer programs.
    - acdha 10y ago
      That's definitely a less-aggravating situation by far. I've had a lot of cases where a significant amount of specialist human time was in a spreadsheet and it's really made me wish there was an Excel-for-data which acknowledges how many people are using it for semi-structured data like this.
      - nicostouch 10y ago
        Like Airtable?
  - Bartweiss 10y ago
    I know of at least one company whose entire business is handling this stuff. They find growing companies as they hit critical mass and need to move their Excel data into a real database. The product is just "Your data is hideous and was entered by hand without validation or formatting; it'll never convert and it'll be wrong when it does. We can help." They handle all kinds of theory and technical stuff, like normalization and processing Excel-corrupted dates. But they also handle a lot of easy-but-agonizing tasks like regularizing single quotes into apostrophes, which crop as soon as you let humans enter free-form data.
tboyd47 10y ago
Ruby has had good ETL libraries for a long time. In my opinion, our product team was too lenient concerning the format of the Excel files. Asking customers to fill out a template spreadsheet to submit to our system, rather than letting them submit any old XLS file they happen to have on their computer, would have gone a long way towards simplifying the problem space.
protomyth 10y ago
Excel imports with Perl did pretty well for me. I was pretty careful on insisting on some rules for the sheet data and enforced them strictly with decent debugging info for the users. I still think Robustness principle[1] is a croc and strictly controlling inputs is one key to happiness. It also, frankly, helps your users in the long run by giving them exactly what they want and it actually cuts down on the amount of thought they have to put into it. Chaos and disappointment do not make a good user experience. 1) https://en.wikipedia.org/wiki/Robustness_principle https://en.wikipedia.org/wiki/Robustness_principle