12 ms·
Excel: Error when CSV file starts with “I” and “D”
- sly010 10y agoReminds me of a charset detection bug in some versions of notepad [1]: 1. Open new file 2. Type "Bush hid the facts" 3. Save the file 4. Open the file 5. The content of the file have changed to "畂桳栠摩琠敨映捡獴" [1] https://en.wikipedia.org/wiki/Bush_hid_the_facts https://en.wikipedia.org/wiki/Bush_hid_the_facts
- FranOntanaya 10y agoI've seen one chardet issue on some Python library where detection fails and causes character corruptions if there's only one type of Unicode character present, and everything else is ASCII characters. I pity anyone getting stuck on it.
- cesarbs 10y agoThere was a similar sentence in Brazilian Portuguese. Something about our largest TV network (Globo) lying about the existence of Acre (a state people jokingly say doesn't exist, like North Dakota in the US). I can't remember the exact sentence now though. :)
- javawizard 10y agoacre vai pra globo?
- cesarbs 10y agoOooh, that was it. I had a completely wrong recollection of it, so my previous comment is BS now :/
- AndyKelley 10y agoThe lesson here is when choosing your new file format's magic bytes, don't use your initials or something clever. Have the decency to use a few actual random bytes as your format's identifier. For plain text files, it's a little bit different, but there's no excuse for this problem happening in a binary file format. Switching topics, I'm imagining a solution to this problem where you don't actually know which format it is, you have a streaming processor for each thing it could be, and feed it to each processor one character at a time. Whenever a processor returns an error, drop it from the list. If the last processor in the list returns and error, report that error to the user along with which format that processor was for. If multiple peocessors complete successfully, you'll have to either rank them, or ask the user what format it is.
- mark-r 10y agoSYLK isn't binary, it's all ASCII. Interesting idea on passing the file to multiple parsers simultaneously, although I don't see a benefit over trying them serially.
- deadowl 10y agoI get this error all the time.
- lspears 10y agoSame
- rbobby 10y agoOk... that workaround is just terrible. Add one single-quote to the first line?!? What about a closing quote? And wouldn't the side effect of this be for the first line to be treated as one value (instead of separate values for each header)? The workaround needs it's own workaround :)
- DenisM 10y agoOne thing they could do is just create a new extnesion, like .CSVX and treat it as CSV file per RFC. I wish.
- DenisM 10y agoCould that be worked around by enclosing the field in quotes?
- v768 10y agohttps://xkcd.com/1700/ https://xkcd.com/1700/
- bawana 10y agoSo why didn't SYLK files get their own extension? Having to parse the file to figure out what it is? Seems so error prone. But maybe Machine learning will solve this too. Isn't that why extensions were invented- to reduce cognitive load?
- Someone 10y agoThey got, but many systems that produced them didn't have such a thing as a filename extension. Microsoft Multiplan, for example, ran on Commodore 64, CP/M, TRS-80, etc. (https://en.m.wikipedia.org/wiki/Multiplan https://en.m.wikipedia.org/wiki/Multiplan) And even if they did, chances were that transporting the file between machines (no, you couldn't move a floppy disk between machines, even if both machines had a floppy disk. Typical transport involved sending data over a serial line that only guaranteed to transfer 7 bits/byte, another reason why SYLK is ASCII) lost the extension.
- makecheck 10y agoThere are times when I think the source code of the Unix "file" command should be a required part of languages’ standard libraries. I have never seen anything do a better job of describing contents accurately. It’s really silly to see applications have trouble understanding data. Even on macOS, where "file" is installed, the graphical interface sometimes fails to open/preview something that the command-line "file" describes perfectly (i.e. if it’s really text, just show me the text).
- Piskvorrr 10y agoFor all its magic, even file is fallible. https://news.ycombinator.com/item?id=8171956 https://news.ycombinator.com/item?id=8171956
- deleted 10y ago[deleted]
- SubiculumCode 10y agoI've been getting this error from CSV I created in Ubuntu. Glad to have learned what this was about.
- flamedoge 10y agoone of those, how did they miss this?
- MikusR 10y ago"A SYLK file is a text file that begins with "ID" or "ID_xxxx", where xxxx is a text string. The first record of a SYLK file is the ID_Number record. When Excel identifies this text at the beginning of a text file, it interprets the file as a SYLK file. Excel tries to convert the file from the SYLK format, but cannot do so because there are no valid SYLK codes after the "ID" characters. Because Excel cannot convert the file, you receive the error message. "
- Piskvorrr 10y agoIn other words, Excel has all the information to decide that this is not a SYLK file but a CSV, but just throws an error, because fuck you. Great UX. That's way up there with the "you can't drag stuff here, should've dragged it a few px further" from Win98.
- kedean 10y agoWho's to say that it should fall back to CSV instead of giving an error? What if it was actually a malformed SYLK file where further headers were mangled in transmission? I do think it should be giving back a more descriptive error though, possibly one informing the user that it thinks it is a SYLK file and giving them the option to interpret it differently.
- Piskvorrr 10y agoThat is Excel team's choice, of course. However, I would have thought that it would be based on the usefulness of the given import type currently, not in 1988 (I trust there have been new import filters since) How many SYLK files did you see recently, e.g. within the last 30 years? For me, the result is 0 (zero). As compared to innumerable swarms of CSVs of all flavors. Yet even MSO2013 prefers Yonder Hiftorical Curioufity; whence Excel's fondness and preference for obscure and rare formats, I have no idea. And yes, giving user at least an intelligible error message would be nice (I hold no illusions that popping up a selection would be a complex feat: import logic is usually a gnarly place).
- rompic 10y agoWe had a big wtf moment yesterday at work. Also see https://en.m.wikipedia.org/wiki/SYmbolic_LinK_(SYLK) https://en.m.wikipedia.org/wiki/SYmbolic_LinK_(SYLK)
- rompic 10y agoAlso reminded me of http://tburette.github.io/blog/2014/05/25/so-you-want-to-write-your-own-CSV-code/ http://tburette.github.io/blog/2014/05/25/so-you-want-to-wri...
- chias 10y agoI feel like this is one of those bugs that would have taken less time to fix than it took to document. You could even do something as trivial as "if Excel fails to open the file as SYLK, try again as CSV" and cut at least 99% of the problem away.
- deleted 10y ago[deleted]
- zaidf 10y agoI feel like this is one of those bugs that would have taken less time to fix than it took to document. After many years, I still have to slap myself when I catch myself thinking this way. Unless you know the architecture of the software, I've learned that it's often significantly harder than you imagine to fix seemingly simple and obvious bugs without breaking something else.
- Dylan16807 10y agoFine, a slight correction then. It should be easy to fix except for terrible software architecture. Terrible software architecture is not a valid excuse when you have plenty of time and billions of dollars. So there should be little forgiveness for this bug still existing.
- cm2187 10y agoParticularly since it's not like if Microsoft was adding new features to Excel since office 2007.
- zaidf 10y agoAs a consumer affected by this bug, sure, you may find it inexcusable. But the basic truth about any mature software product used by tens of millions is that there will be a laundry list of bugs competing for limited resources. So there will always be bugs that you want to fix but simply isn't a high enough priority relative to all the other bugs or features.
- combatentropy 10y agoI've run into this error many times for a decade, always when I make a CSV file of a table whose first column is "ID". It seems to me that Microsoft by now could have improved its tests. If the first two letters are "ID", but if "there are no valid SYLK codes after the 'ID' characters," then maybe it was never meant to be an SYLK file. If the file's suffix is .csv, then maybe you should just treat it as a CSV file. If the file's suffix is .txt, then maybe you should treat it as a text file.
- metalliqaz 10y agoI have absolutely hit this bug before, multiple times. However, I never put it together that the 'ID' was causing it.
- jacobush 10y agoI can think if things breaking for each of those.
- 13of40 10y agoIf you're a fan of this sort of thing, try the following in CMD. (It helps if you read it in the voice of a 90's rapping cartoon cat.) echo MZ is my name my name is MZ. I'm the hippest loader bug from the sea to the sea! >foo.txt .\foo.txt
- serge2k 10y agoDoesn't work on 64 bit, but I seem to recall something about the first 2 bytes of a PE header. :)
- Retr0spectrum 10y agoWhat does this do? I don't have access to a windows machine.
- ratboy666 10y agoAlrighty, then, on Linux, just for you. [fred@dejah launch]$ chmod +x foo.txt [fred@dejah launch]$ ./foo.txt fixme:winediag:start_process Wine Staging 1.9.12 is a testing version containing experimental patches. fixme:winediag:start_process Please mention your exact version when filing bug reports on winehq.org. winevdm: Cannot start DOS application E:\launch\foo.txt because the DOS memory range is unavailable. You should install DOSBox. [fred@dejah launch]$ I have Wine installed. Same defect as Windows, which is either good or bad -- this is hard to determine. At least, the hint of installing DOSBox is reasonable. So, let's do that: [fred@dejah launch]$ sudo dnf install dosbox and try it again: [fred@dejah launch]$ ./foo.txt fixme:winediag:start_process Wine Staging 1.9.12 is a testing version containing experimental patches. fixme:winediag:start_process Please mention your exact version when filing bug reports on winehq.org. DOSBox version 0.74 Copyright 2002-2010 DOSBox Team, published under GNU GPL. --- ALSA lib pulse.c:243:(pulse_connect) PulseAudio: Unable to connect: Connection refused CONFIG: Generating default configuration. Writing it to /home/fred/.dosbox/dosbox-0.74.conf CONFIG:Loading primary settings from config file /home/fred/.dosbox/dosbox-0.74.conf CONFIG:Loading additional settings from config file /home/fred/.wine/dosdevices/c:/users/fred/Temp/cfgcbaf.tmp MIXER:Can't open audio: No available audio device , running in nosound mode. ALSA:Can't subscribe to MIDI port (65:0) nor (17:0) MIDI:Opened device:none [fred@dejah launch]$
- mckoss 10y agoMaybe my bug? I wrote most of the original Excel text-based file import code in 1984 (including the SLYK format, which was the main way we migrated files from Multiplan to Excel).
- magoon 10y ago32 years ago! What are the chances your code has survived that long in a product like Excel?
- eric_h 10y agoAdmittedly this is for Excel 2003, so only ~20 years. I think there's a good chance that this bug was theirs ;)
- puetzk 10y agoThe bug is still there, and my software still has to work around it in my CSV emitter (force redundant quoting so the first byte will be '"' instead). Boo, I say!
- pbreit 10y agoSpreadsheets still suck at leaving my data alone. At least Excel has that ridiculous import facility where you can instruct it to leave certain columns alone. Google Sheets are surprisingly the worst in this department. It's pretty much impossible to prevent Google Sheets to convert 5/7 to a date and 0123 to a number (losing the leading 0 of course and rendering the data invalid). No, ' is not the answer.
- boterock 10y agoI'd like to know if you (or someone) knows who had that stupid idea of localizing csv files (for those who don't know, excel in spanish uses semicolon for separating values, because commas are reserved for decimals, i'd prefer some standard behavior rather than localized unstandard behavior)
- newscracker 10y ago
- lifeisstillgood 10y agoI had to fake a csv file for testing yesterday, and started out with id, then went "ahahah", and used name. I know that's boring but this is one of those generational pieces of knowledge like "keep your docs up to date" that we need to build into software training somehow. (Or rather, this bug is not important, but the kind of training that imparts this knowledge will be vital in building a real software profession) But yeah, it's a real WTF
- pskomoroch 10y agoWeird, I hit this exact error 2 weeks ago
- chrismorgan 10y agoDitto, two days ago.
- code777777 10y ago"Applies to Microsoft Excel 2002 Standard Edition, Microsoft Excel 2000 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft Office Excel 2003 "
- BBlarat 10y agoI still get the error in Excel 2016, so I guess it's not fixed yet.
- chetangole 10y agoWe get a warning in Excel 2016, and can open the file.
- cm2187 10y agoI had to check this is not the 1st of April I particularly like the step by step explaination of how to add an apostrophe at the begining of the first line with a description of which keyboard key to press...
- gus_massa 10y agoI have three apostrophes in my (Latin America) keyboard: ` ' ´ but IIRC Excel only recognizes one of them.
- reilly3000 10y agoJeez, how many SLYK files out there? I venture to guess there is an order of degrees more attempts at opening CSV files with ID as the first header than attempted opens of unconverted SLYK files in excel. When I admire Microsoft, it's most often because despite nearly 4 decades of bloat to support, they still can ship reliable software to millions.
- lostlogin 10y agoI kind of wish I could just use a version from a decade or 2 ago. The (4 year old) versions of word and excel that the uni site I work for use are so clunky to navigate. I loved using word in pre OSX says - I wonder how much of that is rose tinted nostalgia.
- Piskvorrr 10y agoTry LibreOffice (Portable if necessary) - it has remained with the conservative UI.
- garyclarke27 10y agoExcel is useless with csv and utf-8. when I need to export to csv, which I regularly do, I always use Libre Office.
- douche 10y agoI refuse to open CSVs in Excel, because so often perfectly valid CSV gets butchered by it. It's easier to just use Notepad++.
- supergreg 10y agoA nice weekend project would be a converter from CSV to xslx or SpreadsheetML [0] [0] https://en.wikipedia.org/wiki/SpreadsheetML https://en.wikipedia.org/wiki/SpreadsheetML
- m_mueller 10y agoIt actually does have CSV and UTF-8, but finding the correct settings is very hidden. I'm always amazed at how unable Microsoft is to improve the base of Excel and instead just keeps piling UI layers on top to hide the ugly places.
- SonOfLilit 10y agoSurprisingly, Excel can import CSV from UTF8 with BOM (at least new versions of Excel). However, be careful, as Ctrl+S will save it in some arbitrary codepage and you'll probably end up with a document like: ID,Name 1,??? ???? 2,Kevin Dub?is so take care to always save such documents as .xmlx. (If you convert them to your local codepage and import that - when at all relevant - it will be saved in the local codepage, so less risk of data loss.)
- masklinn 10y ago> Surprisingly, Excel can import CSV from UTF8 with BOM (at least new versions of Excel). A BOM is abnormal and not recommended in UTF-8, it's basically a shitty MS hack: the BOM is necessary to detect endianness differences between the document and the host system, endianness has no impact impact on UTF-8. And as freak_nl above notes, Excel uses locale-dependent field separators, so in some countries/locales it will try to import "CSV" with semicolon separators.
- mhd 10y agoSo this would be another problem that could be avoided if "CSV" would actually friggin mean "comma separated values" and not "whatever Windows might consider comma-equivalent depending on locale and/or phase of the moon"?
- stinos 10y agoPretty sure any locale-aware OS treats comma different depending on what locale has been used, no? Anyway commas are historically used as seperators in numbers in some countries, and in text, etc, so if you really want to shed your blame all over something, maybe it shouldn't be Windows here but rather the people who came up with comma as a record seperator in the first place. Probably it was ok for them in that time, at that place (US I'm guessing) and for the usage needed back then, but I'd really wish they chose a different seperator.
- Piskvorrr 10y agoWhich "works", in the same way that national character sets "work". Until you start exchanging data across locales ("duh, easy: just don't talk to the Americans, solved!").
- boulos 10y agoNo. From the detailed description: > A SYLK file is a text file that begins with "ID" or "ID_xxxx", where xxxx is a text string. The first record of a SYLK file is the ID_Number record. When Excel identifies this text at the beginning of a text file, it interprets the file as a SYLK file. Excel tries to convert the file from the SYLK format, but cannot do so because there are no valid SYLK codes after the "ID" characters. Because Excel cannot convert the file, you receive the error message. This is usually called "magic string" or "magic number" (https://en.m.wikipedia.org/wiki/Magic_number_(programming) https://en.m.wikipedia.org/wiki/Magic_number_(programming)). It has nothing to do with the comma, and everything to do with SYLK using a pretty risky magic string (ID) and Excel not having a "try SYLK, if that fails, try as csv". tl;dr: this is about guessing the input format and has nothing to do with the delimiter.
- 10y ago
- deleted 10y ago[deleted]
- deleted 10y ago[deleted]
- ZenoArrow 10y agoI don't know enough about SLYK to suggest a full solution, but couldn't additional checks be performed that confirm it's a SLYK file? Other than all SLYK files starting with ID, are there any other properties that exist in all SLYK files that could be checked against?