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

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
karthik80cAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lokesh B RDeveloperCommented:
Hi,

In which line it is throwing error?
prabhu rajendranCommented:
Code pls.
Lokesh B RDeveloperCommented:
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.
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

karthik80cAuthor 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 RDeveloperCommented:
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
karthik80cAuthor Commented:
Hi Lokesh ,

your are correct lokesh i have 30 columns in my database . is my csv file is wrong
prabhu rajendranCommented:
Yes, It is.
karthik80cAuthor Commented:
Thanks prabhu rajendran  ill   check it now
karthik80cAuthor Commented:
am using a new csv file its still remains same error
a---Copy.csv
Lokesh B RDeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
karthik80cAuthor Commented:
Hi Lokesh

Now its reporting as
System.InvalidOperationException: The given ColumnMapping does not match up with any column in the source or destination.
Snarf0001Commented:
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 RDeveloperCommented:
Hi,

Debug and let me know where you are getting this.
karthik80cAuthor Commented:
Hey Friends I think i missed out a mapping in a column .When i corrected its works Great . Thanks Experts for tremendous Effort
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.