0

I am trying import a csv file into my database. The file has 10 columns. Just for testing purposes i first imported only the firs 4 columns and it worked fine but when i try to import all columns i get an error saying cannot find column 10.

This is my code

`

        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[10] { new DataColumn("Invoice", typeof(string)),
        new DataColumn("P.O.Number", typeof(string)),
        new DataColumn("Line", typeof(int)),
        new DataColumn("Invoice_Date",typeof(DateTime)),
        new DataColumn("Gross", typeof(string)),
        new DataColumn("Disc", typeof(string)),
        new DataColumn("NET", typeof(string)),
        new DataColumn("Date_PD", typeof(string)),
        new DataColumn("Check#_Doc#", typeof(string)),
        new DataColumn("Additional_Info", typeof(string))});


        string csvData = File.ReadAllText(csvPath);
        foreach (string row in csvData.Split('\n'))
        {
            if (!string.IsNullOrEmpty(row))
            {
                dt.Rows.Add();
                int i = 0;
                foreach (string cell in row.Split(','))
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell;
                    i++;
                }
            }
        }`
Saaharjit Singh
  • 125
  • 1
  • 1
  • 14
  • 3
    Maybe one of your field have a `,` inside which cause one of your line to have an 11 fields (or maybe even more) which would cause your code to crash with such an error code. – litelite Aug 14 '17 at 20:06
  • If you have ten columns, they will be referenced in a C# array as 0 through 9, not 1 through 10. – Bob Kaufman Aug 14 '17 at 20:08
  • @litelite i have dollar value stored in one of the column which has "," – Saaharjit Singh Aug 14 '17 at 20:08
  • check the value of `i`. i cannot = 10 as your index starts at 0, so your loop should stop when i = 9 – ArcX Aug 14 '17 at 20:08
  • @SaaharjitSingh so at that line, `.split(',')` will return 11 fields. So in your loop, `i` will have a value of 10 when the max index of your array is 9. Causing the error you see. The easy wait out of this problem would be to find a nice CSV library. – litelite Aug 14 '17 at 20:10

1 Answers1

0

Your index of your columns starts at 0 and ends at 9 not 10. You are trying to add a column index that does not exist.

int i = 0;
foreach (string cell in row.Split(','))
{
    if(i == 10)
        throw new ArgumentOutOfRangeException(nameof(i));


    dt.Rows[dt.Rows.Count - 1][i] = cell;
    i++;
}

The exception will prevent bad data from being written to the datasource if you have extra , commas in your source.

I would suggest investing in the time in converting your source to a | pipe delimited separated file as , commas can be commonly used in data itself.

ArcX
  • 687
  • 8
  • 20
  • But then if a field contains a `,`, the data will be loaded in the wrong field. and some will be ignored silently – litelite Aug 14 '17 at 20:18
  • Yes, that's what's happening – Saaharjit Singh Aug 14 '17 at 20:19
  • Agreed, the ideal way would be to use a library to manage the CSV data, see - https://stackoverflow.com/questions/1941392/are-there-any-csv-readers-writer-libraries-in-c 'Josh Close' recommends `CsvHelper` which is an easy to use CSV library. – ArcX Aug 14 '17 at 20:21
  • @Unknown Or, if a `,` in a field is invalid, throw an exception. It's usually better for a program to crash than to produce bad data. – litelite Aug 14 '17 at 20:22
  • @ litelite I agree with that. Usually I prefer `|` pipe delimited files for such things as `,` commas are commonly used in the data itself such as separating names etc.. eg: "John, Smith" – ArcX Aug 14 '17 at 20:25
  • Thanks for the suggestion – Saaharjit Singh Aug 14 '17 at 20:27
  • @litelite edited answer to prevent incorrect data from being written as pointed out and provided an alternitive approach. – ArcX Aug 15 '17 at 05:20