Reading from a .CSV file in C# returns an unwanted backslash, how do I get rid of it?

Question

Okay, so to keep this short, here is my issue. Apologies in advance since I'm not next to my workstation and can't attach images from the debugger as I had originally planned.

My job requires us to work with a large amount of CSV/Excel files. We take those files, put them in one of our programs that changes the format and whatnot, and then we insert those files into our database to generate reports.

A short description of my program: it needs to validate if a CSV file has the correct headers before we do any further manipulation to it. It needs to have a cell with some text and a number. In case that number is not there the program reads it from another line in the file, since it exists there and I just use that value to create the header manually.

Whenever I read the value from the file, I get the value I need, but it has an unwanted backslash. Unfortunately I can't attach a picture from the debugger, but it looks something like what's in the parantheses(""11111111""). Now as some others have mentioned in other threads, when I print the value to the console or even to a message box, the slashes and the extra quotation marks are not there. I need to convert the value into a number, so I tried using string.Repalce() in order to get rid of the slash and the extra quotation marks.

I can do this:

accountNo = accountNo.Replace(@"\", "");

But that doesn't solve the problem of the extra quotation marks. Because when I try and do this:

accountNo = accountNo.Replace(@"\"", "");

I get syntax errors and my program crashes. Now I know that Excel doesn't show quotes unless you use double quotes, and since a CSV file is like a text file, C#'s StreamReader just reads the file with the quotes as a value and then wraps it in another set of quotes by default because that variable is a string.

Is there a reliable way to get rid of the backslash and the extra quotation marks? I thought about maybe using Regex, but I don't know Regex at all...

Thanks!

I strongly suggest using one of the popular CSV libraries such as https://www.nuget.org/packages/CsvHelper/ because of some complexities of the CSV format such as quoting commas and escaping quotes. — Eric J., Jul 16 '20 at 06:25
The CSV file is likely fine, and your own code is simply failing to correctly handle escaping used in the file. You could fix that, but you can't get help with that without a [mcve] showing your implementation. But you shouldn't bother anyway...just use an existing CSV API. See marked duplicate for oodles of options. — Peter Duniho, Jul 16 '20 at 07:00
if you mark a string with @ you need to use double quotes instead of escaping it with backslash. in your case it should look like this: `@""""` or like this: `"\""` — ndreisg, Jul 16 '20 at 07:03
@PeterDuniho Unfortunately what you and Eric both mentioned isn't an option for me. Regardless I tried using one of the CSV or Excel libraries and always had trouble with them. If we're on that topic, do you have any good resources that help with using C# on Excel and CSV files? — DareRelaqz, Jul 16 '20 at 07:08
_"what you and Eric both mentioned isn't an option for me"_ -- then you need to fix your code. But you can't get help with that using the post you provided here. You need to post a more specific question, one that includes a [mcve] that shows exactly how you are parsing the CSV, and what data isn't being handled correctly. FWIW, every one of the three answers posted so far is a hack. None involve actually handled character escaping; they just kludge around the specific data you might be dealing with. — Peter Duniho, Jul 16 '20 at 07:10

score 1 · Answer 1 · answered Jul 16 '20 at 06:17

1

Did you try accountNo = accountNo.Replace("\"", "");?

Or use method Trim() like accountNo = accountNo.Trim('"').

answered Jul 16 '20 at 06:17

大陸北方網友

3,696
3
12
37

Trim is what fixed it for me. I actually tried it just a few minutes after posting and it worked, but I felt silly answering my own question. I know there are different ways the same result, which others have mentioned as well. Thanks! – DareRelaqz Jul 16 '20 at 07:10

score 1 · Answer 2 · edited Jul 16 '20 at 09:08

1

Rather than trying to replace with empty quotations, try to replace using string.Empty.

// a = ""1111""
string a = ("\"\"1111\"\"");

// b = 1111
string b = a.Replace("\"", string.Empty);

// c = 1111 but as an int
int c = int.Parse(b);

edited Jul 16 '20 at 09:08

Peter Csala

17,736
16
35
75

answered Jul 16 '20 at 06:21

JulianCSalazar

46
4

CloudInSky · Answer 3 · 2020-07-16T06:54:11.087

1

The following regex should do the job

string accountNo = Regex.Match(yourString, @"\d+").Value;

Then you can parse it as int

try
{
    int m = Int32.Parse(accountNo);
}
catch (FormatException e)
{
    Console.WriteLine(e.Message);
}

edited Jul 16 '20 at 06:54

answered Jul 16 '20 at 06:48

CloudInSky

13
1
5

Please prefer `int.TryParse` over `int.Parse` + explicit try-catch block. – Peter Csala Jul 16 '20 at 07:07
1

@PeterCsala I was gonna use TryParse, just to validate that there's an actual number to the value and not an empty nothing. – DareRelaqz Jul 16 '20 at 07:11

Reading from a .CSV file in C# returns an unwanted backslash, how do I get rid of it?

3 Answers3