Alun Rowe

ramblings about cars, tech, web development and more. BOTB Winner in 2010

Excel CSV import returns an SYLK file format error

By

On January 9, 2012

In excel

The Problem:

Trying to open CSV file that my app had generated kept causing the following message to appear in Excel:

Excel has detected that ‘balhblah.csv’ is an SYLK file, but cannot load it. Either the file has error or it is not a SYLK file format. Click OK to try to open the file in a different format’ , you may also get the message SYLK: File format is not valid

The solution:

After many hours of pulling my hair out trying to work it out I discovered that if you make the first letters “ID” of a text file Excel incorrectly assumes you are trying to open an SYLK file (whatever one of those is!).

Hope that helps!

PS Microsoft have a page on this now… http://support2.microsoft.com/kb/323626

Previous

F1 heads to SKY

Next

Codeigniter Beginners Tutorial Article

313 Comments

Add Comment →

  1. Geoffrey Hashman

    I ran into this problem when creating a CSV file with the requirement that columns 1-2 of the first record – representing the first cell of the spreadsheet must contain uppercase “ID”.

    I kept getting “Unable to load SYLK file…” Ichanged that to “Id” – but that was not acceptable to the program into which this file was input.

    For the moment I have got around it by setting up the field as “ ID “ and seeing whther the next program is rigidly expecting :ID” in cols 1-2, or is it a “free-format field which allows for “ID” as floating characters.

    One can only hope and pray!!!

  2. Eric Blankenstein

    Thank you Alun for sharring this info – it has saved me pulling my hair and spending many hours to figure this out….

    Thank you and bless you for sharring this info.

  3. Wesley

    > 700 days later his post is still helping. Many thanks for saving me many hours of frustration.

    Wes

  4. Alun

    Glad to still be of help!

  5. Zid Zidane

    The way to fix this for a programmer is to put the apostrophe character (’) as the first character of the CSV file. Then when Excel opens it, it won’t say, “Error: its a SYLK file gone bad”.

  7. Jaspreet

    Put an apostrophe at the beginning of the first field and this irritating error goes away

  8. jojo

    Another code warrior salutes you!

  9. Tushar

    Thanks ! it helped..

  10. David

    refering to #5 above, the solution is not for the programmer or enyone else in the world to avoid using the characters ID in a CSV header. The solution is for Microsoft the remove such a stupid hard-coded logic.

  11. Alun

    Looking at the MS knowledge base this should be fixed in the most recent versions of excel

    http://support.microsoft.com/kb/323626

  12. khandu

    i cant save excel file in .csv format error you cant save multiple sheet in format
    how to save
    plz reply me.
    i cant save excel file in .csv format error you cant save multiple sheet in format
    how to save
    plz reply me.

  13. Alun Rowe

    Hi Khandu

    You can’t save a multi-sheet excel file as a CSV because csv’s don’t support multiple pages. Simply save each worksheet as a desperate CSV and name them in a sensible way for the other person to import e.g. mydata_sheet1.csv, mydata_sheet2.csv and so on

    Good luck!

    Alun

  14. ALEX

    THANK YOU , THANK YOU , THANK YOU

  15. Dennis

    Thanks!! Still useful!

  16. Alun

    Wow, 2 years and a bit on and it’s still proving useful! Glad to have helped :)

  17. Mitch

    This didn’t help me for my requirements…

    What did help was this:
    —Save file as a .csv and close it.
    —Right click on the .csv file and Open with>Notepad
    —Go to File>Save As
    —Under Ecoding: select UTF-8
    —Click Save

  18. Jewfin

    HELL YEAH!!! Thanks.

  19. Frustrated

    Helped again, thanks

  20. Jeff

    Yup, still helping.

  21. Jay

    Thanks!

  22. F

    Thank you! :-)

  23. Andrew N

    Still a big help. Keep this alive (Incase you were thinking that no one is using this anymore) :-)

  24. Raf

    received the error message during import of a custom csv file about 3 minutes ago.
    googled it and this article resolved it in less than 60 secs.
    thanks!!!!

  25. Paul

    And there was me thinking it was my programming skills that sucked. Microsoft, as usual. Pfft. Suckers.

    On a serious note, thanks for the tip. I dread to think how many circles I would have went round if I didn’t discover it.

  26. Gary

    Changing ID to lower case id should also fix the problem.

  27. Jo

    Thank you – this helped me out as well! :)

  28. Brian Patterson

    You are the best ! Thank you!!!!

  29. Jay

    Thank God for Google that found this.. I was pulling my hair out on this!

  30. Bill

    Thank you!!!! You saved me much frustration.

  31. Abu Alam

    You saved my day. Lot of thanks. I have a csv file that has a column with header=“ID”. It was giving me SYLK error. But once I changed it to “Id”, it worked like magic.

  32. Tatiana

    Just ran into this problem. I have cell A1 of each of my CSV files as “ID”. Would’ve never figured this out on my own. Glad this post is still available here after so many years. Thank you so much for sharing. Cheers!

  33. Alun

    Glad to still be of help

  34. Damien

    Thank you !

  35. Laurence

    I’m just thinking…thank GOD for the Internet!

  36. Jay

    Just another drive by thank you. This is more helpful than the offical MS help page on this topic.

  37. Vinutha

    GOD , Would have never figured out this.. wasted one whole day.

  38. Hish

    Thanks mate.

  39. Sebastian

    Thanks for saving me hours of investigating.

  40. The Moog

    I want to have your babies. I love you. Thanks

  41. Homer

    Let’s us know how that works out! Thanks!

  42. Hamish

    It’s the “The file you are trying to open, ‘x.csv’ is in a different format than specified by the extension…” that gets me.

    Thanks for preventing me going blind looking for obscure and non-existent unprintable characters…

  43. David Willis

    Wow. I never would have guessed that was the issue. I changed the first cell from “ID” to “dbID” and now the error is gone. lol

    Thanks!

  44. Dave

    This is amazing. Thanks!

  45. Ajay

    This is awesome help!

  46. Ella

    Thanks a lot!

  47. Steph

    Thank you!

  48. Piotr

    thank you, thank you, thank you!

  49. Simi

    wow Thanks! still not fixed I see. and still helping…………

  50. Joost Carpentier

    Thank you!

  51. Andy

    Thank you so much. Found this and fixed the error.

  52. Maria

    WOW!!!! Awesome! Thank you very much for this post!

  53. Gunnar

    Aargh! Thanks a lot – just wish I had seen your post a couple of hours earlier 😉

  54. Jen G

    Wow!, thanks for your post! That helped so much!

  55. Michael

    D’oh!; thanks for the post. Very helpful

  56. MarkW

    Thanks for taking away the pain – wish I’d checked your site YEARS ago. My previous solution was opening in Open Office then saving as .xls LOL

  57. Jon Rachiele

    Omg — Thank You so much

  58. Rolando

    Thank you, thank you, thank you!!!!!!!!!!!!!!

  59. scaevola

    Thanks man!

  60. Emeline

    Merci !!!!!! Thank you !!!!! You just saved the day !

  62. A thankful Python newbie

    Thank you so much for that info! Even though I have spent a few hours pulling my hair – I would never have arrived at that conclusion without your input.

  63. Sam

    Thanks a bunch!

    This still occurs in Microsoft Office Professional Plus 2010 (version 14.0.7173.5000), although Microsoft doesn’t admit it on their KB support page.

  64. Nicholas Richardson

    I dont know what is more funny, the solution or how many csv files must begin with “ID”. Thanks for post.

  65. Andy T

    Thanks – your post came up in a google and most helpful. I am in the process of moving to Softmaker Office 2016 having previously used MS Office 2003. When moving back and forth I hit this problem and assumed incorrectly it was Softmaker trouble. Yes my database has ID for the first field (column) and hits this problem.

  66. adi

    thank you !!!!!!!!!!!!!!!!!!!!!!!!!!
    thank you !!!!!!!!!!!!!!!!!!!!!!!!!!
    thank you !!!!!!!!!!!!!!!!!!!!!!!!!!
    thank you !!!!!!!!!!!!!!!!!!!!!!!!!!
    you are the best
    worked

  67. David

    Running Excel 2016 in the year 2017 and this is still a problem that you helped me solve! Thank you!

  68. Hamidul Islam

    thank’s bro , i just spend my 4 hours into this .

  69.

    AlunR

    This article is now 5 years old and it’s still helping! Amazed MS haven’t fixed it but the occasional gift from my Amazon gift list and a few affiliate fees for adverts cover off the over 1200 visitors per month just to read this article!

  70. RLU

    Wow – I wasted 3 hours on this problem – As soon as I looked it up (finally – dumb of me to wait) your fix came up!

  71. nah

    1st hit on google search, still an issue after all this time?

  72. Prashant Ganpat Dhuri

    Thanks buddy. You saved a ton of time…

  73. Jeff G

    Thank you for sharing this. I was so confused by the error and would have spend countless hours on the same issue!

  74. Shahabaz

    Still Helping

  75. From Argentina with Love

    Thanks this helped me too, March 31st, 2017

  76. Kirkwood Paul Donavin

    Thank you so much for pulling your hair out for us!

  77. Tom

    Thanks … would never have figured that out!!!

  78. Phil

    Another happy camper….I would never have worked that out myself

    …A thousand thanks mate !!!! 🙂

  79. Ed Flood

    Still helping, thanks a million!

  80. joshelui

    I solved this issue with:
    Application.DisplayAlerts = False
    Workbooks.Open Filename:=path, Local:=True
    Application.DisplayAlerts = True

    Where ‘path’ is a string variable with the path where the file is.
    Obviously this is a VBA solution.

    I hope this help you.

  81. Iguan

    Nice!

  82. Maria M.

    Priceless information! This thread will live on as you keep saving lives out there in MS World. Thank you!!!!

  83. David

    Still helping 🙂

  84. Beach

    Yep.

  85. David

    And again today!

  86. Costi A

    Still useful. Thanks man.
    BR/ Costi A.

  87. Jackie

    OMG, thank you for saving me so much time.

  88. Jeff Langlois

    You sir, are a gentleman and a scholar.

    This resolved my issue so quickly!

    Well done.

  89. Anton

    What a random error, but it also just happened to me !
    Is there anything we can do about it ?
    My CSV file needs to have “ID” as the first element.
    There is no way around it.

    •

      AlunR

      Why do you need ID as the first field? If you are outputting to Excel then you can choose anything as your output format. If you need to then send it on from there it you could then rename it once it is in the system or output it using a macro which renames the first column.

      Alternatively make ID the last column? most imports should sort it out for you?

  90. Eric

    Aug 14, 2017 and still helping!

  91. San

    Aug 29, 2017 and still helping!

  92. Mark

    Wow, years later and this is still awesome. Thanks for the heads-up!

  93. Mousumi

    Thank you so much for sharing this info.Very useful.

  94. SRamu

    Still helping. Thank you so much for this info.

  95. James

    We have discovered if CSV is changed to UTF-8, the SYLK problem disapears (without change ID to Id)

  96. PongoDog

    OMG ! How much time potentially this has saved me. THANK YOU for posting this information.

  97. renat

    Thank you for the information.
    For everyone who needs a workaround:
    https://stackoverflow.com/questions/29012344/opening-csv-file

  98. Abdul Hadi N

    Thank u

  99. Mike

    AMAZING! Saved me 8 hours of hair pulling. Damn excel!! THANKS!!!

  100. Mohamed

    So my very first cell was the state Idaho, what are the odds?

  101. Paul

    stiiiiilll helping

  102. Another grateful user!

    Still Helping!

  103. And Another!

    Ha Ha!! Still helping. I would never have worked that out!

  104. Nicola

    Thanks was very useful

  105. Dan Williams

    Still helping!
    Cheers!

  106. random it guy

    helpful defusing a panicked exec

  107. Amjad

    Trust me still helping

  108. Brian Spolarich

    This is the gift that keeps on giving

  109. rajen

    omg, thanks!

  110. M Miller

    Such a simple solution for such an annoying issue. THANK YOU!

  111. dylanjharris

    STILL HELPING! THANKS ALL

    WHY IS THIS STILL AN ISSUE AFTER 4 YEARS?!

  112. Mark

    Thank you for the quick fix!

  113. Stephanie

    STILL HELPING after SIX years!!!!!! Thank you 🙂

  114. Peter

    Another two thumbs up!!

  115. Daniela

    Still Helping! Thanks!

  116. Bobby

    Still helping. Thanks!

  117. Nicolas

    Still Helping! Thanks!

  118. Dave

    Still Helping!

  119. Joseph

    Still helping! 🙂 Thank you.

  120. AlyShutter

    Yep still helping =D 2018! Thank you!

  121. Lieutenant Geyser

    Haha this is amazing. Thank you for this.

  122. Cees

    Best post ever 😉 Saving me a bunch of time

  123. Steve Rawlinson

    Still Helping! Thank you.

  124. Joe

    Well done Alun. Magic internet

  126. Mike

    This is still helping as of February 15, 2018 – four years later. Microsoft has disabled the solution page listed above. Users now get the Microsoft version of the 404 File Not Found error, so can anyone explain WHY Excel does this? Or has anyone found the new link to Microsoft’s explanation?

  127. Celia

    Thanks a bunch! That prevented me from suicide 🙂

  128. DH

    Still helping

  129. Richartd Noakes

    Come on MS! This is a tiny task to fix – do it now!
    Far too much monkey see, monkey do. Too clever by half! Are you aware that many people import csv files where the first cell contains ID? And they want them to be csv, nothing else.

  130. Nikhil Bhansi

    24th Feb 2018 | Still Helping

  131. Robby

    26 Feb 2018 – same issue, sti9ll inresolved. Original poster’s work much appreciated

  132. STAN

    Thank you very much.

  133. AQ

    Thank you

  134. Mitisha

    Still helping

  135. Doug

    This is pathetic. Thanks to OP. Pulling my hair out trying to analyze data for my master’s thesis. I don’t have words for how angry I am at Microsoft right now.

    Still helping.

  136. Stuart

    Still helping March 2018!

  137. Alex Claudio

    This is still helping! April 2018!

  138. Tim

    Still helping 29th March 18.

    6 years later and Microsoft still can’t be bothered to fix it. Amazing.

  139. Owen

    Still Helping April 18

  140. David

    Another one saved April 2018 from this, frankly, beyond pointless “feature”.

    Thank you very much!

  141. mdcclxv

    Thank you!!!! U da man!

  142. Aromal

    Thanks Buddy 🙂

  143. Aman

    I found another option.no need to change the column heading order title. Just open a new excel and than open the file which you would like to open (SYLK format file), it will open this time.
    Hope this works for you guys as well.

  144. José Sanches

    Thank you! Still help !
    Jsanches

  145. José Sanches

    Thank you!

  146. Niborg

    Still helping!!!!

    Thanks!!

  147. Dhawal

    18th April 2018…
    Still helping!!!
    Thanks a ton, Alun!!
    Just a feedback that I am not able to open MS link given though.

  148. Gav Massingham

    I mean. Just wow Microsoft. Because no one ever wants the first column of their CSV file to be “ID” do they?

  149. Keren

    Thanks! Another person saved hours of trouble by this.

  150. Stacey Britton

    So glad to find this, thanks!

  151. -jm

    day 2,304 and still helping!

  152. Jem Shaw

    Aaaaaand solved!
    You rock.

    “Get Bill Gates in here!”

  153. jamie

    I literally love you

  154. Francois Janoras

    9th May 2018
    … and still helping!

  155. Daniel

    Still Helping! Thank you

  156. CK

    Saved the day 6 years later!

  157. MT

    Still Helping!!, May 2018

  158. Sara

    Oh my goodness… This solution saved me hours. Thanks!

  159. Thomas

    Lifesaver

  160. melon

    thanks heaps for this.

  161. Niharika

    Thanks a ton. Saved lot of time.

  162. Hancel

    This answer will remain Gold for decades to come 😀
    Thank you so much

  163. Aparna

    Thank you.
    God saved the world.
    You saved my day.

  164. Larry Nee

    I found this abomination again today. Can’t wait to switch off of M$ crap in a year.

  165. AC

    Perfect!! HUGE HELP – thanks!

  166. Sulcalibur

    Just had this problem, you were the first result in Google, lol. Cheers buddy!

  167. Hanna

    Thumbs up!

  168. Kathy

    Still Helping! THANKS!!!

  169. Josh Stauffer

    I’m glad your post came up first in the SERP’s. You saved me from pulling my own hair. 🙂

  170. Kiya Sharma

    Thanks Dude,

    You don’t know how how much time of mine you’ve saved.
    GBU !! xD

  171. edel macias

    Thanks you for the info!
    Still helping haha
    Much spam, such helpful

  172. Archana

    Thanks for the info!
    Still helping

  173. Amir

    Thank You. It is help us 😉 fckng excel 😉

  174. Lara Jain

    thanks a lot!!!It worked!!! in 2018 🙂

  175. asd

    thanks

  176. jamie

    Thanks for this so many years later!

    I am quite certain that I told Excel that I was looking for text files (not slky or skly or ksly or Excel or any other type of file). Yes, I chose from a selection that Excel presented me with. I knew I wanted txt files… I chose a text file… even had a .txt extenstion… I told Excel this after it asked me… and wha??? It wants to tell me it’s a slksdksyr file?

    How awful.

    If Microsoft has any hand in building the AI that is suppose to rule the world one day, at least we now know we can just write “ID” on the bullets we shoot at the droids that come to farm us. They will have no defense.

    •

      AlunR

      Just change the first line of your address and your first name to ID.

      They’ll never know who you are or where you live!

  177. Leslie

    NEWEST issue: what if the file already was saved and I am trying to open it when I get this SYLK error?
    I can’t get to the data to view it and fix it 🙁

    •

      AlunR

      Off the top of my head you could try opening it in a proper text editor like Sublime?

      Or open a new sheet and open it as a datasource?

  178. Bern Almazan

    Big help. Thanks a lot.
    Bern

  179. paddy

    Highlight of my weekend. Many thanks.

  180. EC

    Love it

  181. Ravish

    You save my day, Thanks you !!

  182. Mihai

    wow, I almost can’t believe the ID stuff…saved my day too. Thanks!

  183. :)

    Thank you sir. You are a GOD!

  184. Joachim David

    It helps again, yay!

  185. Connie

    OMG… Thanks so much!

  186. Steve

    What Connie says!

  187. Zoe

    September 2018 …. still helping

    I edited the csv in Notepad and changed the header row to have double quotes around all the headings, and it fixed the issue for my file. The quotes are not displayed when loaded in Excel and no warnings. Winning!
    i.e.
    “ID”,”Name”,”Email”

    •

      AlunR

      Yep, that’s another great way to fix the issue. Adding quotes means it’s just treated as a string and no preprocessing takes place. But like when people give you datasets with columns that have names with sql keywords like Select, from, where etc

  188. Ian

    Still helping…saved me many hours of troubleshooting…

  189. Satyendra Mishra

    Thanks a lot, you saved me many hours of troubleshooting.

  190. Mohammad Khan

    Thank you. Thank you. Thank you.

  191. shrinivas

    thanks

  192. Pierre Chesnier

    Can someone try to OPEN A SECOND TIME the file, after Excel gives you errors and opens a blank file ?
    Just, keep the file (excel) open and double clic the file again. What happens there ?

    Pierre C.

    NEVER GIVE UP

  193. izi

    Well, it is 11.14.2018 and I just encountered the same issue! Oy vey!

  194. Cesar Aquino

    Thank you a lot!!!

  195. Andriy

    thanks!

  196. newoutlaw

    Just save the file as a Unicode .txt file. Then rename it to csv and Bob’s your uncle.

  197. Sivasakthi Sivagnanam

    Thank you for everyone who honed into the issue being first cell cannot have text starting with “ID”. cheers folks.

  198. Kenneth Jims Quezon

    Still helping! March 14, 2019

  199. James

    Still helping! March 19, 2019

  200. Heidi

    still helping!!!

  201. Dave Khan

    This saved me. Fortunately, in my case, the use of “ID” as the first two characters in the CSV file was not a rigid requirement; I was able to change it a bit to get around the problem. But I’d have never known what the problem was without this post.

  202. Papa Stauth

    Energizer Bunny “Still helping” …

  203. Jesse Hefter

    Unbelievably helpful. All I had to so was place a Response.Write(“‘”); at the beginning of the file. Thanks so much!

  204. Jason Woolf

    I’ve copied Dave Khan’s Reply above as its a simple case of “Same Here”!

    This saved me. Fortunately, in my case, the use of “ID” as the first two characters in the CSV file was not a rigid requirement; I was able to change it a bit to get around the problem. But I’d have never known what the problem was without this post.

    Many Many Thanks for posting the solution

  205. Milz

    STILL HELPING “2019”+

  206. Jennifer L.

    STILL helping June 1, 2019! For real!

    Also the knowledge base link referenced seems to now be broken :(. Thanks, Microsoft!

  207. Zernk

    This is freaking insane! WTF is wrong with M$? This problem is so 1980’s. Thanks so much for the solution!

  208. Janvhi Kapoor

    Thanks a lot dear ,it worked.

  209. Ankita Oberoi

    Thanks a lot man ,it really worked.

  210. Pernille

    Just another happy user of this brilliant finding!
    Thanks for putting it out there 🙂

  211. Divya Das

    Very nice site,keep on posting.

  212. Sophie Sharma

    Thanks for sharing such a nice post,keep up the good work.

  213. Sophie Sharma

    It really worked for me,keep up the good work.

  214. Sophie Sharma

    It really worked for me,keep up the good work.

  215. Suzanne

    Nov 19 and I’ve solved my issue in minutes rather than hours. Also the .csv file can be viewed fine in Notepad++ without altering the data. Thank you for sharing.

  216. Erin Hunt

    Still helping!

  217. Rachel Rodger

    Thanks so much for sharing this. I have had issues with a csv file that syncs employee details not working correctly. The help desk could not help me – your article solved the issue within two minutes. Still helping eight years after your original post!

  218. S

    wow. mind blown. such a simple bug.

    Still helping 🙂

  219. Christiane Jechoux

    January 29, 2020 –

    Thanks so much for the info/solution. Was going around in circles here, trying to figure out where the error was!

    Merci!

  220. Oli

    Thank you a lot!!!

  221. Abby

    WOW. Still helping 2020

  222. G

    I just found this. THANK YOU. File still opened, but I was curious why i got the prompt.

  223. Vicki

    Thank you! Still helping 🙂

  224. Damián

    Thank you!!! It helps me 🙂

  225. Vijay

    Wow…. truly wonderful post and will be useful to IT folks until Microsoft fixes (probably never)
    March 23 2020

  226. An Phat

    Thank you so much! I have worked on it 12 hours after before see your post.
    After 8 years, it still work.

  227. Leo Liu

    works for me!
    Thanks a lot. It saves me at least 4 hours.

  228. Ricardo Uhalde

    Bug present in Excel 2003 and Excel 2010, go Microsoft!!

    •

      AlunR

      Microsoft would tell you it’s a feature to benefit the 0.001% of users who work with SYLK on a daily basis 😀

  229. Subrato

    It really worked , I suggest to provide a complete different name for the first column.
    Thanks

  230. ka

    sad to say that as of 08/2020, this post still helped me when someone discovered this issue by accident when opening a .csv file using Excel 2016! Thanks so much.

  231. Sonam Khannna

    It really worked,thanks.

  232. Isika Patel

    Thanks for the greatt post.

  234. Aiysha Goyal

    Awesome post,keep it up.

  235. lukas

    Saved my ass my friend

  236. Alison

    Thank you for this! You’ve just saved me a ton of time with your 8 year old post. 🙂

  237. tim

    Still helping awesome ! after 9 years

  238. Gill

    Hello from 2021! Thanks for the extremely useful tip and clear explanation.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress & Theme by Anders Norén