tricky CSV file output based on a List<Order> orders collection

Question

I have to produce this CSV layout, but a little unsure how to do it.

I have a Order class that I have to convert into a CSV file.

public class Order
{
       public int ID {get; set;}
       public int Created {get;set;}
       public List<Item> Items {get;set;}
}
public class Item
{
        public string Sku {get;set;}
        public int Quantity {get;set;}
}

So each row represents a order. If an order has more than 2 Items in it, I will have to continue to the next line leaving everything blank except for the sku, quanity and a specifical field.

The last field will be "Y" if its continuing from the previous line, "N" otherwise.

So I have:

List<Order> orders = Db.GetOrders();

So a sample output of the CSV would be:

"orderID", "Created", "Item01", "quantity01", "Item02", "N"

if there are more than 2 items, it has to output:

"orderID", "Created", "Item01", "quantity01", "Item02", "N"
"", "", "Item03", "quantity03", "", "", "Y"

So the above order had 3 items, notice how the placeholder for Item#4 is still present, but just empty double quotes.

So every field is mandatory.

How would I produce this?

So far I have:

StringBuilder sb = new StringBuilder();

for(int x = 0; x < orders.Count; x++)
{
         Order o = orders[x];

         if(x % 2 = 0)
         {
           sb.Append(o.ID).Append(",");
           sb.Append(o.Created).Append(",");
         }

}

The trick I guess is to figure out if I need to continue to the next line, and then I have to fill any empty spots if I have less than 2 items in the row?

(you can ignore any bad characters in the output, I'll deal will that later thanks)

Answer 1

your inner loop looks something like this:

for (i = 0; i < items.Count; ++i) {
  sb.Append(sku)
  sb.Append(qty)
  if (i % 2 == 1) {
    if (i == items.Count - 1) {
      sb.Append("N\n")
    } else {
      sb.Append("Y\n" "" "");
    }
  }
}
if (items.Count == 0) {
  sb.Append("" "" "" "" "N")
}
if (items.Count % 2 == 1) {
  sb.Append("" "" "N")
}

you can expand the pseudocode... the csv format is a bit unusual. typically a csv would contain a list of items, one per line (the order number repeated in each).

Answer 2

I find it helps to decompose problems like this into several methods that each are simple to understand and do a small part of the job. Then you can combine them to solve the larger problem.

Here's something to help you get started. It needs more error handling and probably needs logic to deal with escaping special characters when generating the CSV fields (if the SKU may have commas or quotes within it, for example).

    public class Order
    {
        public int ID { get; set; }
        public int Created { get; set; }
        public List<Item> Items { get; set; }
    }
    public class Item
    {
        public string Sku { get; set; }
        public int Quantity { get; set; }
    }

    public class OrderCsvBuilder
    {
        private readonly StringBuilder m_CsvData = new StringBuilder();

        // constructor accepts a sequence or Orders
        public OrderCsvBuilder(IEnumerable<Order> orders)
        {
            foreach (var order in orders)
                WriteOrder(order);
        }

        // returns the formatted CSV data as a string
        public string GetCsvData()
        {
            return m_CsvData.ToString();
        }

        // writes a single order and its line items to csv format
        private void WriteOrder( Order order )
        {
            WriteCsvFields( false, order.ID, order.Created );
            var itemIndex = 0;
            foreach( var item in order.Items )
                WriteOrderItem( item, itemIndex++ );
        }

        // writes a single order item to csv format
        private void WriteOrderItem( Item item, int itemIndex )
        {
            // write the extra fields when the order item is not the first item
            if( itemIndex > 0 )
                WriteCsvFields( false, string.Empty, string.Empty );
            // use (?:) to append indicator of whether item is first or additional
            WriteCsvFields( true, item.Quantity, item.Sku, itemIndex > 0 ? "Y" : "N" );
        }

        // writes a series of fields to the file in csv form
        private void WriteCsvFields( bool isLineEnd, params object[] fields )
        {
            // write each field to the StringBuilder in CSV format
            // TODO: Need better error handling and escaping of special characters
            foreach( var field in fields )
            {
                m_CsvData.AppendFormat("\"{0}\", ", field);
            }
            // trim extra trailing space and comma if this is the last item of a line
            if( isLineEnd )
                m_CsvData.Remove(m_CsvData.Length - 2, 2);
        }
    }

Answer 3

public string BuildOrdersCsv(List<Order> orders)
{
    StringBuilder sb = new StringBuilder();

    foreach (Order o in orders)
    {
        Append(sb, o.ID, o.Created);
        for (int i = 0; i < 2; i++)
        {
            if (i < o.Items.Count)
                Append(sb, o.Items[i].Sku, o.Items[i].Quantity);
            else
                Append(sb, "", "");
        }
        sb.AppendLine("\"N\"");

        for (int i = 2; i < o.Items.Count; i++)
        {
            Append(sb, "", "", o.Items[i].Sku, o.Items[i].Quantity, "", "");
            sb.AppendLine("\"Y\"");
        }
    }

    return sb.ToString();
}

private void Append(StringBuilder sb, params object[] items)
{
    foreach (object item in items)
    {
        sb.Append("\"").Append(item).Append("\",");
    }
}