How to import .csv data into CDS

Welcome back to this small series of posts on PowerApps and CDS.

Now that we’ve looked at how to create your first CDS database in the previous article HERE, let’s see how we can use it by loading some sample data.

We can load a sub-set of data generated using a free online service like Fake Name Generator (http://www.fakenamegenerator.com/). Go to Order in Bulk and get a set of data generated based on the setting you configure on the wizard. You can get a maximum of 50,000 records, but for the purpose of this example de default 3,000 should suffice.

Now, with the data file in hand, let’s see how we can load these records into our Contact entity.

Note the documentation available on the docs site HERE.

There are a few things you can do here with this source file to make it load without issues. You can either manage the data to map to the entity you are loading data to, or map your data to a template you can export from the Export template option at the top of the entity fields listing. I chose to remove a few columns from the generated source file to make it easier to load.

Make sure that your data source includes the values for the required columns on the target entity, otherwise you will end-up with mapping errors and will not be able to continue. The failure message is not really that descriptive. In our case, add to the source data file the following required fields and values for each record:

 

IsPhoneContactAllowed	IsEmailContactAllowed	SOURCE	STATUS	PARTYTYPE
TRUE	TRUE	Default	Active	Person

You can export a template if you prefer by navigating to Contact, and clicking on Export template.

On the next screen, select the Add All button to add all columns in the target entity, and click on Export to Excel at the bottom. That generates a template and lets you download it.

Now click on the back arrow and return to the Contact view.

Once you have your import data ready, on the top navigation bar, find the Import data button and click it.

This starts the wizard to import data from .csv or Excel. Once you select a file (your file you generated earlier), you will most likely get a warning about required mapping fields not being properly mapped. That is expected when you bring in data from an external source. Let’s proceed to mapping our data.

Click on Show mapping. This presents you with a listing of all field names from the header in the import file, and the available drop-down values from your current Contact target entity. Start mapping as closely as possible to the data you have to import.

Note that, if you try to import field values with a relationship to other entities, like the Account/Organization for example, you need to have the data already imported. This is similar behavior to any other relational data source.

I’ll be mapping a few of the most common fields, like name(s), email addresses, phone numbers, etc. When complete, click on the Save changes button at the top of the screen to confirm your mapping. You will be prompted if there are any errors detected in the mapping, and you can go back and correct them before you begin your import process.

Once your import mapping is complete, make sure the mapping is valid. You should see a Match green check-box message.

Click on the Import button to begin the process. This could take a while, depending on the total number of records you have in the source file, so grab a coffee.

On an import of 10k records, it took only seconds for the import to be processed, so don’t take too long with that coffee.

When complete, you will get an import successful message as below.

Now go back to the Contact entity. Click on the Data tab, and validate that the data you imported is in fact available. Your view should show the records as below.

And that’s about it. While this is the most simplistic way to get data into CDS, this only applies where the source data sets are small and not very complex, like for example when building a POC/demo. There are of course more advanced ways to get data into CDS, and we’ll be looking at those in the future.

Enjoy!