I'm trying to pass DataTable to Class and I get this message: Input string was not in a correct format

Question

I am taking an excel file to read the fields and pass it to a class, to be able to enter the DB, when I pass dt.AsEnumerable, it is giving me an error in the "Linea" field. This cell sometimes comes with the $ sign. I think that is what is generating the error, so i am trying to replace the character and then convert it to int, since it is an amount field.

using (var streamExcel = System.IO.File.Create(combineFilePath))
                {
                   await file.CopyToAsync(streamExcel);
                }
                using (var excelWorkbook = new XLWorkbook(combineFilePath))
                {
                   IXLWorksheet workSheet = excelWorkbook.Worksheet(2);

                   workSheet.Clear(XLClearOptions.AllFormats);
                   
                    DataTable dt = new DataTable();

                   bool firstRow = true;
                   foreach (IXLRow row in workSheet.Rows())
                   {
                       //for row number check
                       if (firstRow)
                       {
                           foreach (IXLCell cell in row.Cells())
                           {
                               dt.Columns.Add(cell.Value.ToString());
                           }
                           firstRow = false;
                       }
                       else
                       {
                           //Add rows to DataTable.
                           dt.Rows.Add();
                           int i = 0;
                           foreach (IXLCell cell in row.Cells(1, 50))
                           {
                                if (cell.Address.ColumnNumber == 11)
                                {

                                    workSheet.Cell(11, 11).Style.NumberFormat.Format = "#,##0";
                                    cell.SetDataType(XLDataType.Number);
                                }

                                dt.Rows[dt.Rows.Count - 1][i] = cell.CachedValue.ToString();

                               i++;
                           }
                       }
                   }
                   try
                   {
                        
                        var companys = dt.AsEnumerable().Select(row => new Company
                        {


                            Name = row.Field<string>("Nombre"),
                            Rut = row.Field<string>("Rut"),
                            Address = row.Field<string>("Dirección"),
                            AddressNumber = row.Field<string>(@"Nº"),
                            Location = row.Field<string>("Comuna"),
                            Region = row.Field<string>("Región"),
                            Giro = row.Field<string>("Giro Cliente"),
                            Linea = Convert.ToInt32(row.Field<string>("Monto línea Aprobada").Trim().TrimEnd().Replace(@"$", "")),
                            Observations = row.Field<string>("Observaciones Comité"),


                       }).ToList();

                       UserId = _companyService.AddList(companys);
                   }
                   catch (Exception e)
                   {

                   }

Are you 100% certain that `row.Field("Monto línea Aprobada").Trim().TrimEnd().Replace(@"$", "")` is an integer? Could it have a decimal point? — D Stanley, Jan 31 '22 at 20:57
please create a breakpoint, and inspect the value of `row.Field("Monto línea Aprobada").Trim().TrimEnd().Replace(@"$", "")` when it is throwing the error, and put it here. — Jonathan, Jan 31 '22 at 20:58
@DStanley No amount within the file that I am working with comes with a decimal in that cell — Eduardo C, Jan 31 '22 at 21:14
@Jonathan that is the error that gives me the exception: {"Input string was not in a correct format."} — Eduardo C, Jan 31 '22 at 21:18
Well the error indicates that the string could not be parsed into an integer, which means it either has non-numeric characters or is empty (a blank string or null). Since you're doing a Linq query I would create a function to wrap the parsing and call that function from your `Select`. It's near impossible to debug the actual projection of a linq query. — D Stanley, Jan 31 '22 at 21:18
@DStanley In fact, in some cells they are empty, I am not sure how to skip those cells. — Eduardo C, Jan 31 '22 at 21:23
Well you could add `.Where(row => string.IsNullOrEmpty(row.Field("Monto línea Aprobada"))` before the select. Personally, I would create an `IsValidRow` function that checks for these types of potential problems and call that from `Where`. Then I would create a `ConvertRow` function that does the mapping from columns to fields, parses strings, etc. and call that function from your `Select`. It will make debugging a _lot_ easier. — D Stanley, Jan 31 '22 at 22:16
Hi Eduardo. It's pretty straightforward: you are bringing in values and trying to convert them to `int`, but one or more of them cannot be converted to `int`. They are null or have a text character in there, or are decimals, etc. To solve this, we (or you) have to see the values that you are trying to convert, then all will be clear. Just stick all of the incoming data into another list or something, and write it out. Or (like I say above) put in some breakpoints and inspect the data that is failing. — Jonathan, Jan 31 '22 at 23:29

score 0 · Answer 1 · answered Jan 31 '22 at 23:34

To visualize where it's failing, you could do something like this:

try{
                        
      var companysB = dt.AsEnumerable().Select(row => new 
      {
        Name = row.Field<string>("Nombre"),
        LineaRaw = row.Field<string>("Monto línea Aprobada"),
        LineaProcessed = row.Field<string>("Monto línea Aprobada").Trim().TrimEnd().Replace(@"$", ""),
      })
      .ToList();
    }

Put a breakpoint on 'companysB' and inspect after it's filled. One or more of the LineaRaw / LineaProcessed will not be a number.

score 0 · Answer 2 · edited Feb 01 '22 at 21:24

The error was when the "Monto línea Aprobada" cell had a blank value, I did this:

 var n = 0;
 string cellEmty = "";
 foreach (DataRow rowEmpty in dt.Rows)
 {
      cellEmty = rowEmpty["Monto línea Aprobada"].ToString();
      if (string.IsNullOrEmpty(cellEmty))
      {
           cellEmty = "0";
           dt.Rows[n].SetField("Monto línea Aprobada", cellEmty);
      }
                               
      n++;
  }

I'm trying to pass DataTable to Class and I get this message: Input string was not in a correct format

2 Answers2