CODEWITHSUNDEEP
c#visual-studiocsvfilehelpers
Brandon t
Brandon t

Reputation: 33

Issues with comma delimiter and number of columns when reading in 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

Upvotes: 0

Views: 1893

Answers (3)

user1023602
user1023602

Reputation:

The "short row" issue can be fixed in software fairly easily:

  • Load each row whole, into a List(of String)
  • Calculate the number of cells for each row
  • Get the maximum number of cells used
  • For each "short row", add blank cells until it matches the maximum

However if people are entering commas in numbers (e.g. 9,500) then you are stuffed, and will need to ask for a properly-formatted CSV file.

From the example data in the question, its a "short row" issue.

Upvotes: 0

Marcos Meli
Marcos Meli

Reputation: 3506

You must use

[FieldOptional]

over the 3th and 4th column, like:

[DelimitedRecord(",")]
public class MyRecord
{

   public string Field1;
   public string Field2;
   [FieldOptional]
   public string Field3;
   [FieldOptional]
   public string Field4;

}

Upvotes: 1

Ken Palmer
Ken Palmer

Reputation: 2445

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.

Upvotes: 0

Related Questions