Home:ALL Converter>C# : Imported CSV File, Input string was not in a correct format

C# : Imported CSV File, Input string was not in a correct format

Ask Time:2018-01-25T12:29:24         Author:Matthew Roy Beach

Json Formatter

This code is an attempt to take two columns of numerical values from a CSV file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.IO;

using System.Text.RegularExpressions;
using System.Globalization;

namespace PredominantValues
{
    class Program
    {
        static void Main(string[] args)
        {

            List<DataValues> values = File.ReadAllLines("C:\\Users\\yetih\\Desktop\\Firm_Inventory.csv")
                                           .Skip(1)
                                           .Select(v => DataValues.FromCSV(v))
                                           .ToList();

            int YEARHIGH    = 2050;
            int YEARLOW     = 1700;
            int PRICEHIGH   = 900000;
            int PRICELOW    = 5000;

            int maxYearBuilt = DataValues.GetMaxYearBuilt(values, YEARHIGH);
            int minYearBuilt = DataValues.GetMinYearBuilt(values, YEARLOW);
            int avgYearBuilt = Convert.ToInt32(values.Average(r => r.yearBuilt));

            int maxPrice = DataValues.GetMaxPrice(values, PRICEHIGH);
            int minPrice = DataValues.GetMinPrice(values, PRICELOW);
            int avgPrice = Convert.ToInt32(values.Average(r => r.price));

            Console.WriteLine("Low      - Year Built: " + maxYearBuilt);
            Console.WriteLine("High     - Year Built: " + minYearBuilt);
            Console.WriteLine("Average  - Year Built: " + avgYearBuilt);
            Console.WriteLine("High     - Price:      " + maxPrice);
            Console.WriteLine("Low      - Price:      " + minPrice);
            Console.WriteLine("Average  - Price:      " + avgPrice);

            Console.ReadLine();
        }

    }

    public class DataValues
    {
        public int yearBuilt;
        public int price;

        public static DataValues FromCSV(string csvLine)
        {
            Regex CSVParser = new Regex(",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");
            string[] values = CSVParser.Split(csvLine);
            DataValues dataValues = new DataValues();

            values[0] = values[0].Replace("," , "");

            Console.WriteLine(values[0]);
            Console.ReadLine();

            decimal test = decimal.Parse(values[0], NumberStyles.AllowCurrencySymbol);

            Console.WriteLine(test);
            Console.WriteLine(values[1]);
            Console.ReadLine();

            dataValues.price = int.Parse(values[0], NumberStyles.Currency);
            dataValues.yearBuilt = Convert.ToInt32(values[1]);
            return dataValues;
        }

        public static int GetMaxYearBuilt(List<DataValues> list, int YEARHIGH)
        {
            int maxYearBuilt;
            int index;

            do
            {
                maxYearBuilt = list.Max(r => r.yearBuilt);
                if (maxYearBuilt >= YEARHIGH)
                {
                    index = list.FindIndex(a => a.yearBuilt == maxYearBuilt);
                    list.RemoveAt(index);
                }
            } while (maxYearBuilt >= YEARHIGH);

            return maxYearBuilt;
        }

        public static int GetMinYearBuilt(List<DataValues> list, int YEARLOW)
        {
            int minYearBuilt;
            int index;

            do
            {
                minYearBuilt = list.Min(r => r.yearBuilt);
                if (minYearBuilt <= YEARLOW)
                {
                    index = list.FindIndex(a => a.yearBuilt == minYearBuilt);
                    list.RemoveAt(index);
                }
            } while (minYearBuilt <= YEARLOW);

            return minYearBuilt;
        }

        public static int GetMaxPrice(List<DataValues> list, int PRICEHIGH)
        {
            int maxPrice;
            int index;

            do
            {
                maxPrice = list.Max(r => r.price);
                if (maxPrice >= PRICEHIGH)
                {
                    index = list.FindIndex(a => a.price == maxPrice);
                    list.RemoveAt(index);
                }
            } while (maxPrice >= PRICEHIGH);

            return maxPrice;
        }

        public static int GetMinPrice(List<DataValues> list, int PRICELOW)
        {
            int minPrice;
            int index;

            do
            {
                minPrice = list.Min(r => r.price);
                if (minPrice <= PRICELOW)
                {
                    index = list.FindIndex(a => a.price == minPrice);
                    list.RemoveAt(index);
                }
            } while (minPrice <= PRICELOW);

            return minPrice;
        }
    }
}

So far I am able to successfully import and split the file into a list only if I go into the CSV file and change the formatting on the first column from 'Currency' to 'General'. The two columns are 'Price' and 'Year Built'. If I keep the 'Currency' formatting, I get an exception: "Input string was not in a correct format".

I've tried parsing the string to remove the currency symbol, however I am met with the same result.

dataValues.price = int.Parse(values[0], NumberStyles.Currency);

I would like to store the numerical value in 'values[0]' in 'dataValues.price'

Author:Matthew Roy Beach,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/48435702/c-sharp-imported-csv-file-input-string-was-not-in-a-correct-format
yy