Working with CSV file

Question

I've been working and trying to solve this problem for maybe a whole week, which at this point I am wondering if I can solve it without it diving even deeper into the C# language, and I'm quite fairly new to C#, as well as working with CSV files and sorting and organizing them, so I'm fairly inexperienced into the whole spectrum of this.

I'm trying to sort a CSV file alphabetically, hide items that need to be hidden and have them have depth levels based on their parents, child and grandchild elements.

I've been successful with a couple of them, and written somewhat working code, but I don't know how to sort them alphabetically and give them the proper depth layer based on the parent and child they belong to.

Here's the mockup CSV that I've been trying to organize:

ID;MenuName;ParentID;isHidden;LinkURL
1;Company;NULL;False;/company
2;About Us;1;False;/company/aboutus
3;Mission;1;False;/company/mission
4;Team;2;False;/company/aboutus/team
5;Client 2;10;False;/references/client2
6;Client 1;10;False;/references/client1
7;Client 4;10;True;/references/client4
8;Client 5;10;True;/references/client5
10;References;NULL;False;/references

I've delimited the items by the semicolon, I've displayed the items that needs to be shown, but I fail to sort them like I should.

The sorting should look like this:

Company
  About Us
    Team
  Mission
References
  Client 1
  Client 2

I've tried to sort them or display them in that order by getting the index of the slash, but what the code reproduces is not how it should be displayed, and, it looks like this:

Company
  About Us
  Mission
    Team
  Client 2
  Client 1
References

In the other try, where I recursively match their parent id with the id, the console display looks like this:

Company
  About Us
  Mission
    Team
        Client 2
        Client 1
References

I've tried solving this with a friend, and, even he doesn't know how to approach this problem, since this code should work on a different file that uses different parent ids.

On top of all this, I am unable to index them to an array, because there's only index of 0 or the index is based on their letters or crashes the console if I enter the index position of 1.

Here's the code for the first part where I fail to sort them:

class Program
{
    static void Main(string[] args)
    {
        StreamReader sr = new StreamReader(@"Navigation.csv");
        string data = sr.ReadLine();

        while (data != null)
        {
            string[] rows = data.Split(';');
            int id;
            int parentId;
            bool ids = Int32.TryParse(rows[0], out id);
            string name = rows[1];
            bool pIds = Int32.TryParse(rows[2], out parentId);
            string isHidden = rows[3];
            string linkUrl = rows[4];
            string[] splitted = linkUrl.Split('/');

            if (isHidden == "False")
            {
                List<CsvParentChild> pIdCid = new List<CsvParentChild>()
                {
                    new CsvParentChild(id, parentId, name, linkUrl)
                };
            }

            data = sr.ReadLine();
        }
    }
}

class CsvParentChild
{

    public int Id;
    public int ParentId;
    public string Name;
    public string LinkUrl;
    public List<CsvParentChild> Children = new List<CsvParentChild>();

    public CsvParentChild(int id, int parentId, string name, string linkUrl)
    {

        Id = id;
        ParentId = parentId;
        Name = name;
        LinkUrl = linkUrl;
        string[] splitted = linkUrl.Split(new char[] { '/' }, StringSplitOptions.RemoveEmptyEntries);

        if (splitted.Length == 1)
        {
            Console.WriteLine($". { name }");
        }
        else if (splitted.Length == 2)
        {
            Console.WriteLine($".... { name }");
        }
        else if (splitted.Length == 3)
        {
            Console.WriteLine($"....... { name }");
        }
    }
}

And here's for the second part:

class Program
{
    static void Main(string[] args)
    {
        // Get the path for the file
        const string filePath = @"../../Navigation.csv";

        // Read the file
        StreamReader sr = new StreamReader(File.OpenRead(filePath));
        string data = sr.ReadLine();

        while (data != null)
        {

            string[] rows = data.Split(';');

            ListItems lis = new ListItems();

            int id;
            int parentId;

            // Get the rows/columns from the Csv file
            bool ids = Int32.TryParse(rows[0], out id);
            string name = rows[1];
            bool parentIds = Int32.TryParse(rows[2], out parentId);
            string isHidden = rows[3];
            string linkUrl = rows[4];

            // Split the linkUrl so that we get the position of the
            // elements based on their slash
            string [] splitted = linkUrl.Split(new char[] { '/' }, StringSplitOptions.RemoveEmptyEntries);

            // If item.isHidden == "False"
            // then display the all items whose state is set to false.
            // If the item.isHidden == "True", then display the item
            // whose state is set to true.
            if (isHidden == "False")
            {
                // Set the items
                ListItems.data = new List<ListItems>()
                {
                    new ListItems() { Id = id, Name = name, ParentId = parentId },
                };

                // Make a new instance of ListItems()
                ListItems listItems = new ListItems();

                // Loop through the CSV data
                for (var i = 0; i < data.Count(); i++)
                {
                    if (splitted.Length == 1)
                    {
                        listItems.ListThroughItems(i, i);
                    }
                    else if (splitted.Length == 2)
                    {
                        listItems.ListThroughItems(i, i);
                    }
                    else
                    {
                        listItems.ListThroughItems(i, i);
                    }
                }
            }

            // Break out of infinite loop
            data = sr.ReadLine();
        }
    }

public class ListItems
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int ParentId { get; set; }
        public static List<ListItems> data = null;
        public List<ListItems> Children = new List<ListItems>();

        // http://stackoverflow.com/a/36250045/7826856
        public void ListThroughItems(int id, int level)
        {
            Id = id;

            // Match the parent id with the id
            List<ListItems> children = data
                .Where(p => p.ParentId == id)
                .ToList();

            foreach (ListItems child in children)
            {
                string depth = new string('.', level * 4);
                Console.WriteLine($".{ depth } { child.Name }");
                ListThroughItems(child.Id, level + 1);
            }
        }
    }
}

Answer 1

My Application was compiled and produced the following output with your data:

Company
        About Us
                Team
        Mission
References
        Client 1
        Client 2
        Client 4
        Client 5

I would attempt to use object relation to create your tree like structure. The main difficulty with the question is that parents don't matter. Children do. So at some point in your code, you will need to reverse the hierarchy; Parsing Children first but reading their Parents first to create the output.

The roots of our tree are the data entries without parents.

Parsing

This should be pretty self explanatory, we have a nice class with a constructor that parses the input array and stores the data in it's properties. We store all the rows in a list. After we are done with this, we pretty much converted the list, but no sorting happened at all.

public partial class csvRow
{
    // Your Data
    public int Id { get; private set; }
    public string MenuName { get; private set; }
    public int? ParentId { get; private set; }
    public bool isHidden { get; private set; }
    public string LinkURL { get; private set; }

    public csvRow(string[] arr)
    {
        Id = Int32.Parse(arr[0]);
        MenuName = arr[1];
        //Parent Id can be null!
        ParentId = ToNullableInt(arr[2]);
        isHidden = bool.Parse(arr[3]);
        LinkURL = arr[4];
    }
    private static int? ToNullableInt(string s)
    {
        int i;
        if (int.TryParse(s, out i))
            return i;
        else
            return null;
    }
}
static void Main(string[] args)
{
    List<csvRow> unsortedRows = new List<csvRow>();
    // Read the file
    const string filePath = @"Navigation.csv";
    StreamReader sr = new StreamReader(File.OpenRead(filePath));
    string data = sr.ReadLine();
    //Read each line
    while (data != null)
    {
        var dataSplit = data.Split(';');   
        //We need to avoid parsing the first line. 
        if (dataSplit[0] != "ID" )
        {
            csvRow lis = new csvRow(dataSplit);
            unsortedRows.Add(lis);
        }
         // Break out of infinite loop
         data = sr.ReadLine();
      }
      sr.Dispose();

       //At this point we got our data in our List<csvRow> unsortedRows
       //It's parsed nicely. But we still need to sort it.
       //So let's get ourselves the root values. Those are the data entries that don't have a parent.
       //Please Note that the main method continues afterwards.

Creating our Tree Strukture and Sorting the items

We start by defining Children and a public ChildrenSorted property that returns them sorted. That's actually allsorting we are doing, it's alot easier to sort than to work recursively.

We also need a function that add's children. It will pretty much filter the input and find all the rows where row.parentId = this.ID.

The last one is the function that defines our output and allows us to get something we can print into the console.

public partial class csvRow
{
    private List<csvRow> children = new List<csvRow>();
    public List<csvRow> ChildrenSorted
    {
        get
        {
            // This is a quite neet way of sorting, isn't it?
            //Btw this is all the sorting we are doing, recursion for win!
            return children.OrderBy(row => row.MenuName).ToList();
        }
    }

    public void addChildrenFrom(List<csvRow> unsortedRows)
    {
        // Add's only rows where this is the parent.
        this.children.AddRange(unsortedRows.Where(
            //Avoid running into null errors
            row => row.ParentId.HasValue &&
            //Find actualy children
            row.ParentId == this.Id &&
            //Avoid adding a child twice. This shouldn't be a problem with your data,
            //but why not be careful?
            !this.children.Any(child => child.Id == row.Id)));  


        //And this is where the magic happens. We are doing this recursively.
        foreach (csvRow child in this.children)
        {
            child.addChildrenFrom(unsortedRows);
        }
    }

    //Depending on your use case this function should be replaced with something
    //that actually makes sense for your business logic, it's an example on
    //how to read from a recursiv structure.
    public List<string> FamilyTree
    {
        get
        {
            List<string> myFamily = new List<string>();
            myFamily.Add(this.MenuName);
            //Merges the Trees with itself as root.
            foreach (csvRow child in this.ChildrenSorted)
            {
                foreach (string familyMember in child.FamilyTree)
                {
                    //Adds a tab for all children, grandchildren etc.
                    myFamily.Add("\t" + familyMember);
                }
            }
            return myFamily;
        }
    }
}

Adding Items to the Tree and accessing them

This is the second part of my main function, where we actually work with our data (Right after sr.Dispose();)

    var roots = unsortedRows.Where(row => row.ParentId.HasValue == false).
        OrderBy(root => root.MenuName).ToList();

    foreach (csvRow root in roots)
    {
        root.addChildrenFrom(unsortedRows);
    }

    foreach (csvRow root in roots)
    {
        foreach (string FamilyMember in root.FamilyTree)
        {
            Console.WriteLine(FamilyMember);
        }
    }
    Console.Read();
}

Entire Sourcecode (Visual Studio C# Console Application)

You can use this to test, play around and learn more about recursive structures.

Copyright 2017 Eldar Kersebaum

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at

 http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

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

namespace ConsoleApplication49
    {
    class Program
    {
        static void Main(string[] args)
        {
            List<csvRow> unsortedRows = new List<csvRow>();
            const string filePath = @"Navigation.csv";
            StreamReader sr = new StreamReader(File.OpenRead(filePath));
            string data = sr.ReadLine();
            while (data != null)
            {
                var dataSplit = data.Split(';');   
                //We need to avoid parsing the first line. 
                if (dataSplit[0] != "ID" )
                {
                    csvRow lis = new csvRow(dataSplit);
                    unsortedRows.Add(lis);
                }
                // Break out of infinite loop
                data = sr.ReadLine();
            }
            sr.Dispose();
            var roots = unsortedRows.Where(row => row.ParentId.HasValue == false).
                OrderBy(root => root.MenuName).ToList();

            foreach (csvRow root in roots)
            {
                root.addChildrenFrom(unsortedRows);
            }

            foreach (csvRow root in roots)
            {
                foreach (string FamilyMember in root.FamilyTree)
                {
                    Console.WriteLine(FamilyMember);
                }
            }
            Console.Read();
        }
    }
    public partial class csvRow
    {
        // Your Data
        public int Id { get; private set; }
        public string MenuName { get; private set; }
        public int? ParentId { get; private set; }
        public bool isHidden { get; private set; }
        public string LinkURL { get; private set; }

        public csvRow(string[] arr)
        {
            Id = Int32.Parse(arr[0]);
            MenuName = arr[1];
            ParentId = ToNullableInt(arr[2]);
            isHidden = bool.Parse(arr[3]);
            LinkURL = arr[4];
        }
        private static int? ToNullableInt(string s)
        {
            int i;
            if (int.TryParse(s, out i))
                return i;
            else
                return null;
        }
        private List<csvRow> children = new List<csvRow>();
        public List<csvRow> ChildrenSorted
        {
            get
            {
                return children.OrderBy(row => row.MenuName).ToList();
            }
        }
        public void addChildrenFrom(List<csvRow> unsortedRows)
        {
            this.children.AddRange(unsortedRows.Where(
                row => row.ParentId.HasValue &&
                row.ParentId == this.Id &&
                !this.children.Any(child => child.Id == row.Id)));
            foreach (csvRow child in this.children)
            {
                child.addChildrenFrom(unsortedRows);
            }
        }
        public List<string> FamilyTree
        {
            get
            {
                List<string> myFamily = new List<string>();
                myFamily.Add(this.MenuName);
                foreach (csvRow child in this.ChildrenSorted)
                {
                    foreach (string familyMember in child.FamilyTree)
                    {
                        myFamily.Add("\t" + familyMember);
                    }
                }
                return myFamily;
            }
        }
    }
}

Answer 2

For each item, you need to construct a kind of "sort array" consisting of ids. The sort array consists of the ids of the item's ancestors in order from most distant to least distant. For "Team", our sort array is [1, 2, 4].

Here are the sort arrays of each item:

[1]
[1, 2]
[1, 3]
[1, 2, 4]
[10, 5]
[10, 6]
[10, 7]
[10, 8]
[10]

Once you have this, sorting the items is simple. When comparing two "sort arrays", start with the numbers in order in each array. If they are different, sort according to the value of the first number and you're done. If they are the same, look at the second number. If there is no second number, then sort by the length of the arrays, i.e., nothing comes before something.

Applying this algorithm, we get:

[1]
[1, 2]
[1, 2, 4]
[1, 3]
[10]
[10, 5]
[10, 6]
[10, 7]
[10, 8]

After that, hide the items based on the flag. I leave that to you because it's so simple. Depth is easy: It's the length of the sort array.