Hacker News Reader

Comment by protomyth | Hacker News Reader

"A UPS tracking number is 15 digits or something like that, but a FedEx tracking number is 24 digits. If you opened it in Excel it would truncate it, because it would be like, “That is a 24-digit-number. We should store this in scientific notation.”"

I am stunned that this is actually an issue even when dealing with CSV and Excel. It is quite easy to store numbers in Excel and treat them as simple text.

View on HN

Excel really is horrible at handling anything that needs to feed into a software system that isn't also built on Microsoft technology. Just yesterday I was trying to get a coworker to manually order some rows in a database that I had exported to CSV by date, the problem was that some of the dates were just month-year, and some were month-year-day.

Excel insisted on formatting the columns as dates and the month-year rows would turn into month-year-day columns, turning the year into the day and using 2015 as the year.

I told him to just open up the file in a text editor, as I didn't feel like trying to figure out Excel's interface for him or coach him through an LibreOffice install.

If the person I'd needed the info from was not in the organization, as this situation had been, I'd have considered the approach unworkable and figured out a different way to get the info I needed, as they did. Figuring out and teaching people how to use Excel is not how I want to spend my time.

Well, a lot of people use Excel to prep data for other systems and it really isn't that hard to handle. Perl has excellent libraries for reading and writing Excel spreadsheets. Excel is really easy with a large amount of training material. Even the Perl libraries are very easy to use.

I would never tell a user to open up a text editor on tabular data. It is too easy to configure Excel to do the right thing each time. Basic spreadsheet formatting takes care of a lot of problems.

Let me know when Excel can do real Unicode. Ruby rejects CSVs made with Excel's default Unicode export. I had to sit with my boss last year trying out different ways to get it to work right, and eventually made him use LibreOffice to do the xlsx -> CSV conversion.

Let me get this straight. Your boss's preferred tool outputs a CSV that is rejected by your tool's CSV import and your solution is to force your boss to do the conversion in another piece of software? You being a developer and your boss being a business user doing his/her work in his/her tool. That tool being one of the biggest selling programs of all time for business use.

Yep. It was much easier than writing a layer to translate Microsoft Unicode into real Unicode. But he wasn't a business user. I wouldn't dream of telling the business users what tools to use to get me the information I need. Not because I can't, I absolutely can, but because if it's a business use case, it's worth engineering a real solution. This was just a temporary hack to get us through a tricky data problem. Installing it on his machine and working out a procedure took 10 minutes. Would have been quicker if his machine didn't have the characteristic slowness and inscrutability that Windows machines operated by non-techies exhibit. That procedure carried us through until I could work out a saner way.

Nope, this is a legitimate Excel issue when opening CSVs in Excel. If you know a fix without editing the cell data type and saving back into Excel format, I'm all ears!

Edit: I'm referring to the way that most people open CSVs in Excel, by clicking on the file and having Excel be the default CSV program, not by importing it. Is there a way to turn off the scientific notation for good?

You edit the data type because it isn't a number your importing, its a shipping tag. On import you set the column to text, its right there in the import dialog.

ID handling 101 - just because it's made only out of digits, doesn't mean it's a number.

How true that is, dealing with trains, barges, and government issued ids will teach some seriously valuable lessons on that score.

Reply on news.ycombinator.com