Loading data from 11 column CSV file into separate collections

Question

First off i'm a bit of a novice at C#

So I have a CSV file with 11 columns and over 20,000 rows, its just a bunch of data from a sports tracking gps. What I want to do is be able to take that data from the CSV and load each column into a seperate collection, however I can't get it to work the way I want it.

I've spent ages searching up how to do this properly, but all i've managed to muster together this janky code that does load all the data into the collections, but will only let me load each piece of data into the collection as a string (not as decimal or char, which I need for some [yes I've tried delcaring the collections as decimal or char before]).

So what I need help with is being able to actually load the data from the CSV file into the collection as the data type I want, and if there's an easy way to skip the first 8 or so lines which are just headers.

The list of data types I require are as follows (in order of declared) decimal decimal char decimal char decimal string string decimal decimal string

Here is the code i'm currently using:

  //Seprate class for all the collection declarations
  public static class GPSdata
    {
        public static List<string> time = new List<string>();     //time (in seconds, advances by 0.2)
        public static List<string> lat = new List<string>();      //Latitude
        public static List<string> NS = new List<string>();       //North/South
        public static List<string> lon = new List<string>();      //Longtitude
        public static List<string> EW = new List<string>();       //East/West
        public static List<string> knots = new List<string>();    //Speed in Knots
        public static List<string> date = new List<string>();     //Date [ddmmyy]
        public static List<string> sats = new List<string>();     //**No clue**
        public static List<string> HDOP = new List<string>();     //Satelite Horizontal error
        public static List<string> alt = new List<string>();      //Elevation (above msl)
        public static List<string> rawSV = new List<string>();    //Space Vehicle
    }

 //Method for loading the CSV data into the collections
 public void LoadCSV(string filepath)
    {
        using (StreamReader reader = new StreamReader(filepath))
        {
           while (!reader.EndOfStream)
           {
              var line = reader.ReadLine();
              var values = line.Split(',');

              GPSdata.time.Add(values[0]);
              GPSdata.lat.Add(values[1]);
              GPSdata.NS.Add(values[2]);
              GPSdata.lon.Add(values[3]);
              GPSdata.EW.Add(values[4]);
              GPSdata.knots.Add(values[5]);
              GPSdata.date.Add(values[6]);
              GPSdata.sats.Add(values[7]);
              GPSdata.HDOP.Add(values[8]);
              GPSdata.rawSV.Add(values[9]);
              GPSdata.alt.Add(values[10]);
           }
        }
     }

Also heres an example of the data from the file i'm reading off: 31350.2,3750.9188,S,14458.8652,E,7.98,50817,0,2.3,0,23 31350.4,3750.9204,S,14458.867,E,6.66,50817,0,2.3,0,23

Why would you want them in separate collections? Each line seems to contain data that should be collected together in one class object. To look up the specific property of a specific line, you just take the full object of that line and then select the right property from the object. You can still do lookups in collections of such objects using Linq. — Nyerguds, Mar 24 '18 at 12:04
As for your actual question, what you're _really_ asking is unrelated to csv, but seems to simply be **"how do I convert a string to a floating point number"**. Which is something you can definitely find answers to simply by looking around online a bit. — Nyerguds, Mar 24 '18 at 12:08
Nice library I used in one of my projects - CsvHelper (http://joshclose.github.io/CsvHelper/) - it provides good out of the box mapping. — Vidmantas Blazevicius, Mar 24 '18 at 12:12
Well, [I wrote some code here on another question](https://stackoverflow.com/a/48815454/395685) that does full CSV parsing and validation from header-identified columns, too. — Nyerguds, Mar 24 '18 at 12:14
Matt, best thing you can do is process your CSV and create a List as an intermediate result. Define GPSData as a class or a struct. It's not so difficult to do queries on List using Linq — JRB, Mar 24 '18 at 12:42
@JRB yea that does make sense, but I really wouldn't know how to go about doing that properly, could you please point me in the right direction? — Matt Wilson, Mar 24 '18 at 12:47
@Matt, when processing CSV-files the devil is in the details. Lots can go wrong. You should therefore use a very modular approach. Two pointers. you are using the StreamReader, in stead of that I would use IEnumerable gpsDataLines = File.ReadLines(csvFileName); lstGPSData = convertCSVToLstGPSData(gpsDataLines, startLine); Second pointer when processing the split parts in a read line use this line for ints : star.StarId = parts[1] == String.Empty ? 0 : Convert.ToInt32(parts[1]). In a similar wat you can also convert to decimals and doubles; — JRB, Mar 24 '18 at 13:43

jdweng · Answer 1 · 2018-03-25T08:12:01.860

0

You class should look something like this. The E/W is positive or negative longitude and S/N is positive or negative latitude.

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

namespace ConsoleApplication1
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.csv";
        static void Main(string[] args)
        {
            new GPSdata(FILENAME);
        }
    }
    //Seprate class for all the collection declarations
    public class GPSdata
    {
        public static List<GPSdata> data = new List<GPSdata>();

        public TimeSpan time { get; set; }     //time (in seconds, advances by 0.2)
        public int latDegrees { get; set; }      //Latitude
        public int latMinutes { get; set; }      //Latitude
        public int latSeconds { get; set; }      //Latitude
        public string NS { get; set; }       //North/South
        public int lonDegrees { get; set; }      //Longtitude
        public int lonMinutes { get; set; }      //Longtitude
        public int lonSeconds { get; set; }      //Longtitude
        public string EW { get; set; }       //East/West
        public decimal knots { get; set; }    //Speed in Knots
        public DateTime date { get; set; }     //Date [ddmmyy]
        public int sats { get; set; }     //**No clue**
        public decimal HDOP { get; set; }     //Satelite Horizontal error
        public decimal alt { get; set; }      //Elevation (above msl)
        public int rawSV { get; set; }    //Space Vehicle

        public GPSdata() { }
        public GPSdata(string filepath)
        {
            int lineNumber = 0;

            StreamReader reader = new StreamReader(filepath);

            string line = "";
            while ((line = reader.ReadLine()) != null)
            {
                if (++lineNumber > 8)
                {
                    try
                    {
                        string[] values = line.Split(',');

                        GPSdata gpsdata = new GPSdata();
                        GPSdata.data.Add(gpsdata);

                        gpsdata.time = new TimeSpan((long)(decimal.Parse(values[0]) * (decimal)1.0E07));
                        int latDecimalPoint = values[1].IndexOf(".");
                        gpsdata.latSeconds = int.Parse(values[1].Substring(latDecimalPoint + 1));
                        gpsdata.latMinutes = int.Parse(values[1].Substring(latDecimalPoint - 2, 2));
                        gpsdata.latDegrees = int.Parse(values[1].Substring(0, latDecimalPoint - 2));
                        gpsdata.NS = values[2];
                        int lonDecimalPoint = values[3].IndexOf(".");
                        gpsdata.lonSeconds = int.Parse(values[3].Substring(lonDecimalPoint + 1));
                        gpsdata.lonMinutes = int.Parse(values[3].Substring(lonDecimalPoint - 2, 2));
                        gpsdata.lonDegrees = int.Parse(values[3].Substring(0, lonDecimalPoint - 2));
                        gpsdata.EW = values[4];
                        gpsdata.knots = decimal.Parse(values[5]);
                        int dateLen = values[6].Length;
                        gpsdata.date = new DateTime(int.Parse(values[6].Substring(dateLen - 2)), int.Parse(values[6].Substring(0, dateLen - 4)), int.Parse(values[6].Substring(dateLen - 4, 2)));
                        gpsdata.sats = int.Parse(values[7]);
                        gpsdata.HDOP = decimal.Parse(values[8]);
                        gpsdata.rawSV = int.Parse(values[9]);
                        gpsdata.alt = decimal.Parse(values[10]);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Error Line Number : '{0}',  Text : '{1}'", lineNumber,line);
                    }
                }
            }
            Console.ReadLine();

        }

    }
}

edited Mar 25 '18 at 08:12

answered Mar 24 '18 at 12:15

jdweng

33,250
2
15
20

That... doesn't help at all. It gives no clues on the actual conversion question. Also, time in seconds would be a plain integer, not a `DateTime`. – Nyerguds Mar 24 '18 at 12:16
I'm just giving a start, not the entire answer. – jdweng Mar 24 '18 at 12:17
Time in seconds increasing by 0.2 may not be correct. It is probably the 50Hz Nav data which should be 0.02. The sats is the status which is the indicator that the satellite is working correctly or offline. – jdweng Mar 24 '18 at 12:28
@jdweng Ok, I can see how that would make more sense, but I'm really not properly aquainted to this { get; set; } thing yet, and i'm not sure on accessing objects inside collections. Could you quickly explain or point me in the direction of a good crash course? – Matt Wilson Mar 24 '18 at 12:39
I created a new object 'data' which will contain all the row data. each row of the csv data. So when you read one row you create a new instance of the class : GPSdata newRow = new GPSdata(); Then add row to list : GPSdata.data.Add(newRow). Then add lat : newRow.lat = -123.456; – jdweng Mar 24 '18 at 12:50
@MattWilson if you're a novice at C#, what language(s) _are_ you familiar with? Because the `{get;set;}` is just C# shorthand for the classic public getter and setter of an private variable also used in Java. Only, in C# it's exposed as one property instead of 2 functions. – Nyerguds Mar 24 '18 at 13:02
@Nyerguds Really all i'm familiar with is VB .NET, and I havent used getters and setters in that either, so getter and setters are all new territory to me. But at least i'm being pointed in the right direction here :) – Matt Wilson Mar 24 '18 at 13:15
@MattWilson Oh. All of this stuff should be virtually identical in VB though. It seems you just need to read up on object-oriented programming in general. My point above still stands, though... the CSV parsing is just tangentially related to your real problem, which is _number_ parsing. – Nyerguds Mar 24 '18 at 13:17
I combined you code with my class to get a complete answer. – jdweng Mar 24 '18 at 18:12
The class should not contain a collection of itself – paparazzo Mar 24 '18 at 18:59
pararazzo : Where did you ever get that rule? Doesn't make any sense. – jdweng Mar 24 '18 at 20:49
@jdweng as with the below code, it just breaks when adding values to objects, says input string isnt in correct format – Matt Wilson Mar 25 '18 at 00:09
@jdweng Clearly it does not make sense to you. – paparazzo Mar 25 '18 at 06:58
I tested the code with the same csv you provided and code works. If it is failing let me know which row fails and I will modify code. Very often with text input minor changes are needed. I assumed the date 50817 is month, day, year but it could be day, month, year which would give an error. You have 20,000 rows of data and provided only 2 rows in your sample. It is very hard to get it perfect the first attempt. But I'm very close. – jdweng Mar 25 '18 at 07:58
paparazzo : The proper place to put data for a class is in the class. Not some place else. – jdweng Mar 25 '18 at 08:01
I updated code so it will not read entire csv file and display any lines with error. Also skips first 8 lines. – jdweng Mar 25 '18 at 08:12
I don't know about you but I park my car in a garage not a car. How would you know which car as the cars? Why does the first car even need to be in the cars. So the first car is in itself? Why does every car in the list need a list of cars? – paparazzo Mar 25 '18 at 10:19
I'm not saying you are wrong. But if you have a large project and multiple classes need access to the List, making it static will simplify the access to the data. – jdweng Mar 25 '18 at 11:40

score 0 · Answer 2 · answered Mar 24 '18 at 13:49

Sounds like you are asking two questions here, parsing text into other datatypes, which is discussed in the other answer. here another question that describes that in better detail. String Parsing in C#. The second part you are asking is about skipping header information in you csv files. Use the ReadLine() method on the StreamReader to skip some lines like so:

using (StreamReader reader = new StreamReader(filepath))
{
    for(int i = 0; i<8; ++i){
        reader.ReadLine();
    }
    while (!reader.EndOfStream)
    {
    // the stuff you are already doing
    }
}

Actually, .Net [has a reader specifically for CSV data](https://stackoverflow.com/a/48809517/395685) in `Microsoft.VisualBasic.FileIO`, called `TextFieldParser`. Technically, fields in the CSV format _can contain line breaks_, making line-per-line reading a bad way to go. — Nyerguds, Mar 24 '18 at 13:58

paparazzo · Answer 3 · 2018-03-24T18:58:31.930

Separate collection for each property is not the proper approach.
This may be what you are looking for:

public class GPSdata
{
    public TimeSpan time { get; set; }     //time (in seconds, advances by 0.2)
    public int latDegrees { get; set; }      //Latitude
    public int latMinutes { get; set; }      //Latitude
    public int latSeconds { get; set; }      //Latitude
    public string NS { get; set; }       //North/South
    public int lonDegrees { get; set; }      //Longtitude
    public int lonMinutes { get; set; }      //Longtitude
    public int lonSeconds { get; set; }      //Longtitude
    public string EW { get; set; }       //East/West
    public decimal knots { get; set; }    //Speed in Knots
    public DateTime date { get; set; }     //Date [ddmmyy]
    public int sats { get; set; }     //**No clue**
    public decimal HDOP { get; set; }     //Satelite Horizontal error
    public decimal alt { get; set; }      //Elevation (above msl)
    public int rawSV { get; set; }    //Space Vehicle
}
public static List<GPSdata> LoadCSV(string filepath)
{
    List<GPSdata> data = new List<GPSdata>();
    using (StreamReader reader = new StreamReader(filepath))
    {
        while (!reader.EndOfStream)
        {
            string line = reader.ReadLine();
            string[] values = line.Split(',');

            GPSdata gpsdata = new GPSdata();                    

            gpsdata.time = new TimeSpan((long)(decimal.Parse(values[0]) * (decimal)1.0E07));
            int latDecimalPoint = values[1].IndexOf(".");
            gpsdata.latSeconds = int.Parse(values[1].Substring(latDecimalPoint + 1));
            gpsdata.latMinutes = int.Parse(values[1].Substring(latDecimalPoint - 2, 2));
            gpsdata.latDegrees = int.Parse(values[1].Substring(0, latDecimalPoint - 2));
            gpsdata.NS = values[2];
            int lonDecimalPoint = values[3].IndexOf(".");
            gpsdata.lonSeconds = int.Parse(values[3].Substring(lonDecimalPoint + 1));
            gpsdata.lonMinutes = int.Parse(values[3].Substring(lonDecimalPoint - 2, 2));
            gpsdata.lonDegrees = int.Parse(values[3].Substring(0, lonDecimalPoint - 2));
            gpsdata.EW = values[4];
            gpsdata.knots = decimal.Parse(values[5]);
            int dateLen = values[6].Length;
            gpsdata.date = new DateTime(int.Parse(values[6].Substring(dateLen - 2)), int.Parse(values[6].Substring(0, dateLen - 4)), int.Parse(values[6].Substring(dateLen - 4, 2)));
            gpsdata.sats = int.Parse(values[7]);
            gpsdata.HDOP = decimal.Parse(values[8]);
            gpsdata.rawSV = int.Parse(values[9]);
            gpsdata.alt = decimal.Parse(values[10]);

            data.Add(gpsdata);
        }
    }
    return data;
}

looks good, but just breaks when trying to add values into objects, saying input string was in incorrect format. — Matt Wilson, Mar 25 '18 at 00:07

Loading data from 11 column CSV file into separate collections

3 Answers3