Tag: xrmtoolbox

Data Importer Tool for PPTB – I did a thing!

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!

DateTime fields, XrmToolBox, & Dynamics 365 behaviour

Recently we’ve been rapid producing & deploying solutions, due to the current pandemic. One of the apps that I’ve been working on required quite a few fields for data capture. Well, truthfully most apps require quite a few fields, but I thought that I’d talk about this one in particular, due to something that I discovered.

Now, we all know how to create fields in the Power Platform maker experience. It’s really quite simple – you select that you want to add a new field, put in the details/type of field, & save. Hey voila – you have yourself a nice new field! You can then go on to add it to forms, views, etc etc. We all know how it’s done:

What I’ve found myself doing recently though is not to create fields through the Maker interface (make.powerapps.com), especially when there are lots of fields to create. Instead, I’ve been using the XrmToolBox to do this. There’s a very helpful tool within it called Attribute Editor, which allows you to use an Excel spreadsheet. It takes this, and creates the relevant fields through the Dynamics 365 API.

One of the reasons for doing things this way was that it allows me to get on with other things whilst the fields are being created. Although it doesn’t happen in the blink of an eye (especially when there are a lot of fields to create), I can leave it whizzing along, and do something else. This, of course, makes me feel VERY productive!

Right – back to what I was saying. So I had a lot of fields to create, and many of them needed to be datetime fields. Actually, all I needed was the time component, but unfortunately Dynamics 365 DOESN’T allow you to just show the time. It’s either Date, or DateTime, but no option for JUST Time. A flaw, in my opinion, for what it’s worth….

So I created the Excel template, started the process, and went on to do something else. I of course made sure to specify that the field type should be ‘DateTime’.

Coming back to it when it had finished, I started to place fields on forms, and noticed something strange. All of the datetime fields that I had created through this were date ONLY. This was…puzzling! Going to check the fields themselves, they were set as Date ONLY, not DateTime!

I went back to check my upload spreadsheet, and it was set correctly there. I even tried uploading another field, but still the same issue was occurring.

Now, with the way that Dynamics 365/Power Platform works, once you’ve created a field & saved it, you can’t change the field type. When it’s created it’s saved down to the underlying database structure as the specified field type, and that’s it. No way to change it…or at least not through the front end!

With this in mind, I fired up another one of the XrmToolBox tools, namely Attribute Manager. What this handy tool does is, behind the scenes, allow you to change the field type. Well, it doesn’t ACTUALLY change it directly – it clones it, deletes the original, then clones it back. There are some caveats to it working properly (ie that the field isn’t used in a view somewhere, for instance), but it’s really helpful.

Note: It only works for custom created fields, not the default OOB fields!

Depending on the field type that you’re wanting to change it to, you can select different options. However for DateTime, there’s only one option. OK – I was going to see what happened.

Well, I ran the update, but nothing changed. It was still ‘Date’ only within the interface, which was really being incredibly annoying. It wasn’t as if I could just delete & recreate it (well, I could, of course). I had dozens & dozens of these to do, and quite frankly didn’t want to spend all of that time in doing this.

Thankfully (with the help of one of my colleagues, who’s an experienced & devoted developer – thanks Sid!), we found the solution.

See, I had been doing everything within the ‘new’ interface. This is the one that Microsoft keeps pushing everyone to, as they don’t want people to really be using the Classic Interface anymore. That’s all very well & good, but the ‘new’ interface isn’t on parity (for some things).

Reverting back to the Classic interface (note that the option below is only available when working within a solution!), we discovered some hidden behaviour

We located the entity that we needed, and the field itself, and opened it in Classic. With the screen that’s presented (I do miss this in some ways – I remember the days where I almost lived permanently in here!) we AMAZINGLY have the following option:

We can CHANGE THE TYPE!! Now, this is just with the field that we’ve selected. To be frank, I have no idea at this point about any other field types, and would need to explore that separately. But for the moment, my problem has been solved! (well, to the point that I have ‘time’ values available – I’d still like to see JUST time values being an option).

So with this in mind, I merrily waded through the dozens of fields in the Classic UI, changing them all as needed. It wasn’t just a few minutes of work, but it was definitely much less time that deleting & manually creating each one!

So, really quite helpful. The only other thoughts that I had around things were that it would be nice if the various tools within the XrmToolBox could do this as well. However, the fact that they don’t seem able to actually seems to be a limitation of the API. Having gone to check the different field types & how they’re set programmatically (https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/types-of-fields), I’ve noticed the following:

There really doesn’t seem to be any way to specify the different sub-type, which is a shame!

Have you ever had a similar situation with fields? Drop a comment below- I’d love to hear about it.