Input string was not in a correct format in c#, int value is not in correct format

Question

Following is the code for it:

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


            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[7] 
            {
            new DataColumn("pataintno", typeof(int)),
            new DataColumn("Firstname", typeof(string)),
            new DataColumn("Lastname",typeof(string)),
            new DataColumn("Age", typeof(int)),
            new DataColumn("Address", typeof(string)),
            new DataColumn("Email", typeof(string)),
            new DataColumn("Phno", typeof(int)),});


            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["cnstr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(consString))
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {
                    //Set the database table name
                    sqlBulkCopy.DestinationTableName = "Pataint";
                    con.Open();
                    sqlBulkCopy.WriteToServer(dt);
                    con.Close();
                    Array.ForEach(Directory.GetFiles((Server.MapPath("~/App_Data/"))), File.Delete);
                }
            }
        }
        else
        {
            Label1.Text = "PlZ TRY AGAIN";
        }
    }

Answer 1

You have a DataTable with 3 fields of type integer, the error says that one or more of the data extracted from your file are not valid integers.

So you need to check for bad input (as always in these cases)

    // Read all lines and get back an array of the lines
    string[] lines = File.ReadAllLines(csvPath);

    // Loop over the lines and try to add them to the table
    foreach (string row in lines)
    {
        // Discard if the line is just null, empty or all whitespaces
        if (!string.IsNullOrWhiteSpace(row))
        {
            string[] rowParts = row.Split(',');

            // We expect the line to be splittes in 7 parts. 
            // If this is not the case then log the error and continue
            if(rowParts.Length != 7)
            {
                // Log here the info on the incorrect line with some logging tool
                continue;
            }

            // Check if the 3 values expected to be integers are really integers
            int pataintno;
            int age;
            int phno;

            if(!Int32.TryParse(rowParts[0], out pataintno))
            {
               // It is not an integer, so log the error
               // on this line and continue
               continue;
            }
            if(!Int32.TryParse(rowParts[3], out age))
            {
               // It is not an integer, so log the error
               // on this line and continue
               continue;
            }
            if(!Int32.TryParse(rowParts[6], out phno))
            {
               // It is not an integer, so log the error
               // on this line and continue
               continue;
            }

            // OK, all is good now, try to create a new row, fill it and add to the 
            // Rows collection of the DataTable
            DataRow dr = dt.NewRow();
            dr[0] = pataintno;
            dr[1] = rowParts[1].ToString();
            dr[2] = rowParts[2].ToString();
            dr[3] = age
            dr[4] = rowParts[4].ToString();
            dr[5] = rowParts[5].ToString();
            dr[6] = phno;
            dt.Rows.Add(dr);
        }
    }

The check on your input is done using Int32.TryParse that will return false if the string cannot be converted in an integer. In this case you should write some kind of error log to look at when the loop is completed and discover which lines are incorrect and fix them.

Notice also that I have changed your code in some points: Use File.ReadAllLines so you have already your input splitted at each new line (without problem if the newline is just a \n or a \r\n code), also the code to add a new row to your datatable should follow the pattern: create a new row, fill it with values, add the new row to the existing collection.

Answer 2

I checked the code and it seems fine. I suggest you to check the csv file and make sure there are no headers for any columns.

Answer 3

I had this problem today while parsing csv to sql table. My parser was working good since one year but all of a sudden threw int conversion error today. SQL bulk copy is not that informative, neither reviewing the csv file shows anything wrong in data. All my numeric columns in csv had valid numeric values.

So to find the error, I wrote below custom method. Error immediately popped on very first record. Actual problem was vendor changed the csv format of numeric value and now started rendering decimal values in place of integer. So for example, in place of value 1, csv file had 1.0. When I open the csv file, it reflects only 1 but in notepad, it showed 1.0. My sql table had all integer values and somehow SQL BulkCopy can't handle this transformation. Spent around 3 hours to figure out this error.
Solution inspired from - https://sqlbulkcopy-tutorial.net/type-a-cannot-be-converted-to-type-b

  private void TestData(CsvDataReader dataReader)
    {
        int a = 0;
        while(dataReader.Read())
        {
            try
            {
                a = int.Parse(dataReader[<<Column name>>].ToString());
            }
            catch (Exception ex){}
        }
    }