Are you creating a CSV (Comma Separated Value) or TSV (Tab Separated Value) file in CSharp and you don’t know why your CSV file is breaking when you open it into Excel, then this post is for you.
Mr. Brian has written a very nice article on how to generate CSV file which we referred in our project.
It was difficult to identify the problem. Values in object were html based and we were doing Html.Encode, so i thought that we need to do Html.Decode, but was unlucky.
To get better understanding of Html.Decode and Html.Encode refer: http://www.dotnetperls.com/httputility
Then thought that it can be Encoding issue so tried to pass different encoding formats to StreamWriter but was unlucky in that too. I tried to identify in depth and reached to the problem and then solution.
As my values were in Html format it were containing “t” or “n” or “r” which were creating an issue when writing a file using StreamWriter. As it get “t” or “n” or “r” it was breaking the file.
Once you set the row value in string object ( According to Brian’s Post i.e. whatToWrite object ), then you just need to replace whatToWrite object with the following:
whatToWrite = Regex.Replace(whatToWrite, @"t|n|r", "");
With this we just replace “t” or “n or “r with “”.
It will require the reference of System.Text.RegularExpressions, which you’ll have to add at the top.
Hope this helps you.
Happy Coding… 🙂