In a perfect world, spreadsheet lists of email addresses should never exist, in reality they do. Especially in the world of marketing! I’m often asked for a way to import a list of Contacts or Leads into a marketing list. Not so bad if they are all new (the fax field hack trick works like a charm 😉) but often some already exist and duplicate records hurt my soul.

I set out out on a mission to create a flow that took a CSV file that uses the display names from Dataverse and turned it into a populated marketing list. The fields are flexible. Existing records will be added to the list, new records will be created then added. And of course it works for Contacts or Leads.. Nothing will be hard coded, nothing will be repeated, and custom fields will be supported.

In other words: I fell down a rabbit hole about 2 months ago trying to do something with flow that is probably way better handled with real code, I’ve only just found my way out.

Strings only (for now)

This post is going to focus on getting your import working for string (text) fields only. My next post or two will add the enhancements you need to also populate choices, owners and lookups too, but for the sake of keeping this post for being so damn long you get cramp from scrolling - it will have to wait. I promise I have already worked that bit out so no false promises here.

Part 1: Upload a file & prepare to transform

Part 2: Display names -> Logical names

One of the hardest lessons I had to learn during this was that headers are easier to change than JSON properties (in the world of Power Automate). So before we go in all guns blazing to the JSON part - first lets switch out the Display Name headers e.g. ‘City’ into the corresponding Logical Name address1_city. Remember, no hard coding ,and all custom fields supported.

Part 3: CSV -> JSON

I found this handy little blog post on how to covert CSV into JSON using power automate which works great. However, George Doubinski wanted to flex his developer skills and wrote me a neat little custom connector that does this instead. CSV goes in -> JSON comes out. The GitHub repo for this beauty lives here and CRM Tip of the Day post pending with the juicy details.

P.S I know its possible to do things with CSV files in flow but they usually rely on it being hosted in a specific location in OneDrive/SharePoint which I really wanted to avoid. I like JSON better and so does the Dataverse connector.

Part 4: JSON -> Dataverse

So now you have a big lump of beautifully fromatted JSON with logical names and values. Time to find or create the Dataverse rows (without mapping specific fields or selecting a specific Table name) to add to our marketing list.

Hello CSV -> Marketing List!

Thanks for sticking around brave one, behold the magic that is turning a CSV into a Dynamics 365/Dataverse Marketing List.

Was it worth it?

Maybe, maybe not, but I learnt a lot of things along the way and came out the other side in one piece - I think future Amey will be grateful one day. The hole digging chronicles will return soon with another post or two on how we can enhance this flow to also set up Lookups, Owners and Choices (Option Sets) via the same method. Also, you could make it even better by creating a custom page in your model driven app that allows the user to upload a file and create the marketing list, via this flow, without ever leaving their app.