Export data from nested c# classes to csv file

Question

I have the following classes to work with and I want to export/flatten the contents to a csv file. I have tried to use SelectMany but I can’t find a way of selecting fields for my csv file from both Company level and the underlying level with SalesArea, Node and EthernetArea. Can someone explain what I need to do to accomplish this?

public class Rootobject
{
    public Company[] Companies { get; set; }
}
public class Company
{
    public string ORG_NAME { get; set; }
    public SalesArea[] Salesareas { get; set; }
    public Node[] Nodes { get; set; }
    public EthernetArea[] Ethernetareas { get; set; }
}
public class SalesArea
{
    public string OBJ_NAME { get; set; }
    public string AREA_CTYPE { get; set; }
}
public class Node
{
    public string OBJ_NAME { get; set; }
    public object BUILDING { get; set; }
}
public class EthernetArea
{
    public string OBJ_NAME { get; set; }
    public string AREA_CTYPE { get; set; }
    public Product[] Products { get; set; }
}
public class Product
{
    public string BANDWIDTH_A { get; set; }
    public string CONNECTION_TYPE { get; set; }
}

Do you really, really need to use CSV? Can't you use JSON or XML instead? — Rui Jarimba
– Rui Jarimba, Commented Aug 3, 2018 at 13:23
You 1st need to determine how your CSV will look like with different sets and lengths of data. — Andrei Tătar
– Andrei Tătar, Commented Aug 3, 2018 at 13:25
Check FileHelpers, it's an old library but it might be useful to you: filehelpers.net/example/MasterDetail/MasterDetailCustomSelector — Rui Jarimba
– Rui Jarimba, Commented Aug 3, 2018 at 13:28
You need a class that represent the flattern data like you want it in the CSV file. and then stackoverflow.com/questions/6428940/…. — Drag and Drop
– Drag and Drop, Commented Aug 3, 2018 at 13:28

Drag and Drop · Accepted Answer · 2018-08-07 09:33:39Z

1

Define a class that will be the representation of what you want in your CSV. As multiple object properties have the same name in your model.
In your comment you choosed to prefix your properties with the class name so you should have something like :

public class CompanyCSV
{
    public string Company_ORG_NAME { get; set; }

    public string SalesArea_OBJ_NAME { get; set; }
    public string SalesArea_AREA_CTYPE { get; set; }

    public string Node_OBJ_NAME { get; set; }
    public string Node_BUILDING { get; set; }

    public string EthernetArea_OBJ_NAME { get; set; }
    public string EthernetArea_AREA_CTYPE { get; set; }

    public string Product_BANDWIDTH_A { get; set; }
    public string Product_CONNECTION_TYPE { get; set; }
}

Then simply:

var result = from c in dataInput.Companies
                from s in c.Salesareas
                from n in c.Nodes
                from e in c.Ethernetareas
                from p in e.Products
                select new CompanyCSV
                {
                    Company_ORG_NAME = c.ORG_NAME,

                    SalesArea_OBJ_NAME = s.OBJ_NAME,
                    SalesArea_AREA_CTYPE = s.AREA_CTYPE,

                    Node_OBJ_NAME = n.OBJ_NAME,
                    Node_BUILDING = n.BUILDING.ToString(),

                    EthernetArea_OBJ_NAME = e.OBJ_NAME,
                    EthernetArea_AREA_CTYPE = e.AREA_CTYPE,

                    Product_BANDWIDTH_A = p.BANDWIDTH_A,
                    Product_CONNECTION_TYPE = p.CONNECTION_TYPE
                };


foreach (var line in result)
{ // Don't use this to generate your csv this is simply to show the result
    Console.WriteLine($"{line.c},{line.s},{line.n},{line.e},{line.p}, ..etc ..");
}

To write your CSV you can use CsvHelper. This will help you handlevalue with comma or quote. Do not generate your CSV by hand.

If your collection (Node[], Company[], etc.) can be null you should guard this edge case using a null-coalescing operator in the getter, like :

private Node[] nodes;
public Node[] Nodes {
    get { return nodes ?? (nodes = new Node[] { }); }
    set { nodes = value; }
}

edited Aug 7, 2018 at 9:33

answered Aug 3, 2018 at 14:02

Drag and Drop

2,7463 gold badges28 silver badges40 bronze badges

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

7 Comments

Christer Over a year ago

How do I define the class you are suggsting? I tried with this but it didn't work: public class dataInput { public string Companies { get; set; } public string Salesareas { get; set; } public string Nodes { get; set; } public string Ethernetareas { get; set; } public string Products { get; set; } }

Drag and Drop Over a year ago

Like any other class: public class MyCSV{ public type propertyName; ... with understandable name etc. Then you modify the select part of the query to select new MyCSV{ propertyName = value,....

Drag and Drop Over a year ago

It's a confusing edit you have here: public string Companies? Companies is an array of Company, that have one field ORG_NAME. And 3 arrays Salesareas, Nodes, Ethernetareas. This is a lot of information in a single string. Imagine that every column of your CSV is an property. no amtter where this property is in the complexe object. Write down every csv column you have, and nex to it write where it is in the object. The 1rst part the list of column will be your output object definition

Christer Over a year ago

Thanks a lot for your advices, I can now produce the output for Company and Salesarea. The problem I have now is selecting the Nodes. In my input data they can be NULL but they can also consist of several Node objects. My current declaration in my class MyCSV for the field OBJ_NAME (from Nodes) is: public string Nodes_OBJ_NAME { get; set; } I guess that this declaration that causes the problem? How should I declare this field it so it matches the corresponding field in the class Node?

Drag and Drop Over a year ago

If for exemple, Nodes= null. You have an issue, 'NullReferenceException'. Why node is not an empty collection? Imagine someone that may or may not have kids, if you ask him how many kids he has, do he has to : Answer 1/. Have a stroke and die. 2/. Count them and answer 0. If the simple evocation of the word kid must kill him then null is fine.

|

jjchiw · Accepted Answer · 2018-08-07 10:38:00Z

I'm sure there should be another way to do this, but anyways here it's how I did it...

If I understand correctly you want to 'Denormalize' the data and export the collection to a CSV.

I used many Select SelectMany and Join

var root = new Rootobject
    {
        Companies = new Company[] {
            new Company
        {
            ORG_NAME = "Co",
            Salesareas = new SalesArea[]{
            new SalesArea {
                OBJ_NAME = "Sa",
                AREA_CTYPE = "SaAr",
            }
        },
            Nodes = new Node[] {
            new Node {
                OBJ_NAME = "No",
                BUILDING = "NoBu"
            }
        },
            Ethernetareas = new EthernetArea[] {
            new EthernetArea {
                OBJ_NAME = "Et",
                AREA_CTYPE = "EtAr",
                Products = new Product[] {
                    new Product {
                        BANDWIDTH_A = "ProA",
                        CONNECTION_TYPE = "ProCon"
                    }
                }
            }
        }
        },
        new Company
        {
            ORG_NAME = "Co2",
            Salesareas = new SalesArea[]{
            new SalesArea {
                OBJ_NAME = "Sa2",
                AREA_CTYPE = "SaAr2",
            }
        },
            Nodes = new Node[] {
            new Node {
                OBJ_NAME = "No2",
                BUILDING = "NoBu2"
            }
        },
            Ethernetareas = new EthernetArea[] {
            new EthernetArea {
                OBJ_NAME = "Et2",
                AREA_CTYPE = "EtAr2",
                Products = new Product[] {
                    new Product {
                        BANDWIDTH_A = "ProA2",
                        CONNECTION_TYPE = "ProCon2"
                    },
                    new Product {
                        BANDWIDTH_A = "ProA3",
                        CONNECTION_TYPE = "ProCon3"
                    }
                }
            }
        }
        }
    }
    };

    var sas = root.Companies.SelectMany(x => x.Salesareas.Select(y => new { Company = x.ORG_NAME, SalesName = y.OBJ_NAME, SalesAreaType = y.AREA_CTYPE }));
    var nodes = root.Companies.SelectMany(x => x.Nodes.Select(y => new { Company = x.ORG_NAME, NodesName = y.OBJ_NAME, NodeBuilding = y.BUILDING }));
    var ethes = root.Companies.SelectMany(x => x.Ethernetareas.SelectMany(y => y.Products .Select(z => new { Company = x.ORG_NAME, EthernetName = y.OBJ_NAME, EthernetArea = y.AREA_CTYPE, BandwithA = z.BANDWIDTH_A, ConnnectionType = z.CONNECTION_TYPE })));

    sas.Join(nodes, x => x.Company, y => y.Company, (x, y) => new {x.Company, x.SalesName, x.SalesAreaType, y.NodesName, y.NodeBuilding})
        .Join(ethes, x => x.Company, y => y.Company, (x, y) => new {x.Company, x.SalesName, x.SalesAreaType, x.NodesName, x.NodeBuilding, y.EthernetName, y.EthernetArea, y.BandwithA, y.ConnnectionType})
        .Dump();

Dump() is a LinqPad Extension Method

And here is the result...

With the Collection you can use CsvHelper as recommended by @Drag And Drop in order to generate the CSV file.... :)

Edit

I just realized that @Drag And Drop code is more straightforward, cleaner and better.... :)

Thx for the last paragraph, and for showing the SelectMany syntax for this question. I could not bring myself to admit I wrote one too. It's so messy syntax that there is no way to format it in a readable way.

Collectives™ on Stack Overflow

Export data from nested c# classes to csv file

2 Answers 2

7 Comments

Edit

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Edit

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related