Tag Archives: Import

How I can import correctly a xls sheet?

I have one excel file with a sheet “OK”.

M8F69 How I can import correctly a xls sheet?

I need to import the file, i tried:

s = Import["sales.xls", {"Sheets", "OK"}]

header1 = s[[1]];

data = s[[3 ;;]];

sDataset = Thread[header -> #] & /@ data // Map[Association]

But didn’t work because the column headers are in the row 1 and 2 after the column 1. I need:

1.) SALES, GM, %GM and EBITDA pear YEAR per MONTH.

Ideas? Thanks!

Let’s block ads! (Why?)

Recent Questions – Mathematica Stack Exchange

Troubleshooting Solution Import Errors

gggggg 300x225 Troubleshooting Solution Import Errors

Remember when CRM life was so much simpler that solutions did not yet exist? If you had separate development and production environments and you wanted to move your customizations, you simply clicked Export Customizations and voila! It was done. Those were the days.

Nostalgia Warning – in case you’ve forgotten, here’s a screenshot to jog your memory:

022817 1446 Troubleshoo1 Troubleshooting Solution Import Errors

With CRM 2011, the concept of solutions was introduced, giving us a new set of powers – by picking individual entities, workflows, etc., we now had the ability to group together and move only those customizations we wanted to include in our solution. The next great solutions advancement came with CRM 2016: we can now select specific components within each individual entity – so instead of moving the entire contact entity, for example, we have the option of moving only a certain view or field within the entity. And we can do this without having to hack the xml in the zip file.

(By the way, if you want to learn more about solutions, please have a look at our CRM Book: http://crmbook.powerobjects.com/system-administration/customization/solutions/)

But as wonderfully advanced as the CRM solutions concept is, it also has the potential to cause some serious headaches – failed imports, error messages, etc. – when certain things are even slightly amiss. With that in mind, in today’s blog, we will try to ease those headaches by providing you a what-to-do guide for when things don’t go so smoothly. We hope this helps ensure that you will always be able to successfully import your solution into an environment.

But hey, one quick warning: while we strived to make this guide as helpful as possible, dealing with CRM solution import issues can sometimes become quite time-consuming, especially when it requires you to look at XML code. We’ll try our best to guide everyone in this blog, but if you ever feel like you’re in over your head, please contact us – we’re here to help!

The solution import process is quite simple. Under “Settings” / “Solutions,” click Import, as shown below.

022817 1446 Troubleshoo2 Troubleshooting Solution Import Errors

Select the zip file of your Solution, and click Import. At this point, Microsoft Dynamics will calculate the solution.xml file and check that all dependent components are included for the import process. If a missing dependency is found, the next screen will display a dependency error, and you will not be able to import your solution until the missing components are addressed.

Often, if you are importing a solution that came from a different CRM org, a dependency error is caused by a Managed Solution that exists in the source CRM but not in the target. For example, let’s say you have two environments: development (dev) and production (prod). You create a solution in dev called “Email,” add the email entity, and export it. But when you try to import it in prod, you receive the following error during the dependency check:

022817 1446 Troubleshoo3 Troubleshooting Solution Import Errors

At first glance, it may seem daunting to fix a laundry list of dependency errors for a successful import. However, when we take a closer look at the “Managed Solution” column, we see that all the error rows indicate either PowerEmail or PowerSurveyPlus. In this example, it’s because both PowerEmail and PowerSurveyPlus are Managed Solutions that exist in dev but not in prod. To resolve this issue, we need to simply import both PowerEmail and PowerSurveyPlus into prod before importing our “Email” Solution.

So, as far as Managed Solution components go, always remember that they cannot be exported via an Unmanaged Solution. And to export from one CRM org or environment into another, we always recommend that the same Managed Solutions that exist in your source also exist in your target.

Looking at the same example above, let’s say we imported both necessary PowerPacks into our prod environment and then tried to import the “Email” Solution one more time. Again we failed the dependency check, though this time, the error screen contains just one item:

022817 1446 Troubleshoo4 Troubleshooting Solution Import Errors

What does this mean? Here, we begin by looking at the last column, “Required by,” which is formatted as follows: “Component (Entity).” In our example, it’s telling us that the “Email” component within the email entity requires a component that does not exist. Next, we look at the “Type” field – since it is Web Resource, we can deduce that the “E-mail” refers to our form called E-mail, which contains a reference to a web resource called new_email_js_lib (from the “Name/Id” field) that has not been included in our solution.

To successfully import this solution, we will need to add the web resource mentioned in the error screen (new_email_js_lib), export it again, and then try another import.

There are lots of other types of dependency errors that could occur when you don’t include the necessary components in the solutions, including:

  • A field that uses a global option set.
  • A system view that references newly-added fields.
  • Forms that contains newly-added fields.
  • A form that contains a non-existent quick view form.
  • A site map that contains newly-added components.
  • Forms or Business Process Flows that include missing security roles.

Thankfully, the message displayed is usually very helpful in telling us what is missing, as well as why it is needed. Back to the E-mail example above: we fixed our problem by including the missing web resource. But what would be another way for us to import the solution if we didn’t want to move that web resource? Well, since the only dependency in our example is required by the E-mail form, we can simply exclude that form from the solution.

Another error you may receive after selecting the zip file of your solution and clicking Import is: “This solution package cannot be imported because it contains invalid XML.”

022817 1446 Troubleshoo5 Troubleshooting Solution Import Errors

The most likely reason for this error is that someone previously edited the customization.xml file incorrectly, resulting in bad XML code. Often, it is as simple as an error in the capitalization of an identifier, a basic typo, or having a missing or invalid tag. To find out where the error is, look at the error screen and hover your mouse over “Technical Details.” This is actually a link that takes you to a page showing more detailed information. Clicking on the link will reveal something similar to this:

022817 1446 Troubleshoo6 Troubleshooting Solution Import Errors

In this example, we have a typo when specifying our cascade relationships: “NoCas1cade.” This is the result of someone manually editing an XML file and mistyping something. This can be easily corrected by opening the customization.xml file within the zip file (covered below), searching for the typo, and correcting it. Of course, not all fixes are as easily identifiable as this example.

Note: inside the solution zip file, there exist several files. One is “solution.xml,” which contains a list of dependencies required before the import can be attempted. However, this file isn’t foolproof, as there is still a possibility that a dependency is missing but is not listed in the solution.xml file. When this is the case, it results in the initial dependency check succeeding, allowing you to attempt to import the file. However, it will then fail the import, and an error log will be generated.

Besides dependencies, additional errors can occur during an import. When you first select your zip file and click Import, a progress indicator will appear. In the event of an error, the changes included in the solution file to that progress point will not be applied, and you will have the option to download the log file.

022817 1446 Troubleshoo7 Troubleshooting Solution Import Errors

Click on Download Log File, which will result in an XML file – be sure to open it in Microsoft Excel.

WARNING! Any time you deal with XML (or any other code, for that matter), always tread carefully and always make a backup copy before doing any edits. Simply right-click the zip file and copy it. That way, you will have a backup ready to be re-imported if any problems arise during your editing process.

The Excel file will contain two tabs. The first tab, called Solution, will contain some basic information:

022817 1446 Troubleshoo8 Troubleshooting Solution Import Errors

The second tab, called Components, will contain detailed information about each individual item being processed for import. 022817 1446 Troubleshoo9 Troubleshooting Solution Import Errors

This is what we will use to identify issues and determine necessary fixes. The “Status” column will list the components successfully processed before the error occurred. Subsequent components after the failure will remain unprocessed, since the first failure causes CRM to stop processing. In this way, it is easy to identify the source of the error, though there is also a downside: there may still be more issues, but we can only catch one at a time.

Our troubleshooting process will involve looking at the “ErrorCode” column. Based on the message from the screenshot above, the message received in our example is:

“An item with the same key has already been added.”

Some of the error codes are straightforward and to the point, while others require more digging. And on occasion, you will need to make edits to the XML files inside the zip file. Don’t worry: you don’t necessarily need to be an XML expert to work through some of these issues, though for a non-developer, simply looking at code can often be challenging work. However, if you are a CRM administrator, once you get the hang of looking at CRM XML, it will become increasingly familiar each subsequent time. Oh, and one quick and super-important tip: remember that XML is always case-sensitive!

Let’s have a look at different error codes you can receive in this log file, beginning with the example above.

An item with the same key has already been added.

Stated simply, with this error CRM is telling you that you’re trying to create an item that already exists. Unfortunately, this is one of the more generic error messages, so it isn’t always super helpful. However, the good news is that often, this happens for one of two reasons: the field already exists in CRM but is a different type, or it is the same type but the schema is capitalized differently. Let’s look at two examples that show how each issue can happen.

Example 1: there exists a field (type = text) in your contact entity called “new_ShoeSize.” You then attempt to import a solution that includes a field (type = whole number) with the exact name, “new_ShoeSize.” CRM will attempt to create a new field, but will error out because this name – or key, as referenced in the error code – is already taken.

Example 2: let’s say you have the same text field, “new_ShoeSize,” and this time you are importing a solution where the field is also of type text, but the schema of that field is all lower case: “new_shoesize.” This will result in the same error occurring. CRM will recognize that you have a new field called “new_shoesize,” but when it tries to create it, it will find that the name already exists.

Something to keep in mind is that similar types of problems may ultimately result in different ErrorCode readings. For example, capitalization differences in the schema (as described in Example 2 above) may, depending on your CRM version among other things, actually result in different error codes! Argh, right? Well, we hope to cover all of them in this guide. Remember that as long as you are able to start with an error code – any error code, you can usually identify your problem and possible fix.

The ‘XX’ attribute is not declared.

This error message refers to something in the customizations.xml file that CRM does not recognize as valid. For example, an import could error out with the message, “The ‘Label’ attribute is not declared,” followed by “validation failed at …” and then a sample of the XML file where the error occurs.

This means that when the solution file was created, something CRM can’t process (an undeclared variable) was inadvertently included. To fix it, we will need to examine the customization.xml file to remove the offending part of code. Let’s say you downloaded the log file and saw this message:

The import file is invalid. XSD validation failed with the following error: ‘The ‘label’ attribute is not declared.’. The validation failed at: ‘…ition></filter></filter></filter>5.0.0.00002029{3e3ca60a-9732-48bd-b16f-f4682eff6f67}” <<<<>>>> label=”Priority (Case)” />0

Look at the error location, which identifies the spot just before the error. Since the error message states that “label” attribute is not declared, we know the error being generated is due to the very next statement:

label” <<<<>>>> label=”Priority (Case)” />

But wait, if it’s bad code, why is it there in the first place? Possibly your CRM was upgraded from a previous version and there is deprecated code, though it could really be for any number of reasons. The bottom line is that we need to remove it:

  1. Extract customizations.xml from the zip file.
  2. Open it with your favorite text editor, such as notepad.
  3. Search for the error you received. In this example, you would search for label=”Priority (Case)” and then delete it.
  4. Save the file.
  5. Copy it back to the zip file and it will be ready for another import attempt.

Now, don’t get mad, but here’s where it gets even trickier. If you look at the entire error message shown above, you may notice that there are further recurrences of “label=.” Recall that we stated earlier that CRM will fail on the first error and not attempt to process it further, potentially masking additional errors? Well, this is one of those times. In our case, each of the highlighted references will eventually error out during import attempts:

label=”Priority (Case)” />label=”Response By (Case)” />label=”Resolve By (Case)”</span> />0

Further complicating matters is that this error message is only a snippet of the entire customization.xml file, so this error may have several more occurrences, as well. Your options are twofold: fix each error one at a time and try to import after every fix, or review the entire customization.xml file, doing a one-time clean-up of anything that matches the errors shown above. While the second option is definitely faster, please be absolutely certain that you are editing the correct things – don’t inadvertently remove or change something you shouldn’t have! So, if you are still new to this, our recommendation is to do the first method, and just change one piece at a time, until you are sufficiently familiar with solutions content to take a stab at the second option.

Cannot import security role. The role with specified role id is not updatable or role name is not unique.

This message states CRM found a security role name that already exists. You cannot have a duplicate security role name, so if you receive this message, simply find the duplicate security role name(s) that already exist(s) in the target environment.

When you locate the duplicates, if they have different GUIDs (meaning they were created manually in both environments), then you will not be able to import the solution until one of the security roles has been uniquely renamed.

The dependent component XX does not exist.

During the initial import, it is possible that a dependency will not be caught, allowing you to begin the import. If so, you may receive a message similar to the one below:

The dependent component EntityRelationship (Id=po_campaign_po_satisfactionsurvey) does not exist. Failure trying to associate it with SystemForm (Id=5c2404c5-69ed-4633-a193-0852d38f732a) as a dependency. Missing dependency lookup type = PrimaryNameLookup.

To fix this this issue, you will need to examine the error message in more detail. Our example above shows that a relationship is needed for a form that we are importing – likely, it is referenced in the related items, but does not exist in the target system. So, we would just need to add it to our solution if we want the form to be imported.

The label XX already exists. Supply unique labelid values.

There is likely a duplicated ID on the customization.xml file, and we will need to remove it. Let’s say our error message was as follows:

The label ‘Summary’, id: ‘c706accd-f53f-46f5-a254-b64766269216’ already exists. Supply unique labelid values.

We will need to search the customization.xml file for this id, and we will likely see it twice in the same section. However it was created, it will not be possible to import until one of them is removed.

Please exercise caution when removing this, so that you only remove the necessary part. This seems like a good time to reiterate the importance of making a backup copy of your customization.xml file before you begin editing. Please do it!

Transaction (Process ID #) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

This is an infrastructure-related issue, likely the cause of another process acting on CRM that resulted in a conflict – for example, when multiple users attempt an import at once, or when other integrations/backups are running that result in a conflict for back-end resources.

Fortunately, this issue is typically resolved by just trying it again. However, for an on-premises environment, if you receive this message, we recommend checking with a systems administrator first to determine whether there are any other jobs impacting the SQL environment. It may be in your best interest to delay the import until a time where any running jobs have completed, instead of immediately trying the import again.

The import file is invalid. XSD validation failed with the following error:

You will see this error code if the customization.xml file was edited and contains errors. There could be many causes and variations of this error code. Here is one we saw not too long ago:

The import file is invalid. XSD validation failed with the following error: ‘The ‘labelid’ attribute is invalid – The value ‘{ddcee403-c32b-459f-a4f5- 20e23fb32eb0}’ is invalid according to its datatype ‘FormGuidType’ – The Pattern constraint failed.’. The validation failed at:

As you can see, the GUID shown above is an incorrect format because it contains a space. The obvious lesson here is to always be careful when editing code!

Plug-in assembly does not contain the required types or assembly content cannot be updated.

There are two general causes for this error – either the solution file includes a plugin that is missing some additional items, or something is preventing the target CRM plugin from being updated. If it is the first issue, then the solution file will need to be recreated with the correct components, since the plugin is malformed.

If the plugin is fine, but you are still getting this message, then the problem is that it is failing to update/overwrite the existing plugin. Some options are:

  • Change the existing plugin to “isolation mode = none,” in case it is a security issue.
  • Delete the existing plugin, since you will be reimporting it with the solution file.

Error occurred while fetching the report.

This essentially means CRM is not able to find a place to add the report, so you may have to do some troubleshooting on the report server for an on-premises environment. Some things to check are:

  • Is the report server running?
  • Do other reports work? Be sure to try standard and custom reports.
  • Can the report server URL be accessed from the CRM server?

An alternate solution is to just exclude the report from the solution file.

The ribbon item XX is dependent on ribbon control id=YY

This is another dependency-related error, although this one is specific to the ribbon, and it likely means you have a corrupted form. So, the problem itself is not the solution being imported, but rather the forms/entities the solution touches. In order to fix this, you will need to either edit the ribbon XML or use a ribbon editor, and examine the ribbon control in question.

For this problem, we advise seeking help from a developer (or PowerObjects!) since it may take quite a bit of troubleshooting.

Here’s one example of this error: you have a button with a hide function. Then, somehow, the hide function gets removed, but the button is still there, and it still contains a reference to your function. One possible fix is to recreate the same function, which will enable you to make changes to your button – to either remove it or fix it.

Once this is done, you will be able to make changes to your form (and import your solution).

Required parameter XX is not found for control YY.

This means that within one of the control tags, CRM is expecting another tag, but does not find it. The fix will involve looking further at the message, and adding the necessary tag. Here is one example:

Required parameter QuickForms is not found for control classid=”{5C5600E0-1D6E-4205-A272-BE80DA87FD42}” datafieldname=”po_postedform” disabled=”false”> Edit.

Within the “Interests” definition, it is expecting a tag for but does not find it, and your fix will be to add an additional tag for QuickForms.

The wait operation timed out.

This error indicates that the import took too long and exceeded the time-out value allowed by Dynamics 365. This is performance-related, and may occur when you are importing processes – components that rely on the Microsoft Dynamics CRM Asynchronous service. You may want to simply try the solution import again to see if it works.

Otherwise, for a CRM Online environment, our recommendation is for you to open a ticket with Microsoft.

For an on-premises environment, there are two suggestions:

You may also simply retry the same import at a later time. Note: CRM performance is a separate discussion altogether. Please spend some time researching this before attempting any changes. The following MS blog will provide some background on different time-out values that you can set:

https://blogs.msdn.microsoft.com/crminthefield/2011/06/13/microsoft-dynamics-crm-timeout-settings/

Column names in each table must be unique. Column name X in table Y is specified more than once.

This error is nearly identical to “An item with the same key has already been added,” which we covered earlier. It involves a field that already exists in the target environment but with different capitalization.

A previous blog covered exactly how to solve this particular error:

http://www.powerobjects.com/2013/07/01/importing-solution-in-crm-2011-field-is-not-unique-error/

EntityMap XX – Import: failure. This entity map does not exist on the target system.

This is simply a fancy way of saying you forgot to add a relationship to your solution. To find out more about this error, examine the customization.xml file once more. Let’s say this is the error message you received:

EntityMap po_employment -> incident – Import: FAILURE. This entity map does not exist on the target system.

In the customization.xml file, scroll down until you reach the section, where you may see something like this:


po_employment
incident

This is the piece of the file that CRM reads and does not like, because the relationship itself does not exist in the target environment. The fix is simple: go back to your original solution, and add the missing relationship, as shown below:

022817 1446 Troubleshoo10 Troubleshooting Solution Import Errors

Cannot add a Root Component X of type Y because it is not in the target system.

This is also a dependency-related problem. To investigate, you will need to know to what component it is referring. First, let’s figure out the type. The “type” in this error points to the entity code, which you can learn about here:

https://msdn.microsoft.com/en-us/library/mt608054.aspx

So if your message says it can’t find a root component of type 24, for example, it is referring to a Form; if it is 20, then it’s a Role, and so forth, as described in the linked-to page above.

The root component will be a GUID, which you will need to look for in the customization.xml file. Once you find it, it will take a deeper review of the file to determine how to either remove the component block from the file to re-import it, or to get the missing component into the solution or environment. Sorry, but for this issue, you will need some amount of proficiency in reading XML, since there is no clear-cut solution without doing some investigating.

Should be exactly 1 MessageProcessingStep registered for workflow.

We’ve seen this problem occur a few times, and it is the result of a process activation record that has been duplicated.

To better understand this error, here is a brief overview of how CRM processes (workflows) work:

You create a new workflow, and add some steps. This creates a new record of type process, where category = workflow, and type = definition. Have you ever done an advanced find, and looked at the view called “Activated Processes”?

022817 1446 Troubleshoo11 Troubleshooting Solution Import Errors

This view filters out the other “types” so you only see the actual workflows that have been created. A process can also be of type “template,” which is simply a workflow template. It can also be of type “activation.”

What is a process of type “activation”? Let’s go back to the workflow you created, currently sitting in draft mode. Once you decide to activate it, your workflow will be active. By doing this, you have just created a new process record, where the type = activation. And it is this record that CRM triggers the events defined in the workflow. When you deactivate your process, it also deactivates the other process (type = activation). Umm, yeah, it can be a bit confusing.

Getting back to the error and how it can be fixed – this error may have happened as a result of a bug or glitch that, when a workflow was activated, created more than one process of type “activation.” And it will not allow you to reimport a workflow if there are two or more activation records. In order to fix it, you will have to manually delete the activation records for this workflow so that you have no more than one.

For those in on-premises environments, we have had successful results with a SQL delete command for the activation records. Please note that this is not a Microsoft-supported method, so please take a database backup before doing this, and do it only after work hours.

For CRM online customers, we recommend working with Microsoft to remove the duplicate record, as it cannot be done from the UI.

Invalid link type for system entity cascading actions.

This is an error we saw after an update of CRM 2013, though we have not run into it since. The error refers to the cascade definitions of a relationship, and there exists a nice blog with more information about this specific error, which is caused by a system relationship that changed behavior after an update:

http://cloudxrm.blogspot.com/2014/09/invalid-link-type-for-system-entity.html

No ErrorCode Listed!

We’ve gone through a lot of different Error Codes that can be generated for different issues during solution imports, but there is one more scenario we have yet to cover. Imagine you are doing an import, you receive an error, and you download the log file. When you open it, you see something like this:

022817 1446 Troubleshoo12 Troubleshooting Solution Import Errors

In the “Status” column, you just see Processed, Processed, Processed, and then suddenly Unprocessed. No Failure, nothing to help you. Yikes! What do you do?

Well, the likely cause is the same problem we have covered a few times in the past – when a component you are importing already exists in the target environment, but there is a capitalization mismatch.

The problem with finding out which component is leading to the problem is that you won’t be able to tell by the downloaded log file in Excel. As you can see in Row 16 above, the last ItemType processed was “Chart” from the Incident entity. But that is of no help whatsoever! The only way to find the culprit is to examine each component, one at a time. Ugh.

Trust us, we feel your pain. But one suggestion to make your job easier is to create a new solution in your source CRM, add just one entity, and try to import it. If it succeeds, keep adding additional entities until you receive the error, and then you’ll discover the entity that likely contains a field that is causing the issue. Once you narrow it down to one entity, you will have to do a comparison of all custom fields, looking for any fields that exist in both environments but with mismatched capitalization of the schema.

Most of the problems presented in our examples can be avoided by following some best practices for customizing CRM. For any production CRM environment, our recommendation is to have a separate development/uat environment where you perform your customizations. Once things have been tested, those same customizations should be moved to your production environment via solutions.

This holds true even for small changes, such as increasing a column width of a specific system view or creating a new field. While it may be tempting to create the same change directly in your other environments, which is probably faster than moving it via solutions, we do not recommend this. Doing it this way has the potential for typos and mismatches, especially related to schema capitalization, that will cause problems for future imports. Additionally, other components, such as security roles and connection roles, will have mismatched GUIDs and will also fail your future imports.

Sometimes, editing the customization.xml file is either necessary or is the best approach compared to other methods. This is ok to do – as long as you do it carefully and knowingly. And take backups! It’s so quick to do and takes up very little storage space. Just copy your zip file and save it – now you’re ready to edit!

Most of the problems mentioned in this blog can be avoided by following this process. But if all else fails, feel free to contact our support team – we are ALWAYS happy to help you.

Happy CRM’ing

Let’s block ads! (Why?)

PowerObjects- Bringing Focus to Dynamics CRM

Import all CSV files from a folder with their filenames in Excel

image thumb 30 Import all CSV files from a folder with their filenames in Excel

Last month the Power BI team at Microsoft released an enhanced “combine binaries” experience, that I covered here last month. That new functionality allows you to easily combine multiple CSV files (and other file types) from a folder and utilize their filenames as a column in the final result (which is awesome – if you disagree, you don’t understand it yet, go read the post! wlEmoticon smile 2 Import all CSV files from a folder with their filenames in Excel ).

As I have committed to begin a series on CSVs, it’s time for the second part of the series. I think that today’s blog post will be quite helpful for many of you. So without further ado, let’s begin part #2 of the CSV series.

While we patiently wait to have the same functionality in Excel, in today’s post I will show you how you can create relatively simple query functions to resolve our challenge and combine multiple CSV files from a folder while extracting essential information from their associated filenames. But first, let’s review three possible scenarios, and how to combine CSV files from a folder on each.

If you have a folder with many CSV files that share the exact format, you can could append them all into a single table in Excel file.

Here is an example of such CSV files. Each file contains different Star Wars characters, and we want to append them all. Instead of massive repetitions of copy and paste, or a manual import of each file, here is the Power Query’s game changing way to do it.

clip image015 thumb Import all CSV files from a folder with their filenames in Excel

If you have Excel 2016 In Data tab, click New Query, select From File and then click From Folder. If you are on Excel 2010 or 2013, install Power Query Add-In (download it here) and in the Power Query tab, click From File –> From Folder.

clip image017 thumb Import all CSV files from a folder with their filenames in Excel

Click Browse, and select the folder that contains the CSV files. Click OK in Browser For Folder, then click OK in Folder.

clip image019 thumb Import all CSV files from a folder with their filenames in Excel

If you see this preview window, click Edit.

clip image021 thumb Import all CSV files from a folder with their filenames in Excel

The Query Editor window will appear. This is the main screen to apply transformations on your data. When we’re done, clicking Refresh All in the Data tab (of the ribbon) will automatically load the data from any new and/or modified files and transform it to our desired format.

The next step is where the real magic begins.

(Note from Rob:  yes, we’ve covered parts of this on the blog before, BUT this is a lesson that bears repeating, shouting from the rooftops, and seeing from many perspectives – PLUS it sets up the second half of this post, so this is all very valuable.)

Unfortunately, the portal to the magic land is well hidden. On the left side of the header of the first column you will notice a small button with two arrows. Click on it.

 clip image023 thumb Import all CSV files from a folder with their filenames in Excel

The result: All the CSV files are appended to a single table. We are still inside the Query Editor and can see a preview of the transformation. But there is still some work for us. Notice that each file contains the header names, so the appended result has all the headers as rows in the table.

clip image025 thumb Import all CSV files from a folder with their filenames in Excel

In Transform tab, click Use First Row As Headers.

clip image027 thumb Import all CSV files from a folder with their filenames in Excel

The last step promoted the headers of the first CSV files as the headers of the unified appended table.

Above, we “promoted” the header row from the first file, so that one is taken care of, but we still need to remove the header rows from the other CSV files. To do it, we can apply a simple filter on the first column.

Click the filter button in the header of the first column (In my case column Name), and uncheck the header name in the filter pane (In my case – Name, as highlighted below). When you click OK in the filter pane, the header rows will be filtered out.

clip image029 thumb Import all CSV files from a folder with their filenames in Excel

 

Note: In some unexpected cases, unchecking a value in the filter pane creates a dangerous logic that will yield unexpected results. Make sure you activate the Formula Bar of the Query Editor, and confirm that the formula explicitly excludes the value that you unchecked (Keeping the Formula Bar invisible by default, is one of the most common mistakes you can do in Power Query. Read more here – but short version is that you WANT to see something like ‘[Name] <> “Name” ‘ – if you see ‘[Name] = “R2-D2” or [Name]=”R5-D4” or… ‘ then you probably want to change it to the single “<>” version).

In case your CSV files overlap and you think you have duplicate rows, you can easily remove the duplicates by selecting the columns, whose combined values are unique (In my case it’s column Name). After you select the column/s, right click on one of the headers, and select Remove Duplicates.

clip image031 thumb Import all CSV files from a folder with their filenames in Excel

We are done. We can now click Close & Load in Home tab to close the Query Editor to load the append data into Excel.

clip image033 thumb Import all CSV files from a folder with their filenames in Excel

And here are the results.  Of course, we’d use “Load To” in order to get this into Power Pivot’s data model in most serious cases, but this time I’ll just drop it into Excel for simplicity:

clip image035 thumb Import all CSV files from a folder with their filenames in Excel

(Note from Rob:  YEAH, this is what I’m talking about!  Even I learned some good stuff while reviewing this.  Glad you stuck around? wlEmoticon smile 2 Import all CSV files from a folder with their filenames in Excel)

What if instead we want to append CSV files that share the same format, but each file has a unique context that is crucial our analysis? For example, imagine we have sales data from three regions of our company: London, New-York and Paris.

clip image036 thumb Import all CSV files from a folder with their filenames in Excel

How can we keep the region after we append the data from the three CSV files?

In this section, we will learn how to append the data from all the regions, when the region name is given in the first cell of each file.

image thumb 29 Import all CSV files from a folder with their filenames in Excel

After we select our folder, we should click our magic button again:

clip image040 thumb Import all CSV files from a folder with their filenames in Excel

In the next steps, we will now transform the tables, and move the regions from the first row of each file to a new column, as illustrated here:

clip image042 thumb Import all CSV files from a folder with their filenames in Excel

In Add Column tab, click Conditional Column.

clip image044 thumb Import all CSV files from a folder with their filenames in Excel

In Add Conditional Column dialog, Select Column2 as Column Name, equals as Operator, and “” as Value. Then, change the type of Output to Column, and set Column1 as Output. When you’re done, click OK.

clip image046 thumb Import all CSV files from a folder with their filenames in Excel

Note: The window above doesn’t support blank values as an input – YUCK. So we will need to fix the formula. If this is your first time using the Query Editor, and you don’t see the formula bar, go to View tab and check the Formula Bar checkbox.

In the formula bar, replace the long sequence of double quotes to two double quotes “”.

Here is the original formula (before the change):

clip image048 thumb Import all CSV files from a folder with their filenames in Excel

Here is the modified formula:

clip image050 thumb Import all CSV files from a folder with their filenames in Excel

In the next step, we will fill down the region values, so each row will include its corresponding region.

Select column Custom and right click its header. Select Fill and then select Down.

clip image052 thumb Import all CSV files from a folder with their filenames in Excel

Click on the filter icon of Column2 and select Remove Empty.

clip image054 thumb Import all CSV files from a folder with their filenames in Excel

In Transform tab, click Use First Row As Headers.

clip image056 thumb Import all CSV files from a folder with their filenames in Excel

Now we can remove all the header rows. Click the filter icon on the first column (In my case – Date), scroll down till you see the column name as a value (In my case – Date) and uncheck it, then click OK.

clip image058 thumb Import all CSV files from a folder with their filenames in Excel

We can rename the last column to City or Region.

clip image060 thumb Import all CSV files from a folder with their filenames in Excel

In the last two steps we can change the column types of Sales and Date, by clicking the small ABC icon in the headers and select Decimal Number for Sales and Date for Date.

clip image062 thumb Import all CSV files from a folder with their filenames in Excel

clip image064 thumb Import all CSV files from a folder with their filenames in Excel

That’s it. We can now close the Query Editor. In Home tab click Close & Load.

We were able to append all the sales data and keep the region/city information. You will find this method useful in many scenarios where your CSV files contain meta-data before the actual tables. Copying the data to a new custom column, and filling it down will do the trick.

clip image066 thumb Import all CSV files from a folder with their filenames in Excel

(This is the part that is just like my previous post, but we’re doing it in Excel rather than Power BI)

So what should we do if our meta-data (like region names) isn’t available in the CSV itself, but in the filename. In our example, all the CSV files contain the sales data, and the region/city name is in the filename, as shown here:

clip image068 thumb Import all CSV files from a folder with their filenames in Excel

Till the improved Combine Binaries feature in Power BI is released in Excel, we cannot click the Combine Binaries button in the header of column Content to append the data without losing the crucial information in the filenames.

Here is the solution:

After you import the folder, follow the steps below.

Select the first two columns Content and Name. Right click on one of the headers and select Remove Other Columns.

clip image070 thumb Import all CSV files from a folder with their filenames in Excel

In Home tab, click New Source, then select Other Sources and select Blank Query.

clip image072 thumb Import all CSV files from a folder with their filenames in Excel

Rename the new query to LoadCSV.

Click Advanced Editor.

clip image074 thumb Import all CSV files from a folder with their filenames in Excel

Paste the following code in the Advanced Editor and click Done.

(content as binary, filename) =>

let

    #”Imported CSV” = Csv.Document(content,[Encoding=1252, QuoteStyle=QuoteStyle.None]),

    #”Promoted Headers” = Table.PromoteHeaders(#”Imported CSV”),

    #”Added Custom” = Table.AddColumn(#”Promoted Headers”, “filename”, each filename)

in

#”Added Custom”

clip image076 thumb Import all CSV files from a folder with their filenames in Excel

Note: Keep the code above. You will be able to reuse it whenever you need to append CSV files and their filenames from a folder.

In Add Column tab, click Invoke Custom Function.

clip image078 thumb Import all CSV files from a folder with their filenames in Excel

In Invoke Custom Function dialog, select LoadCSV as Function query.

 clip image080 thumb Import all CSV files from a folder with their filenames in Excel

Select column Content as content, select Column Name as the type of filename (optional).

clip image082 thumb Import all CSV files from a folder with their filenames in Excel

Select Name as filename (optional), and click OK.

clip image084 thumb Import all CSV files from a folder with their filenames in Excel

We can now remove the first two columns, by selecting the third column, right clicking on its header and selecting Remove Other Columns.

clip image086 thumb Import all CSV files from a folder with their filenames in Excel

There is another magic button that we can use in the header of column LoadCSV. This button will expand all the tables we extracted from the CSV files, and will transform them into a single table – with the region/city context intact. Click on the icon (highlighted below), uncheck Use original column name as prefix, and click OK.

clip image088 thumb Import all CSV files from a folder with their filenames in Excel

We can now manipulate the filename values to represent the cities by removing the extension from the filename).

To remove the .csv extension and keep the city names, select column filename and right click on its header. Then, select Replace Values..

clip image090 thumb Import all CSV files from a folder with their filenames in Excel

In Replace Values dialog, set .csv as Value To Find, and click OK. (Note that we didn’t provide any text to replace with, as we want to delete this extension).

clip image092 thumb Import all CSV files from a folder with their filenames in Excel

We can now rename the column filename to City, and change the types of Date and Sales as we did in the previous section. Finally, clicking Close & Load in Home tab will provide the expected results.

clip image094 thumb Import all CSV files from a folder with their filenames in Excel

From now on, any additional CSV files that you’ll add in the folder will be automatically appended when you click Refresh All in Data tab.

Conclusions

Following this blog post, I hope that you’ll never again manually append multiple CSV files into your worksheet. The Power Query way is relatively easy, and it allows you to refresh the data when new CSV files come in.

Would you like to improve your Power Query skills? Follow my new series on 10 common mistakes you do in Power BI and Power Query, and and how to avoid the Pitfalls here.

Let’s block ads! (Why?)

PowerPivotPro

Import all CSV files from a folder with their filenames in Power BI

image thumb 1 Import all CSV files from a folder with their filenames in Power BI

Imagine Being Able to Just Collect Exported CSV/TXT/Excel Files in One Folder,
Name them Properly/Consistently, and Magically Combine them Into a Single Smart Table

You know my old joke, that “Export to Excel” is the third-most-common button in data applications behind OK and Cancel?  Well it’s not really a joke – the world RUNS on export to Excel, and in most cases, the export button actually produces a text file – a .CSV (Comma Separated Values) or .TXT (Text – typically tab-delimited).

Dealing with those exported files has been VERY labor-intensive in the past, but there’s great news:  the newer tools from Microsoft are AMAZING at handling these exported files and making terrific sense of them – in an eyeblink.

image thumb 2 Import all CSV files from a folder with their filenames in Power BISeriously, if you’re toiling away today, doing ANY of the following:

  1. Creating a new version of a spreadsheet EVERY time you export a CSV
  2. Struggling with data being treated as text in Excel when you need it to be a number
  3. Lacking any ability to trend/compare the exported data over time
  4. Just generally spending much more time getting the right data connected with the right analyses

…then you need to know that your life can be much better, TODAY, just by using the newest capabilities from Microsoft.

Gil is here to help.  Take it away, sir…

The Power BI team has recently released an enhanced “combine binaries” experience as part of November 2016 update to Power BI Desktop. (Jargon Alert:  “Combine Binaries” is a scary term.  Instead it should be named “Magically combine multiple files together into one table and make me SUPER happy.”)  The improved experience can drastically help you to import multiple Excel or other files from a folder and avoid writing advanced query functions. But today we will focus on a specific scenario, which is so common that it deserves this special post – Handling CSV files.

In fact, today’s blog post is actually the first post in “The CSV Series”. I hope you will enjoy it. To celebrate the November update of Power BI Desktop, we will review the improved experience, and will walk you through one of the most common scenarios that is now so easy to implement – Importing multiple CSV files from a folder, including parts of their filenames.

NOTE:  for now this “enhanced” capability is only available in Power BI, but Microsoft has been pretty consistent about bringing these benefits to Excel.  Stay tuned for my next post in this series, to learn how you can do it in Excel, who lacks the Power BI improvement (at least for the time being. Hopefully the improvement will find its way to Excel soon).

It all starts with a somewhat-hidden yet magical button – You can see it highlighted in the following screenshot:

image thumb Import all CSV files from a folder with their filenames in Power BI

The Button Looks Like an Elevator Going Down
(But Really It’s “Magically combine multiple files together into one table and make me SUPER Happy”)

Prior to November update, clicking the magical button (AKA combine binaries) was only effective when you had a folder of CSV files. All CSV files were appended into a single file. There were only two caveats:

1- It only worked on CSV files (If you had other file types, such as Excel workbooks, you had to create a query function to process a single file and then invoke the function on all files. This is now done automatically for you in the November update of Power BI).

2- If your CSV files contained crucial meta data in the filename, you “lost” that data after clicking the combine binaries button.

For example: In the screenshot above you can see that we have the cities information in column Name. Clicking the combine binaries will append our sales data from each city, but we will no longer be able to associate the data to the city, if the city is not mentioned in the CSV files. This limitation was so common, and for many users, who weren’t familiar with query functions or were reluctant to manipulate the Power Query formulas, this magical button had become useless.

So today, with the new functionality in Power BI Desktop, you can append all CSV files together and keep their filenames in the appended results. Let’s review the new experience on a folder of CSV files.

Open Power BI Desktop and click Get Data icon.

Screenshot 49 thumb 1 Import all CSV files from a folder with their filenames in Power BI

Select File and then select Folder and click Connect.

clip image003 thumb 1 Import all CSV files from a folder with their filenames in Power BI

Click Browse to browse to a folder with CSV files. Select the relevant folder and click OK. Click OK again in the Folder dialog.

Screenshot 107 thumb Import all CSV files from a folder with their filenames in Power BI

In an extra-probably-unnecessary step will will see the content of the selected folder. Click OK.

Screenshot 108 thumb Import all CSV files from a folder with their filenames in Power BI

In the Query Editor you will notice the small icon in the header of the first column. This is our Combine Binaries button. Click on it, and the magic will happen.

clip image005 thumb 1 Import all CSV files from a folder with their filenames in Power BI

With this update of Power BI Desktop, a new step is introduced after you click the Combine Binaries. You will see a preview of the first file (For example, with Excel workbooks, you will get to see a navigator window that will allow you to choose which worksheet or table to load). For our CSV scenario, you will notice the CSV dialog.

In this dialog you will see a preview of the data from the first CSV file. Under the hoods, Power BI will automatic detect which delimiter to use, and may even promote the first row as headers. You can manually change the delimiter, or define how Power BI should handle data types. You can set it to automatically detect data types based on first 200 rows, or the entire dataset or you can even opt out the detection of data types.

clip image007 thumb 1 Import all CSV files from a folder with their filenames in Power BI

The results are awesome – We can now import and combine all CSV files from a folder, and include their filename in the appended table.

You can see that the filename is now located in the new column Source.Name.

clip image011 thumb 1 Import all CSV files from a folder with their filenames in Power BI

You may notice that Power BI Desktop creates new artifacts on the Queries pane. If you don’t consider yourself an advanced Power Query user, I suggest that you just ignore those new “strange creatures”. Imagine that that they don’t exist.

But if you insist on scrutinizing your Queries pane,  follow my blog post here to learn more about these artifacts.

Sometimes, your exported data does NOT tell you “where” it came from.  For example, in our data here, each CSV was created by filtering some other application by city and then pressing Export – but the resulting report/export did NOT include the City as a column!  When we saved the CSV, we re-named it “London.csv” etc. so that at least the filename “remembers” where we got it from.  (Another common example is the same exact export run over different timeframes rather than different locations – one for January, one for February, etc. – in which case the technique below will also be 100% applicable).

So our next step is to extract the city from column Source.Name. It’s pretty simple.

Select the column Source.Name, and right click on its header, then select Replace Values.

Screenshot 109 Import all CSV files from a folder with their filenames in Power BI

In the Replace Values dialog, set .csv as Value To Find, and click OK. This step will remove the extension (*.csv) from all the values in column Source.Name, leaving the cities intact.

Screenshot 110 thumb Import all CSV files from a folder with their filenames in Power BI

Now that we have extracted the cities from our filenames, we can rename the column to Region (Renaming is simple. Same way you rename a filename in a folder).

Screenshot 111 Import all CSV files from a folder with their filenames in Power BI

To prove that we were able to combine all the files, and have filenames as regions, let’s sort our data by Product. To sort the data by Product, click the sort button. It’s similar to sorting tables in Excel.

Do you see all the cities in column Region? Isn’t it awesome?

Screenshot 112 Import all CSV files from a folder with their filenames in Power BI

Before we close the Query Editor, it’s important to ensure that our numeric/date columns have the right data type. In our example, we can change column Sales to Decimal Number, and column Date to Date.

Screenshot 116 Import all CSV files from a folder with their filenames in Power BI

That’s it. Clicking Close & Apply in Home tab, will load all our CSV files and their filenames from the folder. Here is an example for a simple report that I’ve built.

Screenshot 118 thumb Import all CSV files from a folder with their filenames in Power BI

Now, for the sake of completeness and awesomeness, let’s check out what happen to our report, if we add new data for Chicago region by adding a new CSV file Chicago.csv to our folder.

Screenshot 113 thumb Import all CSV files from a folder with their filenames in Power BI

Clicking Refresh on the report will immediately append the data from the new CSV file along with the other CSV files in our folder, and include region Chicago in our report. And this is real magic.

Conclusions

Following this blog post, I hope you are encouraged to download the latest Power BI Desktop and import multiple CSV files from a folder. You can now easily include the CSV filenames to refine the appended table with supplementary information.

I hope that Microsoft will soon update Excel with this improved experience (they usually do).

Stay tuned for my next post in “The CSV Series”.

Let’s block ads! (Why?)

PowerPivotPro

Testing Applications that Import Excel Files into Microsoft Dynamics CRM

The ability to import data from spreadsheets is a powerful and useful functionality in Microsoft Dynamics CRM. It allows business users to collect massive amounts of data all into Microsoft Dynamics CRM such that users are able to keep track of customer information all into one place. This is because in the CRM game, information is power and data provides powerful business intelligence.

A common functionality regarding Microsoft Dynamics 365 is importing CSV files into the client. The Microsoft stack offers Microsoft Excel, a popular spreadsheet application tool. It allows users to store data into spreadsheets, and provides various useful tools such as applying formulas, creating visual graphs and statistical analysis. Many users and businesses are familiar with Microsoft Excel and have Microsoft Excel as their ‘go-to’ spreadsheet application tool. Users having the familiarity of using Excel for data entry and data analysis while also using Excel in their everyday business. This means the ability to import Excel csv files into Microsoft Dynamics CRM is fundamental.

Third applications are used by businesses to gather information from its customers. Not all businesses will use the ‘Import Data’ tool that is provided out-of-the-box by Microsoft Dynamics 365.  Third party applications can provide a more seamless and integrated approach to gather data into Excel files and importing these into Dynamics CRM. In the context of this blog we store information into Excel files, such that third party application can import Excel files into Dynamics CRM. Seamless integration of data importing from these third party apps into Dynamics CRM is key. As testers, we must ensure that importing Excel files into Microsoft Dynamics CRM is well tested and functional.

The most important thing to check and test with importing Excel files is data mapping and integration. Because what is the point of using our data if it does not provide useful and contextual information to its user? To ensure data accuracy, quality and integrity, we must ensure that the import functionality is reliable and tested for data mapping into CRM.

image thumb Testing Applications that Import Excel Files into Microsoft Dynamics CRM

When your data mappings get messy during imports – not exactly useful is it?

This means primarily checking if the correct entity records are created, and checking if the data is correctly mapped to the fields. The Advanced Find functionality in Dynamics CRM is useful to test for data mappings. Instead of individually navigating through each entity record and checking each record fields (which is painstakingly inefficient), we can use a custom view in Advanced Find to view the data in the columns. We can create a custom view, alter the column order identical to the column headers in our Excel files, and compare whether the correct data under a column gets mapped to the appropriate fields. This way we can view a large amount of records to eyeball and validate, thus easily compare selective records.

image thumb 1 Testing Applications that Import Excel Files into Microsoft Dynamics CRM

Using Advanced Find to easily eyeball and validate data mappings for CRM records with the imported Excel spreadsheet. We can easily compare by altering the columns in our view to mimic the columns in the imported Excel spreadsheet.

Consider the following real-world scenario to test: given a console application, it prompts the user to import data file pairs into Microsoft Dynamics CRM.

image thumb 2 Testing Applications that Import Excel Files into Microsoft Dynamics CRM

From the above example, we can draw up a few test cases that can hopefully give you some ways in how to test the import functionality of Excel files into Microsoft Dynamics CRM.

Again, as testers, we assume that the system user can make mistakes, especially when it comes to dealing with large amounts of data entry. Users can be overwhelmed by the massive stream of information and the mental load regarding this means that mistakes can happen. Consider the following to test for the Excel file to be exported:

Consider User Mistakes in Excel Data Entry:

-    blank data file – will it prompt the user if there are contents in the Excel file to import?
-    empty columns in the Excel spreadsheet
-    empty rows in the Excel spreadsheet – does an empty row still create a record when imported in CRM?

image thumb 3 Testing Applications that Import Excel Files into Microsoft Dynamics CRM
Make sure that an empty line in your Excel spreadsheet does not create a default record in CRM (unless it is intended functionality). Records have been blurred out for demonstration purposes.

Validation of File Format when Reading the File to Import:

-    validating the correct file names that identify a file to be imported
-    correct file format to import (csv file type)
-    missing or incorrect file names to identify a file to import
-    duplicate detection of files to be imported
-    consider access verification of the folder location of file to import

Field Mappings:

-    missing column header on a column with data
-    duplicate column headers in an Excel spreadsheet
-    Are the appropriate CRM entities created correctly in CRM?
-    Are the CRM fields mapped correctly in CRM with the associated column header in the Excel file?

Relationships:

-    Are Excel data linked with other data from another Excel file/ sheet?
-    Does an Excel file to be imported require another linked Excel file/ spreadsheet before executing the import?

Another important feature to test is whether we want to bring in user interaction and friendliness into testing. As testers we want to simulate what the user is experiencing when using the system. As such, it is important to assume users do not have familiarity with using the system application and to always give user feedback regarding required actions, import process actions, and processing status updates. We must consider the following test cases for the Excel file to be exported:

Required Actions from the System User:

-    Does it require the user to manually choose the files to be imported?
-    Does it require any validation from the user to execute the import process? E.g. “Do you want to import the following import files: Y/N?”
-    Will it validate if the required actions from the user is correct? E.g. “Do you want to import the following import files: Y/N?”, expecting a ‘Y’ or ‘N’ input but entering an invalid text or a number

User Friendliness:

-    Does the user get updated by the system on what is going on and what the system status is?  e.g. connected to CRM, import data located at …., currently processing the following csv file, import process completed
-    Is the language simple enough for the user to understand and interpret? Will there be any jargon that can be confusing to the user?
-    Are the system statuses given by the system informative and useful to the user?

A good system to test must also be able to handle what happens when things don’t go as expected. The ability to detect an error and execute error handlers is key in testing for a robust system. We also want to think about how an error is not only informative to the system user, but to the developers as well. This is to ensure that:

Error rollback/ error handling during import, information to the user and to the developers/ support team

-    Will the user be notified of any errors and what the reason is during the import process?
-    Test how an error during the import process is handled by the system and rollback processes. E.g. The import process already creates the records in CRM, then midway through the import process error has occurred. A rollback method could be to stop failed import process and delete the created records.
-    A log file generated can be useful for testing purposes to detect any implementation errors, error handling such that we can find more bugs, record all errors and so that developers can further improve the system

We take into account other business requirements, specifications or functionality that is specific to the system and the project we are testing. We also want to consider a stress test for dealing with imports of large data files. Here we can possibly analyse and test functionality, limitations and the speed of the importing process and if the system can handle or break. Through stress testing we can identify system stability, reliability and detect other modes of failure dealing with larger data files

Above are some of the ways I approached my testing regarding an import functionality into Dynamics 365. Testing a functionality that deals with streams of data and information needs to be considered with certain scenarios involving the system user and system integration. A well tested and well thought out test execution process is essential to creating an accurate and robust system to import data into Dynamics CRM. While every test execution is different for every project and has many different specifications, requirements and functionalities, hopefully you can draw a few ideas into how to build your test cases based on the experience of my own testing. Let’s get testing!

Let’s block ads! (Why?)

Magnetism Solutions Dynamics CRM Blog

Testing Applications that Import Excel Files into Microsoft Dynamics CRM

The ability to import data from spreadsheets is a powerful and useful functionality in Microsoft Dynamics CRM. It allows business users to collect massive amounts of data all into Microsoft Dynamics CRM such that users are able to keep track of customer information all into one place. This is because in the CRM game, information is power and data provides powerful business intelligence.

A common functionality regarding Microsoft Dynamics 365 is importing CSV files into the client. The Microsoft stack offers Microsoft Excel, a popular spreadsheet application tool. It allows users to store data into spreadsheets, and provides various useful tools such as applying formulas, creating visual graphs and statistical analysis. Many users and businesses are familiar with Microsoft Excel and have Microsoft Excel as their ‘go-to’ spreadsheet application tool. Users having the familiarity of using Excel for data entry and data analysis while also using Excel in their everyday business. This means the ability to import Excel csv files into Microsoft Dynamics CRM is fundamental.

Third applications are used by businesses to gather information from its customers. Not all businesses will use the ‘Import Data’ tool that is provided out-of-the-box by Microsoft Dynamics 365.  Third party applications can provide a more seamless and integrated approach to gather data into Excel files and importing these into Dynamics CRM. In the context of this blog we store information into Excel files, such that third party application can import Excel files into Dynamics CRM. Seamless integration of data importing from these third party apps into Dynamics CRM is key. As testers, we must ensure that importing Excel files into Microsoft Dynamics CRM is well tested and functional.

The most important thing to check and test with importing Excel files is data mapping and integration. Because what is the point of using our data if it does not provide useful and contextual information to its user? To ensure data accuracy, quality and integrity, we must ensure that the import functionality is reliable and tested for data mapping into CRM.

image thumb Testing Applications that Import Excel Files into Microsoft Dynamics CRM

When your data mappings get messy during imports – not exactly useful is it?

This means primarily checking if the correct entity records are created, and checking if the data is correctly mapped to the fields. The Advanced Find functionality in Dynamics CRM is useful to test for data mappings. Instead of individually navigating through each entity record and checking each record fields (which is painstakingly inefficient), we can use a custom view in Advanced Find to view the data in the columns. We can create a custom view, alter the column order identical to the column headers in our Excel files, and compare whether the correct data under a column gets mapped to the appropriate fields. This way we can view a large amount of records to eyeball and validate, thus easily compare selective records.

image thumb 1 Testing Applications that Import Excel Files into Microsoft Dynamics CRM

Using Advanced Find to easily eyeball and validate data mappings for CRM records with the imported Excel spreadsheet. We can easily compare by altering the columns in our view to mimic the columns in the imported Excel spreadsheet.

Consider the following real-world scenario to test: given a console application, it prompts the user to import data file pairs into Microsoft Dynamics CRM.

image thumb 2 Testing Applications that Import Excel Files into Microsoft Dynamics CRM

From the above example, we can draw up a few test cases that can hopefully give you some ways in how to test the import functionality of Excel files into Microsoft Dynamics CRM.

Again, as testers, we assume that the system user can make mistakes, especially when it comes to dealing with large amounts of data entry. Users can be overwhelmed by the massive stream of information and the mental load regarding this means that mistakes can happen. Consider the following to test for the Excel file to be exported:

Consider User Mistakes in Excel Data Entry:

-    blank data file – will it prompt the user if there are contents in the Excel file to import?
-    empty columns in the Excel spreadsheet
-    empty rows in the Excel spreadsheet – does an empty row still create a record when imported in CRM?

image thumb 3 Testing Applications that Import Excel Files into Microsoft Dynamics CRM
Make sure that an empty line in your Excel spreadsheet does not create a default record in CRM (unless it is intended functionality). Records have been blurred out for demonstration purposes.

Validation of File Format when Reading the File to Import:

-    validating the correct file names that identify a file to be imported
-    correct file format to import (csv file type)
-    missing or incorrect file names to identify a file to import
-    duplicate detection of files to be imported
-    consider access verification of the folder location of file to import

Field Mappings:

-    missing column header on a column with data
-    duplicate column headers in an Excel spreadsheet
-    Are the appropriate CRM entities created correctly in CRM?
-    Are the CRM fields mapped correctly in CRM with the associated column header in the Excel file?

Relationships:

-    Are Excel data linked with other data from another Excel file/ sheet?
-    Does an Excel file to be imported require another linked Excel file/ spreadsheet before executing the import?

Another important feature to test is whether we want to bring in user interaction and friendliness into testing. As testers we want to simulate what the user is experiencing when using the system. As such, it is important to assume users do not have familiarity with using the system application and to always give user feedback regarding required actions, import process actions, and processing status updates. We must consider the following test cases for the Excel file to be exported:

Required Actions from the System User:

-    Does it require the user to manually choose the files to be imported?
-    Does it require any validation from the user to execute the import process? E.g. “Do you want to import the following import files: Y/N?”
-    Will it validate if the required actions from the user is correct? E.g. “Do you want to import the following import files: Y/N?”, expecting a ‘Y’ or ‘N’ input but entering an invalid text or a number

User Friendliness:

-    Does the user get updated by the system on what is going on and what the system status is?  e.g. connected to CRM, import data located at …., currently processing the following csv file, import process completed
-    Is the language simple enough for the user to understand and interpret? Will there be any jargon that can be confusing to the user?
-    Are the system statuses given by the system informative and useful to the user?

A good system to test must also be able to handle what happens when things don’t go as expected. The ability to detect an error and execute error handlers is key in testing for a robust system. We also want to think about how an error is not only informative to the system user, but to the developers as well. This is to ensure that:

Error rollback/ error handling during import, information to the user and to the developers/ support team

-    Will the user be notified of any errors and what the reason is during the import process?
-    Test how an error during the import process is handled by the system and rollback processes. E.g. The import process already creates the records in CRM, then midway through the import process error has occurred. A rollback method could be to stop failed import process and delete the created records.
-    A log file generated can be useful for testing purposes to detect any implementation errors, error handling such that we can find more bugs, record all errors and so that developers can further improve the system

We take into account other business requirements, specifications or functionality that is specific to the system and the project we are testing. We also want to consider a stress test for dealing with imports of large data files. Here we can possibly analyse and test functionality, limitations and the speed of the importing process and if the system can handle or break. Through stress testing we can identify system stability, reliability and detect other modes of failure dealing with larger data files

Above are some of the ways I approached my testing regarding an import functionality into Dynamics 365. Testing a functionality that deals with streams of data and information needs to be considered with certain scenarios involving the system user and system integration. A well tested and well thought out test execution process is essential to creating an accurate and robust system to import data into Dynamics CRM. While every test execution is different for every project and has many different specifications, requirements and functionalities, hopefully you can draw a few ideas into how to build your test cases based on the experience of my own testing. Let’s get testing!

Let’s block ads! (Why?)

Magnetism Solutions Dynamics CRM Blog

Testing Applications that Import Excel Files into Microsoft Dynamics CRM

The ability to import data from spreadsheets is a powerful and useful functionality in Microsoft Dynamics CRM. It allows business users to collect massive amounts of data all into Microsoft Dynamics CRM such that users are able to keep track of customer information all into one place. This is because in the CRM game, information is power and data provides powerful business intelligence.

A common functionality regarding Microsoft Dynamics 365 is importing CSV files into the client. The Microsoft stack offers Microsoft Excel, a popular spreadsheet application tool. It allows users to store data into spreadsheets, and provides various useful tools such as applying formulas, creating visual graphs and statistical analysis. Many users and businesses are familiar with Microsoft Excel and have Microsoft Excel as their ‘go-to’ spreadsheet application tool. Users having the familiarity of using Excel for data entry and data analysis while also using Excel in their everyday business. This means the ability to import Excel csv files into Microsoft Dynamics CRM is fundamental.

Third applications are used by businesses to gather information from its customers. Not all businesses will use the ‘Import Data’ tool that is provided out-of-the-box by Microsoft Dynamics 365.  Third party applications can provide a more seamless and integrated approach to gather data into Excel files and importing these into Dynamics CRM. In the context of this blog we store information into Excel files, such that third party application can import Excel files into Dynamics CRM. Seamless integration of data importing from these third party apps into Dynamics CRM is key. As testers, we must ensure that importing Excel files into Microsoft Dynamics CRM is well tested and functional.

The most important thing to check and test with importing Excel files is data mapping and integration. Because what is the point of using our data if it does not provide useful and contextual information to its user? To ensure data accuracy, quality and integrity, we must ensure that the import functionality is reliable and tested for data mapping into CRM.

image thumb Testing Applications that Import Excel Files into Microsoft Dynamics CRM

When your data mappings get messy during imports – not exactly useful is it?

This means primarily checking if the correct entity records are created, and checking if the data is correctly mapped to the fields. The Advanced Find functionality in Dynamics CRM is useful to test for data mappings. Instead of individually navigating through each entity record and checking each record fields (which is painstakingly inefficient), we can use a custom view in Advanced Find to view the data in the columns. We can create a custom view, alter the column order identical to the column headers in our Excel files, and compare whether the correct data under a column gets mapped to the appropriate fields. This way we can view a large amount of records to eyeball and validate, thus easily compare selective records.

image thumb 1 Testing Applications that Import Excel Files into Microsoft Dynamics CRM

Using Advanced Find to easily eyeball and validate data mappings for CRM records with the imported Excel spreadsheet. We can easily compare by altering the columns in our view to mimic the columns in the imported Excel spreadsheet.

Consider the following real-world scenario to test: given a console application, it prompts the user to import data file pairs into Microsoft Dynamics CRM.

image thumb 2 Testing Applications that Import Excel Files into Microsoft Dynamics CRM

From the above example, we can draw up a few test cases that can hopefully give you some ways in how to test the import functionality of Excel files into Microsoft Dynamics CRM.

Again, as testers, we assume that the system user can make mistakes, especially when it comes to dealing with large amounts of data entry. Users can be overwhelmed by the massive stream of information and the mental load regarding this means that mistakes can happen. Consider the following to test for the Excel file to be exported:

Consider User Mistakes in Excel Data Entry:

-    blank data file – will it prompt the user if there are contents in the Excel file to import?
-    empty columns in the Excel spreadsheet
-    empty rows in the Excel spreadsheet – does an empty row still create a record when imported in CRM?

image thumb 3 Testing Applications that Import Excel Files into Microsoft Dynamics CRM
Make sure that an empty line in your Excel spreadsheet does not create a default record in CRM (unless it is intended functionality). Records have been blurred out for demonstration purposes.

Validation of File Format when Reading the File to Import:

-    validating the correct file names that identify a file to be imported
-    correct file format to import (csv file type)
-    missing or incorrect file names to identify a file to import
-    duplicate detection of files to be imported
-    consider access verification of the folder location of file to import

Field Mappings:

-    missing column header on a column with data
-    duplicate column headers in an Excel spreadsheet
-    Are the appropriate CRM entities created correctly in CRM?
-    Are the CRM fields mapped correctly in CRM with the associated column header in the Excel file?

Relationships:

-    Are Excel data linked with other data from another Excel file/ sheet?
-    Does an Excel file to be imported require another linked Excel file/ spreadsheet before executing the import?

Another important feature to test is whether we want to bring in user interaction and friendliness into testing. As testers we want to simulate what the user is experiencing when using the system. As such, it is important to assume users do not have familiarity with using the system application and to always give user feedback regarding required actions, import process actions, and processing status updates. We must consider the following test cases for the Excel file to be exported:

Required Actions from the System User:

-    Does it require the user to manually choose the files to be imported?
-    Does it require any validation from the user to execute the import process? E.g. “Do you want to import the following import files: Y/N?”
-    Will it validate if the required actions from the user is correct? E.g. “Do you want to import the following import files: Y/N?”, expecting a ‘Y’ or ‘N’ input but entering an invalid text or a number

User Friendliness:

-    Does the user get updated by the system on what is going on and what the system status is?  e.g. connected to CRM, import data located at …., currently processing the following csv file, import process completed
-    Is the language simple enough for the user to understand and interpret? Will there be any jargon that can be confusing to the user?
-    Are the system statuses given by the system informative and useful to the user?

A good system to test must also be able to handle what happens when things don’t go as expected. The ability to detect an error and execute error handlers is key in testing for a robust system. We also want to think about how an error is not only informative to the system user, but to the developers as well. This is to ensure that:

Error rollback/ error handling during import, information to the user and to the developers/ support team

-    Will the user be notified of any errors and what the reason is during the import process?
-    Test how an error during the import process is handled by the system and rollback processes. E.g. The import process already creates the records in CRM, then midway through the import process error has occurred. A rollback method could be to stop failed import process and delete the created records.
-    A log file generated can be useful for testing purposes to detect any implementation errors, error handling such that we can find more bugs, record all errors and so that developers can further improve the system

We take into account other business requirements, specifications or functionality that is specific to the system and the project we are testing. We also want to consider a stress test for dealing with imports of large data files. Here we can possibly analyse and test functionality, limitations and the speed of the importing process and if the system can handle or break. Through stress testing we can identify system stability, reliability and detect other modes of failure dealing with larger data files

Above are some of the ways I approached my testing regarding an import functionality into Dynamics 365. Testing a functionality that deals with streams of data and information needs to be considered with certain scenarios involving the system user and system integration. A well tested and well thought out test execution process is essential to creating an accurate and robust system to import data into Dynamics CRM. While every test execution is different for every project and has many different specifications, requirements and functionalities, hopefully you can draw a few ideas into how to build your test cases based on the experience of my own testing. Let’s get testing!

Let’s block ads! (Why?)

Magnetism Solutions Dynamics CRM Blog

The Hidden Truth about CRM Data Import Files

There is no denying that when working with importable data in CRM, data integrity is of the highest importance. Microsoft has gone a long way to help make importable templates much more manageable and safe for end users.  They have added a number of user friendly measures, such as tool tips that tell you the field type and set length, or minimum and maximum values of data for that field, including stopping measures if you exceed one of the rules.
Image 1 The Hidden Truth about CRM Data Import Files

For Lookups, they give you the tool tip that records must exist prior to importing them, and for Option Set Items, you are only able to select the available values that exist in the system:

Image 3 The Hidden Truth about CRM Data Import Files

This isn’t a revolutionary development… the CRM developers just learned to leverage the Microsoft Excel built-in Data Validation rules to enhance the user experience. For those of you that do not know where to find these, they can be navigated to in Excel by going to Data -> Data Validation -> Data Validation…

Image 4 The Hidden Truth about CRM Data Import Files

And here you can see the rule that was set for this particular text column:

Image 5 The Hidden Truth about CRM Data Import Files

If you look behind the curtain, you will see the rule for Option Set values as such:

Image 6 e1471379383168 The Hidden Truth about CRM Data Import Files

This format is shouldn’t come as a surprise as we need to get the data from somewhere to populate the drop-down list.  But that source…… hiddenSheet!$ A$ 17:$ Q$ 17

 

Where can we find this hiddenSheet?

Typically, you can find hidden worksheets by just selecting the worksheet tab at the bottom and choose the unhide option.  But for some reason, that option is not showing that anything is hidden.

Image 7 1 The Hidden Truth about CRM Data Import Files

So, this sheet has to be somewhere right? The answer is hidden in an advanced feature, where few tend to look: under View Code:

Image 8 1 The Hidden Truth about CRM Data Import Files

This will pop up a separate VBA page that may look scary to a standard Microsoft Office user, but fear not, we can help you find what you need as long as you follow along closely.

Once inside the VBA editor, you will need to view the project files.  To find them, Click View -> Project Explorer.

Image 10 1 e1471379254623 The Hidden Truth about CRM Data Import Files

Once the Project Explorer comes up, look what we find.  It’s our hidden DataSheet, and if we look at the Visibility status, it was set to a 2 – xlSheetVeryHidden (I’d say so).

Image 11 1 The Hidden Truth about CRM Data Import Files

To get that hidden sheet to appear, we want to change the Visibility value to 1 – xlSheetVisible.  You don’t need to save, just click back on your Microsoft Excel Document.

Image 12 1 The Hidden Truth about CRM Data Import Files

Once you are done in here, go back to your VBA Editor, and set the visibility back to 2 – xlSheetVeryHidden and close the extra window without saving.

So, you may be wondering: why would someone ever want to do this?

For me, it’s the same reason that the CRM developers went through all the work in the first place…to improve data integrity and make the end users life just a little easier.

When I am sending a data import template to my customers, I want to make this process as simple and easy as possible.  I will commonly take the lookups that have 10 items or less that would be used, add them to the hiddenSheet file. Then, I create the Data Validation rule for that lookup column.  This gives the user the ability to select the record from a dropdown and validate that it matches what exist already in CRM. When matching lookups, it must be precise as far as spelling, white spaces, abbreviations, punctuation, etc…  If I can make this easier, by already having the options as they exist in CRM available to the end user, I know I have improved the data integrity.  Additionally, if they are populating the file and something is missing from the list, we can be aware of that ahead of time to get the record added in CRM, so that it does not fail our import and have to be repeated multiple times.

This is certainly something that may only be relevant to a select crowd as it is more advanced, but in finding my way through this, it was not as easy as a quick internet search.  So in writing this up, I hope that a few will find this useful, save them time and that they can make someone else’s life just a little easier.  To me, that is a win, win win….

Written by Bryan Page, Developer at Rockton Software, a Microsoft Dynamics CRM Add-On Partner.

Let’s block ads! (Why?)

CRM Software Blog

Three ways to import Power BI data into SharePoint

It’s no secret you can import data from a SharePoint list into Microsoft’s Power BI. However, it is also possible…

to easily bring Power BI data into SharePoint.

SharePoint, the document management and collaboration platform from Microsoft, now enables users to incorporate analytics directly into the platform as well as view them from SharePoint. Embedding analytics directly within SharePoint can eliminate some of the hurdles to data-driven decision making, where users previously had to have various applications open to see the data. Now the data can exist natively in a content management app. There are a few different methods for doing so.

Method 1: Importing raw data

The first way to make Power BI data available in SharePoint is by exporting the raw data. When you mouse over a Power BI visualization, an icon appears that looks like three periods in the upper-right-hand corner of that visualization. Click on this icon, followed by the Export Data icon, shown in Figure 1. The report data can be saved as a comma-separated values file. Now, simply upload the CSV file to a SharePoint list within your team site.

sContentManagement Figure1 072516 2 mobile Three ways to import Power BI data into SharePoint Figure 1: Click on the Export Data icon.

Method 2: Using a URL

A second method involves using a URL. Begin the process by clicking a visualization within the Power BI interface. Next, choose the Publish to Web option from the File menu, shown in Figure 2. Once you do this, you will see a message telling you that you can get a link or embed code that you can include on a website or send in an email. Click on the Create Embed Code button, followed by the Publish button to get this code.

sContentManagement Figure2 072516 2 mobile Three ways to import Power BI data into SharePoint Figure 2: Choose the Publish to Web option from the File menu.

Now, open your team site and navigate to the page on which you want to insert the Power BI dashboard. Click the Edit link to begin editing the page, and then go to the Insert tab and click on the Embed Code icon, shown in Figure 3.

sContentManagement Figure3 072516 2 mobile Three ways to import Power BI data into SharePoint Figure 3: Go to the Insert tab and click on the Embed Code icon.

At this point, you can paste your embedded code into the space provided, click on the Insert button, followed by the Save button. The Power BI dashboard will now be added to the SharePoint site, as shown in Figure 4.

sContentManagement Figure4 072516 mobile Three ways to import Power BI data into SharePoint Figure 4: The Power BI dashboard has been added to SharePoint.

Method 3: Analyzing the data in Excel

Embedding analytics directly within SharePoint can eliminate some of the hurdles to data-driven decision making.

Begin the process by logging into Power BI and clicking on the visualization that you want to share. When you do, Power BI will display a larger view of the dashboard. Now, click on the Power BI File menu and then choose the Save As option. When prompted, name the report. Then you can save the report into Power BI’s Reports container. Now, right-click on the report and choose the Analyze in Excel option, as shown in Figure 5.

sContentManagement Figure5 072516 mobile Three ways to import Power BI data into SharePoint Figure 5: Right click on the report and choose the option to analyze the report in Excel.

At this point, a pop-up window may appear that contains a message indicating that new versions of the Excel libraries are available. You will need to download this update before you will be able to analyze Power BI data in Excel. The download exists in the form of an MSI file, which makes it easy to push the add-in to anyone else whose copy of Excel 2016 may need to be updated.

When you open the report in Excel, you won’t see the Power BI dashboards. Instead, you will see a message like the one shown in Figure 6, explaining that you need to choose fields from the pivot table in order to build the report. You can easily use these fields to display the data in any way that you want.

sContentManagement Figure6 072516 mobile Three ways to import Power BI data into SharePoint Figure 6: You will need to choose fields from the pivot table to create a report.

When you have finished formatting the spreadsheet, save it to a local file and upload it to a SharePoint list or to a document library.

As you can see, it is relatively easy to make Power BI data available through a SharePoint team site. You can embed the Power BI dashboard directly into the site, or you can make raw data available in the form of an Excel file or a CSV file.

Let’s block ads! (Why?)


SearchBusinessAnalytics: BI, CPM and analytics news, tips and resources

Why Don’t I See Address Fields on the CRM Import Template?

If you are responsible for importing data into CRM, a convenient way to do so is to use the Download Template for Import option. Clicking on this button will yield an XML file which can be opened in Excel, and will display the fields for your chosen entity. Here you

Read More

 Why Don’t I See Address Fields on the CRM Import Template?

Let’s block ads! (Why?)

PowerObjects- Bringing Focus to Dynamics CRM