3 ms·

Having built a CSV import pipeline handling a few thousand different CSV files from almost the same number of different providers: I've probably seen almost eve

Having built a CSV import pipeline handling a few thousand different CSV files from almost the same number of different providers: I've probably seen almost everything one can mess up while writing out data: Invalid or missing escaping, double or per column string encoding, truncated columns, BOMs, EANs in E notation, Month names instead of numbers and so on. Out of curiosity: do you handle any of that?

spreadr 5y ago
Thats a great question. We handle most of it, except: 1. Escaping in csv 2. Month names will be treated as string. This has got us thinking for more such scenarios. Thank you!
- stephenhuey 5y ago
  Pretty cool how you offer this as a widget. 18 years ago I built a handy importer for CSV and tab-delimited files in a logistics company’s traditional Java web app with no JavaScript/AJAX, and it enabled the users to map columns in a multi-page workflow. CSV was new to me and of course all the edge cases were surprising, but on top of that all the data consisted of shipping addresses, and I was amazed at how many different ways address fields would be presented! So it definitely felt like a double whammy of CSV inconsistencies and address inconsistencies!
  - simlan 5y ago
    Same in the sciences. Every instrument manufacturer invents their own esoteric format. Simple x,y,y2,y3 is the exception. Sometimes they call it tsv, csv but no commas or tabs were intended. Multiple datasets in the same csv but instead of adding extra columns put in some breaks in one long column. So you start scanning for empties, skip some metadata and start reading again... Sometimes it is wild.
codeulike 5y ago
Great point, there are so many gotchas with CSV. Could the app handle this one? "First Name","Last Name","Address","Town","Postcode" David,O'Leary,"12 Acacia Avenue",London,NW5 3DF June,Robinson,"14, Abbey Court","Putney",SW6 4FG Greg,Hampton,"",, Stephen,James,"""Dunroamin"" 45 Bridge Street",Bristol,BS2 6TG Daisy,Smith,"Flat 6, Porchester Court, 24 Dragon Road",Newcastle,NE3 4FG (i.e. some fields enclosed in speechmarks and some not, commas in text, blank values, escaped speechmarks, carriage returns) edit: I tried it with the csvbox.io demo - it seemed to deal with it ok!
- spreadr 5y ago
  It will work. I have setup a demo for you for this data. Simply create a csv file and upload here - https://app.csvbox.io/upload/HdPOFwRMeU73O3hLMqM98lOB2ABllS?userId=default123 https://app.csvbox.io/upload/HdPOFwRMeU73O3hLMqM98lOB2ABllS?...

spreadr 5y ago

Thats a great question. We handle most of it, except: 1. Escaping in csv 2. Month names will be treated as string. This has got us thinking for more such scenarios. Thank you!

stephenhuey 5y ago
Pretty cool how you offer this as a widget. 18 years ago I built a handy importer for CSV and tab-delimited files in a logistics company’s traditional Java web app with no JavaScript/AJAX, and it enabled the users to map columns in a multi-page workflow. CSV was new to me and of course all the edge cases were surprising, but on top of that all the data consisted of shipping addresses, and I was amazed at how many different ways address fields would be presented! So it definitely felt like a double whammy of CSV inconsistencies and address inconsistencies!
- simlan 5y ago
  Same in the sciences. Every instrument manufacturer invents their own esoteric format. Simple x,y,y2,y3 is the exception. Sometimes they call it tsv, csv but no commas or tabs were intended. Multiple datasets in the same csv but instead of adding extra columns put in some breaks in one long column. So you start scanning for empties, skip some metadata and start reading again... Sometimes it is wild.

codeulike 5y ago

Great point, there are so many gotchas with CSV. Could the app handle this one? "First Name","Last Name","Address","Town","Postcode" David,O'Leary,"12 Acacia Avenue",London,NW5 3DF June,Robinson,"14, Abbey Court","Putney",SW6 4FG Greg,Hampton,"",, Stephen,James,"""Dunroamin"" 45 Bridge Street",Bristol,BS2 6TG Daisy,Smith,"Flat 6, Porchester Court, 24 Dragon Road",Newcastle,NE3 4FG (i.e. some fields enclosed in speechmarks and some not, commas in text, blank values, escaped speechmarks, carriage returns) edit: I tried it with the csvbox.io demo - it seemed to deal with it ok!

spreadr 5y ago
It will work. I have setup a demo for you for this data. Simply create a csv file and upload here - https://app.csvbox.io/upload/HdPOFwRMeU73O3hLMqM98lOB2ABllS?userId=default123 https://app.csvbox.io/upload/HdPOFwRMeU73O3hLMqM98lOB2ABllS?...