Facing 'cannot convert from string to int' error when replace and split csv

Question

I want to make the csv more readable so I want to replace the special character " in csv and split it by using , and here is my code:

for(int i = 1; i < 10200; i++)
{
    string valueBefore = specialSheet.Cells[i, 1].Value;
    List<string> valueAfter= new List<string>(valueBefore.Replace("\"", ""));

    for(int j = 1; j < 28; j++)
    {
        specialSheet.Cells[i, j].Value = valueAfter[j];
    }
}

The csv before my code:


""""a, b, c, d, e, f, g""""
""""h, i, j, k, l, m, n""""

The csv after my code (this is what i want):


a	b	c	d	e	f	g
h	i	j	k	l	m	n

but now i receive an error:

CS1503  Argument 1: cannot convert from 'string' to 'int'

Can anyone help to see if there are any error in my code? Thanks.

What are you expecting `new List(valueBefore.Replace("\"", ""));` to do? If you're trying to create a new list with a single entry, you want `new List { valueBefore.Replace("\"", "") };` — Jon Skeet, Sep 09 '21 at 08:36
Thanks Jon, I use excel to open csv therefore there are """"" special characters in each row of the value, I want to replace it before split into the list — Jason Wong, Sep 09 '21 at 08:38
That's completely irrelevant to my question though. The CSV part is irrelevant - you'd get the same error with `new List("text")` - I'm asking what you'd expect that to *do*. If you're expecting it to create a new list with a single entry, you need to use a collection initializer, as I said in my first comment. — Jon Skeet, Sep 09 '21 at 08:40
You are missing the call to split method : List valueAfter= new List(valueBefore.Replace("\"", "").Split(",")); — Aman B, Sep 09 '21 at 08:43
Hi Jon, I want to split the data store in ```valuebefore``` by comma and store it into the list, and then I would like to use a for-loop to paste it. This is the reason I want to create a new list. — Jason Wong, Sep 09 '21 at 08:44
Again, the matter of "the value you're trying to store in the list" is irrelevant to the compilation error you're getting. You're trying to pass a single string value into a `List` constructor. Look at the constructors for `List` - there isn't one accepting a `T`. I've shown you how to create a list with a single entry. Have you tried that yet? Once you've understood the compilation error, you can work on the splitting side of things. — Jon Skeet, Sep 09 '21 at 08:58
Is there a reason you've hardcoded 10200 and 28 into your loops? — sr28, Sep 09 '21 at 10:16

score 1 · Accepted Answer · answered Sep 09 '21 at 10:14

I think I understand what you're getting at. Assuming you want to do the following:

Remove all double quotes from the start and end of the string.
Split the string by comma to get the individual component parts

To do that you can do the following:

First, create a method to 'cleanse' your string. In this case I've assumed you only want to remove white space (method taken from here: Efficient way to remove ALL whitespace from String?):

private string RemoveWhitespace(string input)
{
    return input.ToCharArray().Where(c => !char.IsWhiteSpace(c))
        .ToArray()
        .ToString();
}

Then use this method in conjunction with trimming the double quotes and splitting the result by whatever the expected delimiter is:

var valueAfter = RemoveWhitespace(specialSheet.Cells[i, 1].Value).Trim('\"').Split(',');

brandwegg · Answer 2 · 2021-09-09T09:53:51.550

I don't know exactly what you're trying to accomplish (since I haven't really worked with cvs files before) but if it's just removing trash signs I'd probably split it into a char[] and do something like:

string valueBefore = "\"\"\"\"a, b, c, def, g, hi, j\"\"\"\"";
Console.WriteLine(valueBefore);
char[] chars = valueBefore.ToCharArray();
List<char> clean = new List<char>();

foreach(char c in chars)
    if (c == ',')
        clean.Add(','); // if you want any dividers left
    else if ((c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z'))
    {
        clean.Add(c);
    }

string valueAfter = new string(clean.ToArray()); // or save return value of clean.ToArray() in a char[].
    Console.WriteLine(valueAfter);

it's fairly dynamic (you can just change the if statement if there's any additional chars you want to include) and should work for any amount of chars between the commas (up to a reasonable length).

If I were to guess you'd probably want to do

char[] valueAfter = clean.ToArray();
for(int j = 1; j < 28; j++)
    {
        specialSheet.Cells[i, j].Value = valueAfter[i,j];
    }

but then I wouldn't include the clean.Add(',');

Facing 'cannot convert from string to int' error when replace and split csv

2 Answers2