2

I am trying to store a CSV file containing the following data to a list:

CSV_Data.png

I have the following code which successfully saves the CSV to App_Data folder:

public static IEnumerable<Product> CSVProducts;

        public static IEnumerable<Product> CSVToList(HttpPostedFileBase CSVFile)
        {

            if (CSVFile == null || CSVFile.ContentLength == 0)
            {
                throw new InvalidOperationException("No file has been selected for upload or the file is empty.");
            }

            // saves the file into a directory in the App_Data folder
            var fileName = Path.GetFileName(CSVFile.FileName);
            var path = Path.Combine(HttpContext.Current.Server.MapPath("~/App_Data/"), fileName);
            CSVFile.SaveAs(path);


            CSVProducts = from line in File.ReadAllLines(path).Skip(1)
                          let columns = line.Split(',')
                          select new Product
                          {
                              Id = int.Parse(columns[0]),
                              Barcode = columns[13],
                              Name = columns[1],
                              CategoryName = columns[9],
                              Description = columns[2],
                              Price = int.Parse(columns[4])
                          };

            return CSVProducts;
        }

However, the LINQ query to get a list gives the following error message:

Input string was not in a correct format.

Source Error: 


Line 31:             CSVProducts = from line in File.ReadAllLines(path).Skip(1)
Line 32:                           let columns = line.Split(',')
Line 33:                           select new Product
Line 34:                           {
Line 35:                               Id = int.Parse(columns[0]),

Source File: C:\Users\Documents\Visual Studio 2015\Projects\Website\Models\Repository.cs

Line: 33 

When I debug in Visual Studio, I can't see what's inside the variables inside the LINQ query, but I can see what is inside CSVProducts and the 'Current' property for it has a value of null.

This is the class for Product and Category:

    public class Product
    {
        public int Id { get; set; }
        public string Barcode { get; set; }
        [Required]
        public string Name { get; set; }
        public string Description { get; set; }
        [Required]
        public Byte[] Image { get; set; }
        [Required]
        public Decimal Price { get; set; }
        [Required]
        public bool ShowOnIndex { get; set; }
    }

    public class Category
    {
        public int Id { get; set; }
        [Required]
        public string Name { get; set; }
        [Required]
        public Byte[] Image { get; set; }
        public virtual ICollection<Product> Products { get; set; }
    }
9
  • 1
    And what's the value of columns[0] when the error happens? Commented Feb 17, 2017 at 0:08
  • 1
    Make sure there's no whitespace in any values in column 0. Commented Feb 17, 2017 at 0:14
  • 1
    Then you likely have an empty cell somewhere. You can put filters on your file in Excel to help find the culprit. Commented Feb 17, 2017 at 0:20
  • 1
    Your fourth column, Price, is written as decimal, '1.19', and you're parsing it as integer, Price = int.Parse(columns[4]), parse it as a double or float. Also, ensure your data has no comma in it, if it has a comma your split will break, splitting a CSV file is not a matter of splitting just by the sepparator as the data can be quoted and escaped. Commented Feb 17, 2017 at 0:46
  • 1
    ...using a tool like CSVHelper which has already encountered this and many, many other csv related issues, you'd be done by now. Commented Feb 17, 2017 at 1:05

1 Answer 1

1

Thank you guys in the comment section.

The reason why the LINQ query gave that error was because the data-type for the Price property in the Product class was Decimal. I initially was Parsing an Int inside the query for the Price value: Price = int.Parse(columns[4]).

I changed it to Price = decimal.Parse(columns[4]) and now the LINQ query successfully parses through the Excel CSV file and stores each record in an IEnumerable list of Products. Thanks again guys. Love you all. :)

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.