0

I'm setting up a back office for our admin team on our website and looking to allow the user to update the database via a csv file import setup.

My code looks like this:

protected void Upload(object sender, EventArgs e)
    {
        //Upload and save the file
        string csvPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
        FileUpload1.SaveAs(csvPath);

        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[13] { new DataColumn("ID", typeof(int)),
        new DataColumn("ZooplaURL", typeof(string)),
        new DataColumn("Branch",typeof(string)),
        new DataColumn("HouseNumber",typeof(string)),
        new DataColumn("PropAddress",typeof(string)),
        new DataColumn("Town",typeof(string)),
        new DataColumn("County",typeof(string)),
        new DataColumn("Postcode",typeof(string)),
        new DataColumn("Price",typeof(string)),
        new DataColumn("PropType",typeof(string)),
        new DataColumn("Beds",typeof(string)),
        new DataColumn("PropStatus",typeof(string)),
        new DataColumn("Weeks",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++;
        }
    }
}

string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
    {
        //Set the database table name
        sqlBulkCopy.DestinationTableName = "dbo.Zoopla";
        con.Open();
        sqlBulkCopy.WriteToServer(dt);
        con.Close();
    }
}
}
}

}

The error it returns when uploading is as follows: Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.IndexOutOfRangeException: Cannot find column 13.

Source Error: 

Line 72:             foreach (string cell in row.Split(','))
Line 73:             {
Line 74:                 dt.Rows[dt.Rows.Count - 1][i] = cell;
Line 75:                 i++;
Line 76:             }

The CSV file has 13 columns being imported and the database also has 13 columns.

SavageDragon87
  • 57
  • 1
  • 1
  • 9

1 Answers1

3

The columns are zero-based, so your i is 13, meaning your CSV has more columns than 13 at row.Split(',').

This can occur if the input contains quoted strings that contain commas, like 42, "Foo, Bar", 43, which in CSV terms are 3 columns, but according to string.Split(',') are four.

Use a CSV library to handle this, don't reinvent the wheel. See Parsing CSV files in C#, with header.

Community
  • 1
  • 1
CodeCaster
  • 147,647
  • 23
  • 218
  • 272
  • The CSV has only 13 columns within it. Unless I'm missing what you're saying? – SavageDragon87 Sep 04 '15 at 10:45
  • 1
    _"The CSV has only 13 columns within it"_ - that may be what you think, but the exception you get states otherwise. Perhaps `string.Split()` is not the proper way to parse CSV, for example if the input contains quoted commas (`42, "Foo, Bar", 43`). – CodeCaster Sep 04 '15 at 10:46
  • I think you're right. I've just seen that in the csv some of the cells contain comma's within them. What would be the alternative way to seperate columns other than comma? – SavageDragon87 Sep 04 '15 at 10:48
  • 1
    Use a CSV parsing library. See [Parsing CSV files in C#](http://stackoverflow.com/questions/2081418/parsing-csv-files-in-c-sharp). – CodeCaster Sep 04 '15 at 10:49
  • Could you post your response i.e. the one about comma seperation. I'll mark it as answered! That's what it was. Just need to sort parsing it a different way. Thanks! – SavageDragon87 Sep 04 '15 at 10:53