Multiple Record Types in One File?

[mslissap]

Is there a way to read multiple record types from one CSV file? The file I am getting has a header (with column names and values), followed by detail records (with column names and values). It looks something like this:

Version,Control Number,Date,Time,Routing Number,Records Received,Records Accepted,Total Errors,Total Warnings,Total Duplicates
'4.2','88888888111','20151015','121530',**,'100','96','3','1','1'
Location ID,Location Name,Order Number,Date Filled,Comment
'12345','Test Location','1','20151015','Invalid Identification'
'12345','Test Location','2','20151015','Out of Stock'
'12345','Test Location','3','20151015','Invalid Item Number'
'12345','Test Location','4','20151015','Duplicate = same as Row 3'

Thanks for your help!

[JoshClose]

You can register as many maps as you want. You can look through each record and determine what type of record it is, then read that type.

So, if you see Version, then you know it's a heading for something. All the rows after you can read as that type, until you come across another keyword, like Location.

[saumyatripathi]

Hey Josh - Don't mean to open this issue up again but I have a related questions. The class maps in this case need to be index based i.e. name based class maps will not work. Is that correct?

[sathishcdr]

Name based class map did not work for me too. I used Index based which is working fine.

[JoshClose]

I'm putting together a name based example right now...

[JoshClose]

Here is a simple example. Tweak it to your situation.

void Main()
{
	using (var stream = new MemoryStream())
	using (var writer = new StreamWriter(stream))
	using (var reader = new StreamReader(stream))
	using (var csv = new CsvReader(reader))
	{
		writer.WriteLine("Id1,Name1");
		writer.WriteLine("1,one");
		writer.WriteLine();
		writer.WriteLine("Id2,Name2");
		writer.WriteLine("2,two");
		writer.Flush();
		stream.Position = 0;
		
		csv.Configuration.RegisterClassMap<Test1Map>();
		csv.Configuration.RegisterClassMap<Test2Map>();

		var list1 = new List<Test1>();
		var list2 = new List<Test2>();
		var type = RecordType.None;
		while (csv.Read())
		{
			if (csv.CurrentRecord.Length == 0)
			{
				continue;
			}
			
			if (csv.GetField(0) == "Id1")
			{
				csv.ReadHeader();
				type = RecordType.Type1;
				continue;
			}

			if (csv.GetField(0) == "Id2")
			{
				csv.ReadHeader();
				type = RecordType.Type2;
				continue;
			}

			switch (type)
			{
				case RecordType.Type1:
					list1.Add(csv.GetRecord<Test1>());
					break;
				case RecordType.Type2:
					list2.Add(csv.GetRecord<Test2>());
					break;
			}
		}
		
		list1.Dump();
		list2.Dump();
	}
}

public enum RecordType
{
	None = 0,
	Type1,
	Type2
}

public class Test1
{
	public int Id1 { get; set; }
	public string Name1 { get; set;}
}

public class Test2
{
	public int Id2 { get; set; }
	public string Name2 { get; set;}
}

public sealed class Test1Map : CsvClassMap<Test1>
{
	public Test1Map()
	{
		Map(m => m.Id1);
		Map(m => m.Name1);
	}
}

public sealed class Test2Map : CsvClassMap<Test2>
{
	public Test2Map()
	{
		Map(m => m.Id2);
		Map(m => m.Name2);
	}
}

[saumyatripathi]

Hey Josh - Thanks for taking the time to respond. I seem to be facing a couple of issues with the code that you provided -

1. csv.GetField(0) == "Id1" is never evaluated to true as the Reader reads the first row by default.
2. csv.ReadHeader(); throws exception 'Header record has already been read.'

Are there any issues with my setup/version?
Thanks again for the help.

[JoshClose]

Sorry... this is only possible in version 3.

[saumyatripathi]

Ok cool. I'll work with index mapping then. Thanks!

[chris-hockeystickco]

Hello...I have a similar but different use case. I have a row that is acting as a composite of multiple classes. These classes thereafter represent a nested, aggregated object.
e.g.:
(header)product_name, product_sku, product_description, promotion_date, promotion_price
(data) xxx, 1234, "great product", 12/4/202, $3.41
I want to read the first 3 cols in the Product object then the other two into the Promotion object.
Can I do this with two maps? Can I create a view between two classes in the classMap?

Thanks

[AltruCoder]

@chris-hockeystickco You can either do it with several maps

public class Program
{
    static void Main(string[] args)
    {
        using (var stream = new MemoryStream())
        using (var writer = new StreamWriter(stream))
        using (var reader = new StreamReader(stream))
        using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
        {
            writer.WriteLine("product_name,product_sku,product_description,promotion_date,promotion_price");
            writer.WriteLine("xxx,1234,great product,12/4/202,$3.41");
            writer.Flush();
            stream.Position = 0;

            csv.Configuration.RegisterClassMap<TransactionMap>();

            var records = csv.GetRecords<Transaction>().ToList();
        }
    }
}

public class Transaction
{
    public Product Product { get; set; }
    public Promotion Promotion { get; set; }
}

public class Product
{
    public string Name { get; set; }
    public string Sku { get; set; }
    public string Description { get; set; }
}

public class Promotion
{
    public DateTime Date { get; set; }
    public string Price { get; set; }
}

public class TransactionMap : ClassMap<Transaction>
{
    public TransactionMap()
    {
        References<ProductMap>(m => m.Product);
        References<PromotionMap>(m => m.Promotion);
    }
}

public class ProductMap : ClassMap<Product>
{
    public ProductMap()
    {
        Map(m => m.Name).Name("product_name");
        Map(m => m.Sku).Name("product_sku");
        Map(m => m.Description).Name("product_description");
    }
}

public class PromotionMap : ClassMap<Promotion>
{
    public PromotionMap()
    {
        Map(m => m.Date).Name("promotion_date");
        Map(m => m.Price).Name("promotion_price");
    }
}

Or with one map

public class Program
{
    static void Main(string[] args)
    {
        using (var stream = new MemoryStream())
        using (var writer = new StreamWriter(stream))
        using (var reader = new StreamReader(stream))
        using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
        {
            writer.WriteLine("product_name,product_sku,product_description,promotion_date,promotion_price");
            writer.WriteLine("xxx,1234,great product,12/4/202,$3.41");
            writer.Flush();
            stream.Position = 0;

            csv.Configuration.RegisterClassMap<TransactionMap>();

            var records = csv.GetRecords<Transaction>().ToList();
        }
    }
}

public class Transaction
{
    public Product Product { get; set; }
    public Promotion Promotion { get; set; }
}

public class Product
{
    public string Name { get; set; }
    public string Sku { get; set; }
    public string Description { get; set; }
}

public class Promotion
{
    public DateTime Date { get; set; }
    public string Price { get; set; }
}
public class TransactionMap : ClassMap<Transaction>
{
    public TransactionMap()
    {
        Map(m => m.Product.Name).Name("product_name");
        Map(m => m.Product.Sku).Name("product_sku");
        Map(m => m.Product.Description).Name("product_description");
        Map(m => m.Promotion.Date).Name("promotion_date");
        Map(m => m.Promotion.Price).Name("promotion_price");
    }
}

[garrettcollier]

I am trying to read in multiple CSV files and I don't have any understanding of which Classmap is needed until I read the CSV file. I need to also interpret multiple different header names using these mappings. I currently have the following code in my Index. Is there a way to check the first field "csv.GetField(0)" against all the values in each of my 3 mappings for each row and then add that value to one of my class lists to then be added to a database. I currently get an error on my while loop saying one of the three record types is not present in the list because I assume it gets stuck using a single mapping instead of multiple

public class IndexModel : PageModel
    {
        private readonly ClientContext _context;
        public IndexModel(ClientContext context)
        {
            _context = context;
        }

        [BindProperty]
        public FileUpload fileUpload { get; set; }

        //create new lists for all class data types to add to database
        public List<Client> clients = new List<Client>();
        public List<Demographic> demographics = new List<Demographic>();
        public List<ReturnData> returnDatas = new List<ReturnData>();

        public void OnGet()
        {
            ViewData["SuccessMessage"] = "Upload necessary files (MAX OF 4 FILES)";
        }
        public ActionResult OnPostUpload(FileUpload fileUpload)
        {
            //counter
            int i = 1;

            //type of Record
            RecordType type = RecordType.None;

            //for each file in the form files
            foreach (var file in fileUpload.FormFiles)
            {
                //display filenames
                ViewData[i.ToString()] = file.FileName;
                i++;

                var config = new CsvConfiguration(CultureInfo.InvariantCulture)
                {
                    MissingFieldFound = null,
                    HeaderValidated = null,
                    IgnoreBlankLines = true,
                    UseNewObjectForNullReferenceMembers = false
                };

                //use streamReader and csvHelper to pull records from file
                using var stream = new MemoryStream();
                using var writer = new StreamWriter(stream);
                using var sreader = new StreamReader(file.OpenReadStream());
                using (var csv = new CsvReader(sreader, config))
                {
                    string[] headerRow = csv.HeaderRecord;

                    //add all context mappings to interpret mutliple different header names
                    csv.Context.RegisterClassMap<ClientMap>();
                    csv.Context.RegisterClassMap<DemoMap>();
                    csv.Context.RegisterClassMap<DataMap>();

                    //grab records and add them to class lists
                    while (csv.Read())
                    {
                        clients.Add(csv.GetRecord<Client>());
                        demographics.Add(csv.GetRecord<Demographic>());
                        returnDatas.Add(csv.GetRecord<ReturnData>());
                        }

                    }
                }
            }

            foreach (var client in clients)
            {
                _context.Client.Add(client);
                _context.SaveChanges();
            }

            foreach (var demographic in demographics)
            {
                _context.Demographic.Add(demographic);
                _context.SaveChanges();
            }

            foreach (var returnData in returnDatas)
            {
                _context.ReturnData.Add(returnData);
                _context.SaveChanges();
            }

            //Save database changes
            _context.SaveChanges();

            //Process uploaded files
            ViewData["SuccessMessage"] = fileUpload.FormFiles.Count.ToString() + " file(s) uploaded!";
            var DropDownAndCheckBoxCount = i;
            return Page();
        }
        public class FileUpload
        {
            [Required]
            [Display(Name = "File")]
            public List<IFormFile> FormFiles { get; set; } // convert to list
            public string SuccessMessage { get; set; }
        }
    }


    //Client data mapping
        public sealed class ClientMap : ClassMap<Client>
    {
        public ClientMap()
        {
            Map(m => m.ID).Name("ID", "Id");
            Map(m => m.FirstName).Name("FirstName", "First Name");
            Map(m => m.LastName).Name("LastName", "Last Name");
            Map(m => m.DoB).Name("DateOfBirth", "DoB", "Date of Birth");
            Map(m => m.Last4SS).Name("Last 4", "XXX-XX-1234", "Last Four", "Last 4 SS");
        }
    }

    //enum to reference the different mappings for the csv
    public enum RecordType
    {
        None = 0,
        ClientType,
        DemographicType,
        ReturnDataType,
        TaxYearType
    }

    //Demographic data mapping
    public sealed class DemoMap : ClassMap<Demographic>
    {
        public DemoMap()
        {
            Map(m => m.ID).Name("ID", "Id");
            Map(m => m.TaxYear).Name("Tax Year", "TaxYear");
            Map(m => m.Address).Name("Street Address", "Address");
            Map(m => m.Zip).Name("ZIP", "Zip", "zip", "Postal Code");
            Map(m => m.County).Name("County", "Location");
            Map(m => m.State).Name("State", "ST");
        }
    }

    //ReturnData data mapping
    public sealed class DataMap : ClassMap<ReturnData>
    {
        public DataMap()
        {
            Map(m => m.ID).Name("ID", "Id");
            Map(m => m.TaxYear).Name("TaxYear", "Tax Year");
            Map(m => m.FederalReturn).Name("FedReturn", "Federal");
            Map(m => m.TotalRefund).Name("Total Refund", "TotalRefund");
            Map(m => m.EITC).Name("EITC");
            Map(m => m.CTC).Name("CTC");
            Map(m => m.Dependents).Name("dependents");
            Map(m => m.SurveyScore).Name("Questions");
        }
    }

[JoshClose]

Have you tried this? https://joshclose.github.io/CsvHelper/examples/reading/reading-multiple-data-sets/

[garrettcollier]

I have this currently now, one of the file's records fell into the final case, can I get it to skip that record and move forward to the rest?

foreach (var file in fileUpload.FormFiles)
            {
                //display filenames
                ViewData[i.ToString()] = file.FileName;
                i++;

                var config = new CsvConfiguration(CultureInfo.InvariantCulture)
                {
                    MissingFieldFound = null,
                    HeaderValidated = null,
                    IgnoreBlankLines = true,
                    UseNewObjectForNullReferenceMembers = false
                };

                //use streamReader and csvHelper to pull records from file
                using var stream = new MemoryStream();
                using var writer = new StreamWriter(stream);
                using var sreader = new StreamReader(file.OpenReadStream());
                using (var csv = new CsvReader(sreader, config))
                {
                    string[] headerRow = csv.HeaderRecord;

                    //WRITE A NEW COLUMN TO THE CSV FILE FOR THE CLIENT ID PRIMARY KEY

                    //add all context mappings to interpret mutliple different header names
                    csv.Context.RegisterClassMap<ClientMap>();
                    csv.Context.RegisterClassMap<DemoMap>();
                    csv.Context.RegisterClassMap<DataMap>();

                    //Header bool
                    bool isHeader = true;

                    //grab records and add them to class lists
                    while (csv.Read())
                    {
                        //clients.Add(csv.GetRecord<Client>());
                        //demographics.Add(csv.GetRecord<Demographic>());
                        //returnDatas.Add(csv.GetRecord<ReturnData>());

                        if (isHeader)
                        {
                            csv.ReadHeader();
                            isHeader = false;
                            continue;
                        }

                        if (string.IsNullOrEmpty(csv.GetField(0)))
                        {
                            isHeader = true;
                            continue;
                        }

                        switch (csv.HeaderRecord[0])
                        {
                            case "clientId":
                                clients.Add(csv.GetRecord<Client>());
                                break;
                            case "DemoId":
                                demographics.Add(csv.GetRecord<Demographic>());
                                break;
                            case "ReturnId":
                                returnDatas.Add(csv.GetRecord<ReturnData>());
                                break;
                            default:
                                throw new InvalidOperationException("Unknown record type.");
                        }
                    }
                }
            }

            foreach (var client in clients)
            {
                _context.Client.Add(client);
                _context.SaveChanges();
            }

            foreach (var demographic in demographics)
            {
                _context.Demographic.Add(demographic);
                _context.SaveChanges();
            }

            foreach (var returnData in returnDatas)
            {
                _context.ReturnData.Add(returnData);
                _context.SaveChanges();
            }

            //Save database changes
            _context.SaveChanges();

[JoshClose]

The example I posted assumes a few things. You'll need to adapt to your data. Can you post some sample data that shows what's happening?