ASP.Net Error-System.IndexOutOfRangeException: Cannot find column 30.

karthik80c
karthik80c used Ask the Experts™
on
Hi ,

Am Trying to Upload CSV to my server but it keep on showing Error as
System.IndexOutOfRangeException: Cannot find column 30.
but my csv has 31 comma seperated values in it . Any Suggestions Experts
a---Copy.csv
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Lokesh B RDeveloper
Top Expert 2015

Commented:
Hi,

In which line it is throwing error?
Code pls.
Lokesh B RDeveloper
Top Expert 2015

Commented:
Hi,

I think you are trying to add data to the table column 30 which is not there.

Table is not having the 30 columns.
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Author

Commented:
Hi Guys ,

Line No 139 Throwing Error as   tblcsv.Rows[tblcsv.Rows.Count - 1][count] = FileRec;

and below is c# code

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


public partial class Import : System.Web.UI.Page
{
    SqlConnection con;

    string sqlconn;
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {

    }

    protected void Button2_Click(object sender, EventArgs e)
    {

    }
   

    private void connection()
    {
         sqlconn = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
        con = new SqlConnection(sqlconn);

    }
    //Function to Insert Records  
    private void InsertCSVRecords(DataTable csvdt)
    {

        connection();
        //creating object of SqlBulkCopy    
        SqlBulkCopy objbulk = new SqlBulkCopy(con);
        //assigning Destination table name    
        objbulk.DestinationTableName = "Company";


        //Mapping Table column    
        objbulk.ColumnMappings.Add("CompanyId", "CompanyId");
        objbulk.ColumnMappings.Add("agent_id", "agent_id");
        objbulk.ColumnMappings.Add("CompanyOwner","CompanyOwner");
        objbulk.ColumnMappings.Add("CompanyOwnerId", "CompanyOwnerId");
        objbulk.ColumnMappings.Add("CompanyName", "CompanyName");
        objbulk.ColumnMappings.Add("CreatedBy", "CreatedBy");
        objbulk.ColumnMappings.Add("CreatedById", "CreatedById");
        objbulk.ColumnMappings.Add("ModifiedBy", "ModifiedBy");
        objbulk.ColumnMappings.Add("ModifiedById", "ModifiedById");
        objbulk.ColumnMappings.Add("CreatedTime", "CreatedTime");
        objbulk.ColumnMappings.Add("ModifiedTime", "ModifiedTime");
        objbulk.ColumnMappings.Add("LastActivityTime", "ModifiedTime");
        objbulk.ColumnMappings.Add("Territories", "ModifiedTime");
        objbulk.ColumnMappings.Add("ACNNumber", "ACNNumber");
        objbulk.ColumnMappings.Add("ABNNumber", "ABNNumber");
        objbulk.ColumnMappings.Add("TrusteeInformation", "TrusteeInformation");
        objbulk.ColumnMappings.Add("RegistrationDate", "RegistrationDate");
        objbulk.ColumnMappings.Add("ASIC", "ASIC");
        objbulk.ColumnMappings.Add("Status", "Status");
        objbulk.ColumnMappings.Add("ClientType", "ClientType");
        objbulk.ColumnMappings.Add("LastContacted", "LastContacted");
        objbulk.ColumnMappings.Add("OrganizationType", "OrganizationType");
        objbulk.ColumnMappings.Add("Agent", "Agent");
        objbulk.ColumnMappings.Add("Address", "Address");
        objbulk.ColumnMappings.Add("City", "City");
        objbulk.ColumnMappings.Add("Province", "Province");
        objbulk.ColumnMappings.Add("PostalCode", "PostalCode");
        objbulk.ColumnMappings.Add("Note", "Note");
        objbulk.ColumnMappings.Add("TenancyAddress", "TenancyAddress");
        objbulk.ColumnMappings.Add("SQLID", "SQLID");
        //inserting Datatable Records to DataBase    
        con.Open();
        objbulk.WriteToServer(csvdt);
        con.Close();


    }

    protected void UploadButton_Click(object sender, EventArgs e)
    {
        //Creating object of datatable  
        DataTable tblcsv = new DataTable();
        //creating columns  
        tblcsv.Columns.Add("CompanyId");
        tblcsv.Columns.Add("agent_id");
        tblcsv.Columns.Add("CompanyOwner");
        tblcsv.Columns.Add("CompanyOwnerId");
        tblcsv.Columns.Add("CompanyName");
        tblcsv.Columns.Add("CreatedBy");
        tblcsv.Columns.Add("CreatedById");
        tblcsv.Columns.Add("ModifiedBy");
        tblcsv.Columns.Add("ModifiedById");
        tblcsv.Columns.Add("CreatedTime");
        tblcsv.Columns.Add("ModifiedTime");
        tblcsv.Columns.Add("LastActivityTime");
        tblcsv.Columns.Add("Territories");
        tblcsv.Columns.Add("ACNNumber");
        tblcsv.Columns.Add("ABNNumber");
        tblcsv.Columns.Add("TrusteeInformation");
        tblcsv.Columns.Add("RegistrationDate");
        tblcsv.Columns.Add("ASIC");
        tblcsv.Columns.Add("Status");
        tblcsv.Columns.Add("ClientType");
        tblcsv.Columns.Add("LastContacted");
        tblcsv.Columns.Add("OrganizationType");
        tblcsv.Columns.Add("Agent");
        tblcsv.Columns.Add("Address");
        tblcsv.Columns.Add("City");
        tblcsv.Columns.Add("Province");
        tblcsv.Columns.Add("PostalCode");
        tblcsv.Columns.Add("Note");
        tblcsv.Columns.Add("TenancyAddress");
        tblcsv.Columns.Add("SQLID");

        //getting full file path of Uploaded file  
        System.IO.StreamReader stream = new System.IO.StreamReader(FileUploadControl.PostedFile.InputStream);

        //string CSVFilePath = Path.GetFullPath(FileUploadControl.PostedFile.FileName);

        //Reading All text  
        string ReadCSV = stream.ReadToEnd();
        //string ReadCSV = File.ReadAllText(CSVFilePath);
        //spliting row after new line  
        foreach (string csvRow in ReadCSV.Split('\n'))
        {
            if (!string.IsNullOrEmpty(csvRow))
            {
                //Adding each row into datatable  
                tblcsv.Rows.Add();
                int count = 0;
                foreach (string FileRec in csvRow.Split(','))
                {
                    tblcsv.Rows[tblcsv.Rows.Count - 1][count] = FileRec;
                    count++;
                }
            }


        }
        //Calling insert Functions  
        InsertCSVRecords(tblcsv);

    }
}

Open in new window

Lokesh B RDeveloper
Top Expert 2015

Commented:
Hi,

In datatable you have 30 columns and which is 0 based index.

I think from the CSV split string contains more than 30 values with comma separated.

So you are getting the error.

Check the count here before loop.

int valuesCount = csvRow.Split(',').Count(); // it will be more than the columns in datatable

Author

Commented:
Hi Lokesh ,

your are correct lokesh i have 30 columns in my database . is my csv file is wrong
Yes, It is.

Author

Commented:
Thanks prabhu rajendran  ill   check it now

Author

Commented:
am using a new csv file its still remains same error
a---Copy.csv
Developer
Top Expert 2015
Commented:
Hi,

whatever the file is, if the columns or values from CSV are more than the datatable columns you will get the error.

So skip if more values are there from CSV using the below code.


 
foreach (string csvRow in ReadCSV.Split('\n'))
        {
            if (!string.IsNullOrEmpty(csvRow))
            {
                //Adding each row into datatable  
                tblcsv.Rows.Add();
                int count = 0;
                foreach (string FileRec in csvRow.Split(','))
                {
                     if(count > 29) // more then 30 columns quit.
                       break;

                    tblcsv.Rows[tblcsv.Rows.Count - 1][count] = FileRec;
                    count++;
                }
            }
        }

Open in new window

Author

Commented:
Hi Lokesh

Now its reporting as
System.InvalidOperationException: The given ColumnMapping does not match up with any column in the source or destination.
Though it's of course best practice to check for column counts as suggested above, the actual problem is much simpler, and pretty common.

You have an extra line break in your file.
The last "row" in the file is blank, so will have length = 0 when you try to split on it.
Remove that extra line break and it will work properly.
Lokesh B RDeveloper
Top Expert 2015

Commented:
Hi,

Debug and let me know where you are getting this.

Author

Commented:
Hey Friends I think i missed out a mapping in a column .When i corrected its works Great . Thanks Experts for tremendous Effort

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial