I did a post not too long ago on the vlookup function and how it can be used to extract data by matching on a row and returning data x number of columns away. The same exists using the hlookup command to match on a column and return data x number of rows away.

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

The use case for this is simple: you’re doing a parallel upgrade of Call Manager using the import/export method and you want to copy over all the tables that exist in the old database, but if they don’t exist, you still need the table headers. To do this you’ll need the old phone.csv and a blank export of the new phone.csv side by side.

You’ll paste this on the new phone.csv in A2 and drag to the right and down to populate the database.


When you’re done you should copy all values to a new sheet to clean up any #N/A values it may create and thats your new phone.csv

Making bulk changes in CUCM can be done in 3 ways:

  1. bulk operations built into cucm – great but very limited
  2. import/export of tar file – all details – concise but requires change freeze
  3. sql operations – sometimes the best option

The following example I needed to update each line text label to add “Agent” to the beginning. Built in operations would only allow me to change the label all together, not append text. Import / Export would work fine, but for 10,000 phones this would take a full day to process. In this case SQL was my best option. The query below matches the line and updates the label. I used excel and the concatenate function to pump out all of the required operations.

run sql update devicenumplanmap set (label) = (‘Agent – 1112001’) where fknumplan = (select pkid from numplan where dnorpattern = “1112001”)

The vlookup function in excel is one of the most powerful tools to a voice engineer, which at times can seem more like being a database administrator. The logic here is simple: find the user id from the table on the left in the user id column on the table in the right. When you find it, return the value in the row x number of rows away.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value = userid

table_array = the entire table on the right. the leftmost column must contain the lookup value

col_index_num = how many rows to the right to grab the value to return

range_lookup = false always!


Well oh well, I guess it’s been a while since I posted anything. I’ve been so busy lately, mostly working in large bat files or manipulating call manager databases.
Something that ticked me off was how you can’t put windows side by side because they are nested inside Excel. The following code makes all Excel files open in a new window!

*Note this is for .xls files, for .xlsx change the (Excel.Sheet.8 to Excel.Sheet.12), For .csv files, it’s Excel.CSV


@=””C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE” /e “%1″”