If you’ve been tasked with integrating data, then you know there are a wide variety of challenges that you must overcome. Every time I’m asked to programmatically import large data sets from a flat file like a CSV or Excel sheet, I think about how Forest Gump would react. If he was a developer, I know he would say “Flat files are like a box of chocolates … You never know what you’re going to get.” There’s a lot of truth to that statement. When data is stored in a flat file, there are no rules or constraints on what is delivered.
If you are faced with this situation, it’s a best practice to fully validate these files before you import a single row of data. A validation process should confirm that the structure is acceptable to import, and alert the client if it is not. This process should provide a log of issues for the client to review, so they can fix then resubmit their data.
How do I validate a flat file with
Here’s my top three items to validate, along with the related classes available from the metadata service. You can view full code samples on Microsoft’s site here:
Verify entities and fields exist
Each file should be named after a specific entity, and each column should represent an attribute. You can validate the entities using RetrieveAttributeRequest.EntityLogicalName and the fields using AttributeMetadata.SchemaName.
Verify required fields contain data
Every required field in the file should contain data. These are most likely your unique identifiers, and cannot be blank. You can find the required fields using AttributeMetadata.RequiredLevel.
Verify the data matches the field constraints
Every field should contain the correct type of data that matches the Dynamics 365 schema. For example, if the field is a decimal, you should verify the data is a number. You can find the type by using AttributeMetadata.AttributeType. Data should also respect the minimum and maximum length and values by using AttributeMetadata.MaxLength, AttributeMetadata.MaxValue and AttributeMetadata.MinValue.
Beringer Technology Group, a leading Microsoft Gold Certified Partner specializing in