If you’re not aware of the ‘I did a thing’ quote origin, it’s Jeremy Clarkson’s catchphrase from Clarkson’s Farm. Which I’ve been watching religiously for the last few years – it’s an amazing eye opener into the reality of farming (from a UK standpoint, though has wider principles too). When I decided to create my own PPTB tool, I just couldn’t resist using this as part of the opening. Whether the tool results are better than Jeremy’s farming attempts remains to be seen… 

This is the story of why I built a tool I’d never have dreamed of building a couple of years ago, what it does, and why I think you should go try it out and use it.

The problem/challenge

If you work with Dynamics 365 or Power Platform (specifically data in Dataverse), you’ll know this one. Someone hands you a spreadsheet, and it needs to be in Dataverse. Properly – with with lookups resolved and choices mapped, and ideally without losing a week (or more) of your life.

I’ve tried the usual suspects over the years, and each one fell short for me in some way: 

• The data import wizard – no real way to preview your data before it goes in. You find out what went wrong afterwards. 

• Excel import – the same sort of gaps, and fiddly when you need real control. 

• Dataflows – yes, Power Query gives you a preview, but for what is often a fairly straightforward job it’s a lot more difficult than it needs to be. 

• Lookups – I had serious challenges getting these set, particularly where alternate keys were involved. 

• Option sets – where the name of the value wasn’t the actual value (for reasons I’m still not entirely sure about), it just errored. Constantly. I’m not sure what Microsoft holds against these – maybe because I decided against using SSIS?

• SSIS – it would do the job, but that’s a sledgehammer, usually used for very complicated and large datasets. When you have flat files to import, surely it doesn’t need the full power of this.

I also couldn’t pause an import in any of the existing tooling I used. When you’re a long way into a big load and something looks wrong, that matters. And on top of all that, I wanted proper, verbose logging that was easy to read (if you’ve ever taken a look at the errors that the native import tool gives, you’ll know EXACTLY what I’m talking about). There was nothing on the market that did what I needed, so I decided to build it. 

Where the inspiration came from 

Well, I didn’t just decide to build it. I’ve been a major fan & user of XrmToolBox (XTB) for a number of years. The commitment by people in the community to build tools on their own time, for free, is incredibly amazing and inspiring. I’ve used many of the XTB tools over the years, so when I looked to see what could be done to solve my frustrations with data imports, my first port of call was to go look at what might be available in XTB.

Now there is a data import tool there, called ‘DataImport++‘. It’s still in use by people, and I went to try it out, but it wasn’t giving me all that I was looking for. I contacted the people who developed/support it, who said that it was mostly in support mode now, with no major releases being planned for it, but that I was welcome to fork & submit ideas for it.

I tool a look at doing this, but found that there were potentially limitations with what I was looking to do. Additionally, with the emergence of the Power Platform ToolBox (PPTB) on the scene, I was looking to see if I could do something with a much more modern influence, look & feel.

PPTB is a desktop application where you can discover, install and run community-built tools in one place, and it openly honours XrmToolBox as its foundation. It felt like the right place to see what I could do. I didn’t just look to copy & reskin DataImport ++ – my development went down a different pathway, with some different functionality. However, I want to give full credit to the developers for Data Import++ for the inspiration to create my tool – it simply wouldn’t exist without the idea that came before it. 

Giving something back 

Our community runs on people sharing what they’ve built. Every one of us has used a free tool, a blog post, a snippet or a workaround that someone else took the time to share, and it’s saved us hours. I’ve benefited from that for years, and I wanted to give something back. I’ve obviously been blogging for a while now (along with other community engagements), but always wanted to be able to be part of an effort to create a tool for the community.

Here’s the honest bit: I’m not a developer. Building something like this by hand would have been totally beyond my capabilities. What made it possible was AI. I used VS Code with GitHub Copilot, and combined it with two things I did bring to the table: knowing how to prompt it well, and knowing the scenario I wanted to build for inside out, because I’d lived with it on real client projects. 

That combination is what got me from “this should exist” to “this exists”. I’m genuinely grateful that this is now possible. Ten years ago, an idea like this would have stayed an idea, or a long wish-list item for someone else to build. If you’ve got a problem you understand deeply and have been told “that’s a developer thing”, my advice is to have a go, especially given what we’re able to do nowadays without coding ourselves!

What it does 

At its heart it’s simple: pick a CSV or Excel file, pick a Dataverse table, map your columns, and import. The detail is where it earns its keep: 

• CSV and Excel (.xlsx/.xls) – choose your file and worksheet, and headers are detected automatically. 

• Column-first mapping – the grid lists the Dataverse columns and you pick the source column for each. Columnsare auto-matched by logical and display name, so most of the donkey work is done for you. The reason for doing it this way (at least for the moment) is because I’ve seen scenarios where source values need to be mapped to multiple destination columns. I’m open to changing the way this works, based on feedback being received!

• All the column types – text, numbers, currency, dates, GUIDs, Yes/No, choices, status/status reason, multi-select, lookups and owner. There’s even a Created On override. 

• Choice mapping – confirm how each distinct value in your spreadsheet maps to a Dataverse option (local or global), with green/red badges so you can see at a glance what still needs attention. This one fixes my option set pain. 

• Lookup resolution – by GUID, or by a column value queried in Dataverse. Polymorphic lookups are supported, and you don’t need an alternate key to match records. 

• Preview – see what’s going in before it goes in. 

• Create, update or upsert – matching on one or more columns. 

• Verbose logging – live progress and a per-row failure report. It carries on past failures, so one dodgy row doesn’t wreck the lot. 

• Pause the import – mid-run, whenever you need to. 

• Save and load configurations – export your table, settings and mappings to XML and reload them later. If you do the same import across environments, this is a lifesaver. 

• Light and dark themes – because we’re not savages. 

Speed 

This is very important and relevant. If you’ve ever done data imports at scale with the import wized, you’ll be more thna aware that not only are you looking at taking a coffee break, you likely need a breakfast & lunch break too!

The tool uses batching, with a configurable batch size, to speed things up. As an example, I ran it against a very large data set of 100,000 rows, and it took around 25 minutes. From experience, the out-of-the-box import takes a lot longer to get through that kind of volume. I know the pain, because I’ve sat through it more than once, watching a progress bar and questioning my life choices. 

Is this a conceptual tool?

Not at ALL!! I created it due to staring at needing to import circa 500K rows of data for a client solution – and having the standard import error, error & error on me I’ve now used it on more than 10 client engagements, and have colleagures using it as well.

It has now passed 100 downloads (which isn’t a major milestone, but has indeed prompted me to actually write this blog post!). For something that started life as “I just need this to work on my project”, I’m chuffed. Thank you to everyone who has installed it. 

FYI, it seems that some organisations restrict PPTB. If that’s you, I’d suggest working with your IT and governance teams to get it unblocked. It’s an extremely valuable tool, not just for Data Importer but for the whole ecosystem of community tools that now sit inside it. 

Go and try it 

You can find Data Importer in the PPTB tool marketplace here: Data Importer on PPTB. 

The code lives on GitHub, and that’s the place for feedback. If you find a bug, or you’d like improvements or extra functionality, please raise it there as an issue or feature request. This tool gets better when the people using it tell me what they need, and I’d love to hear what you’d like to see. 

Go and give it a spin, and let me know what you think. And if it all goes wrong, well, at least it’s not a barn full of cows!