C# CSV reader that can handle truncated rows?

Question

Best practice question:

I have a CSV file that looks like this:

1,2,3

1,,3

,2,3

,,3

1

1

1

I want it to go into SQL:

1,2,3

1,null,3

null,2,3

null,null,3

1,null,null

1,null,null

1,null,null

The last three rows are giving me problems. Without the correct number of commas, my "cmd.Parameters.Add" throws an exception: IndexOutOfRangeException when no value has been assigned to that location in the array. I would like it to just pass null values like it already does for instances where a comma delimits an empty space.

while
{
string parts = new string[10];
parts = parser.ReadFields();
if (parts==null)
{break};

SqlCommand cmd = new SqlCommand("sp", conn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@one",SqlDbType.Int).Value = parts[0];
cmd.Parameters.Add("@two",SqlDbType.NVarChar).Value = parts[1];
cmd.Parameters.Add("@three",SqlDbType.Float).Value = parts[2];

[run cmd]

How should I be doing this? I have a couple hundred parameters, so avoiding a solution where I test every single value for null and then apply DBNull.Value would be best if possible. Is this [string parts = new string[10];] my problem? Is there a better way to declare this array that will avoid these exceptions?

You problem is accessing `parts[0..n]` when your `ReadFields` method doesn't return an array with 10 parts... Can you show that function's code ? — Bun, Jun 25 '14 at 15:20
@Bun: i will ad it in, but its using the built-in VS TextFieldParser. — user2366153, Jun 25 '14 at 16:33
@Tim: It does not seem to allow me to set the default value as null? — user2366153, Jun 25 '14 at 16:34
@Tim: indeed, but when I try to assign the parameter to parts[2] it throws an exception if the CSV import did not get to parts[2] before reaching the end of the line. It basically says I cannot do that because my array does not have enough elements...theres not even a spot for the null to go. I am very new to programming in general, so I might not be making sense. Thanks! — user2366153, Jun 25 '14 at 16:48

score 0 · Accepted Answer · answered Jun 25 '14 at 15:22

string parts = new string[10]; is wrong (it should just be string[] parts;). I'm amazed that compiles, with the mismatch between the declared and assigned types.

But it's not the problem here.

Try changing this line:

if (parts==null)

to this:

if (parts==null || parts.Length < 3)

Or, if you need those rows:

cmd.Parameters.Add("@one",SqlDbType.Int).Value = parts[0];
cmd.Parameters.Add("@two",SqlDbType.NVarChar).Value = parts[1] ?? DBNull.Value;
cmd.Parameters.Add("@three",SqlDbType.Float).Value = parts[2] ?? DBNull.Value;

I could not get the ?? function to work in my situation, but the OR did the trick. Thank you! — user2366153, Jul 02 '14 at 14:36

score 0 · Answer 2 · answered Jun 25 '14 at 15:23

0

Couldn't you simply use ElementAtOrDefault or check the Length of the string[]?

string value = parts.ElementAtOrDefault(1);
if(value == null)
    cmd.Parameters.Add("@two",SqlDbType.NVarChar).Value = DBNull.Value;
else
    cmd.Parameters.Add("@two",SqlDbType.NVarChar).Value = value;

answered Jun 25 '14 at 15:23

Tim Schmelter

450,073
74
686
939

You can use the null coalescing operator to sub in `DBNull` for `null` – Servy Jun 25 '14 at 15:25
@Servy: i wasn't sure if that compiles. **Edit** It does not: `cmd.Parameters.Add("@two", SqlDbType.NVarChar).Value = value ?? DBNull.Value;` – Tim Schmelter Jun 25 '14 at 15:26
yeah, you might need a cast to `object` in there. Of course, you can just create a new extension method to support it as well, which is likely worth doing. – Servy Jun 25 '14 at 15:27
@ Servy & Tim: I attempted the same command as Tim wrote out, but it wouldn't compile. I am afraid I am not really sure what the coalesce operator is doing. I will research it. – user2366153 Jun 25 '14 at 16:44

C# CSV reader that can handle truncated rows?

2 Answers2