Bulk Import or Update using CUCM BAT could be quite tricky if you initially taste it without review its long official user guide and possess very little hands-on experience of it. This post entails the critical tips that you have to bear in mind for BAT transaction, and walks you through the practice-verified BAT transaction procedure derived from my experiences in those cisco UC projects. Hopefully, this post can significantly reduce your time wasting in coping with all kinds of format errors, character encoding issues etc encountered during the BAT operation.
Rules to Keep in mind
- The text-based CSV (Comma Separated Value) file which will be uploaded to CUCM for BAT operation should be encoded in Unicode UTF-8 format, otherwise, you might run into some characters not rendered correctly after BAT transaction. This rule is even more strict when your CSV file isn’t in purely alphanumeric format. In other words, Chinese characters are included for example. But if you CSV file contains only alphanumeric (letters & numbers) characters, the Unicode UTF-8 encoding restriction could be loosen little bitter, because alphanumeric-only CSV files encoded other than Unicode UTF-8 could, still possibly be correctly rendered by CUCM BAT. (This is why you can still use Microsoft Excel to edit CSV file and upload to CUCM only when CSV file is a purely alphanumeric one.)
- The Name of Attributes in the first line of your CSV file should be correct. otherwise BAT transaction fails with error codes such as “unrecognized attribute”. In regarding to this issue, you can click on “view sample file” in any Import or Update sub-menus under BAT menu in CUCM Admin GUI to find the reference.
- For editing pure alphanumeric CSV files, any text edit applications (Notepad, Microsoft Excel) can be used. (Generally, Excel is preferred as each column is clearly identified and better layout is provided). However, regarding to edit CSV files with non-alphanumeric characters included (Chinese characters for example), you have to replace Microsoft Excel with OpenOffice or LibreOffice Spreadsheet.
Save CSV file in Unicode UTF-8 format using LibreOffice.
BAT Procedures & Options
Here, we take Bulk Phone Import in CUCM 9.1.1 using text-based CSV file in conjunction with Phone Template as an example. CSV file contains the unique parameters for each individual phone while Phone Template details all the parameters in common.
Define the heading line of CSV file
It means determining those unique attributes which should be inserted into CUCM database through CSV file. see the first line of below CSV file for example.
DEVICE NAME,DESCRIPTION,DIRECTORY NUMBER 1,PARTITION 1
SEP0065F643CC97,Phone for Testing,3148421,CN_Internal_Partition
There are 3 ways to do this:
- Manually create this using any Text Edit application (Notepad, UltraEdit, CSVed etc) as long as it can be saved in Unicode UTF-8 format finally.
- Created by manually delete unwanted attributed in an CUCM BAT exported (export all details) text-based CSV file
- Manually create using BAT.xlt downloaded from CUCM BAT. Make sure you enable Macro function when open BAT.xlt using Microsoft Excel.
Edit & Input values for each attribute in CSV file
As we have figured out the heading line of CSV file, now, it comes to the step of filling in values of all the attributes listed in the heading line, for every single phone that you are going to import. Also, 3 options are provided.
- Directly edit the CSV file in Text Edit applications (Notepad, UltraEdit etc) and save it finally in Unicode UTF-8 format.
- Open Text-based CSV file in Microsoft Excel by Navigate ting “Data” Tab, select “From Text” and use Comma as Separator.
Note: Microsoft Excel implicitly use an encoding scheme other than Unicode UTF-8, and it does not provide access to change that encoding even when you save a file in Excel. If you edit CSV file using Microsoft Excel and input non-alphanumeric characters as values, Chinese Characters for example, those non-alphanumeric characters would definitely not be rendered correctly. In this case, you need to choose spreadsheet in OpenOffice or LibreOffice for editing as they enable you to change encoding scheme to Unicode UTF-8 when saving the CSV file. And Use Microsoft Excel only when editing pure-alphanumeric CSV file.
- Input values for attributes directly in BAT.xlt
This is the most low-risk way as BAT.xlt will automatically encoding the whole CSV file in Unicode UTF-8 format and ensure each character to be rendered correctly when you click on “Export to BAT Format” after you finish you editing.
Verify you uploaded CSV file
After you upload you CSV file for BAT transaction, it is a best practice to verify your CSV file before using it to Bulk import or update.
- Ensure that all characters in CSV file are rendered correctly.
You can quickly check this by click on “View Source File” button in BAT Job Submit webpage.
- Ensure your values in CSV file make sense to existing CUCM configuration. For example, the objects you specified in CSV file should already exist in CUCM configuration.
”Validation” function in BAT menu is dedicated for this purpose.