Problems with the comma delimiter and the number of columns when reading in the csv file in C #


I'm a fairly new coder, and everything I'm working on I have completely taught myself. I'm trying to read in CSV files and then eventually output them to an SQL server. I read a lot about how to do this and decided to implement it using the FileHelpers in Visual Studio 2015.

An issue that I'm running into is that some of the data itself contain commas (ex: 9,500). I know many people suggest putting quotes around the number. However, I am not the one inputting the numbers. The file is just sent to me that way, and I am supposed to write the code to input it into an SQL server. Is there any other way to fix that issue other than quotation marks? Also, some of my rows are shorter than others.

ex: Joe, John, Jim, Ben
    35,  42,   35,  60
    A,   B,     C,  D
    40,  50

So for example, that last row does not have the same amount of values. They are not null either. I have already addressed null values.

It just doesn't include them, and when I go running my code, I get an error-

" Line: 29 Column: 4. Delimiter ',' not found

after field 'Hours' (the record has fewer fields, the delimiter is wrong, or the next field must be marked as optional)."

How would I go about fixing these issues? When I remove the rows containing comma values such as 9,500 and when I also remove the incomplete rows, my code works perfectly. However, I need it to work for all of the data. Thanks

Is there any other way to fix that issue other than quotation marks?

Yes, you could perform a string replace on a comma-space combination with something else like an asterisk. And then split on the asterisk or special character to get an array of items.

string input = "Joe, John, Jim, Ben 35, 42, 35, 60, 9,500";
string output = input.Replace(", ", "*");
string[] myArray = output.Split('*');

In a case where you had the value 9,500, it would interpret that value as one item rather than two, because there is no space after the comma.

But, you're better off having those values arrive in quotes in your CSV. If you've got Excel installed, add row of numbers, with some containing commas. Then save your workbook as a CSV, and then open the CSV in Notepad. You'll see that numbers with commas are wrapped in double quotes. Here is an example from a test I just performed.

Numbers entered in a row:  9,000, 1, 8, 7, 500, 125,050
CSV text: "9,000",1,8,7,500,"125,050"

I hope that helps you out. Good luck.