The Excel Importer module, available in the App Store, is a great resource for importing data. Typically, I have seen it used as a mode for data migration or for an Administrator importing data – loading a large table, like a list of state abbreviations. In this instance, a developer would have to manually enter each state which takes time (and often needs to be replicated across multiple application environments). But with the Excel Importer, you can design the Excel once and simply use the importer across each environment.
The situation above showcases the most common use case for the Excel Importer. However, the underlying logic can be reused in a user friendly fashion so that end users can (unbeknownst to them) take advantage of this powerful tool. Here are two other use cases where I have found this tool very helpful.
Providing a way for a user to upload an Excel file
In this use case, the user has an Excel file (maybe provided by a client or some other system) that they must enter into the system. Rather than having the user manually enter this data, we can create a template in the background for them to use. Once this template is created, we can simply retrieve it in a Microflow for use.
Using a template to import a nightly batch
The second scenario is similar in that the file is provided fairly often, but must be retrieved by the system to import daily. This Microflow is very similar, and it is run as a scheduled event.
The only major difference here is the error handling – if there is an error, we cannot show a message to the user, as there is no user. I add a descriptive line to the log file, but you may want to implement other measures as well (such as sending an email or adding to a user’s task list to address this).
There are a few things you must be aware of while doing this:
- You don’t have complete control over what file an end user will upload. Since you can’t control the file they will import, you must have the proper error handling in place. This will depend on your particular use case, but the generic, ‘an error has occurred’ page may not suffice.
- You can’t control the data they import. The file may be formatted properly, but they may enter bad data. To minimize bad data entry, I suggest importing to a temporary table and then validating the data before entering it into the proper database table. During this validation you can present useful messages to the user so they are aware if particular records did not import properly.
- The templates must be entered into each environment. This can be quickly done by using the Export and Import Template functionality provided by the Module, but depending on your error handling, this may cause unhandled errors, if they are not properly dealt with. A generic ‘An error has occurred’ message would appear and could confuse the end user.
The Excel Importer Module is very powerful and can be used to improve your application in many ways. These are the three ways I’ve used it – do you have any other creative uses for the Excel Importer module? Have you used other App Store modules in a creative way? Share them in the comments below!