How to export Outbound Route Dial Patterns and Trunk Dialed Number Manipulation Rules to a CSV file in FreePBX

 

Important
This is an edited version of a post that originally appeared on a blog called The Michigan Telephone Blog, which was written by a friend before he decided to stop blogging. It is reposted with his permission. Comments dated before the year 2013 were originally posted to his blog.

If you use a recent version of FreePBX, you are familiar with the new and tedious method of entering Outbound Route Dial Patterns and Trunk Dialed Number Manipulation Rules.  Fortunately, version 2.9 and above offer a way to import a list of patterns from a CSV file (there’s a way to patch FreePBX 2.8 to get this functionality as well — see Ticket #4691).

What they don’t give you is a way to export a list of patterns once you have them installed.  So if you want to clone a route and you’ve lost your original CSV file (or never had one to begin with because FreePBX converted your existing routes and trunks when you upgraded from version 2.7), what do you do?  Fear not, because it actually is possible, if not exactly the most straightforward process.

EDIT: Now there is an easy way around all this — see this thread on the FreePBX Swiss Army Knife Module.  If you use that module, you don’t need to read the rest of this article (although, you might be interested in the part about editing CSV files). Unfortunately, it is reported that the module does not work with FreePBX 2.10 or above, and the author has said he will not fix it to work with newer versions. It is possible that the ability to export outbound route and trunk data to a CSV file may be present in the newest version(s) of FreePBX.

EDIT: Steps 1 and 2 involve using a Database editor module to export the data to a CSV file.  Unfortunately, one commenter says that this module no longer works with FreePBX 2.9.  There are other ways to accomplish the same thing — see the edit at the end of this article to use Webmin or phpMyAdmin instead of the Database editor module.  If you export the data using one of those other programs, then skip to step 3 below.

Step 1: Go to the FreePBX bug tracker and look for Ticket #4793 — Database editor module (like phpMyAdmin for FreePBX).  On that page you’ll find a download link for dbeditor-1.0.tgz which (at the time of this writing) is the only version of this software available. Download and install it as you would any third-party module (download it to your computer, then in FreePBX’s GUI navigate to Module Admin and then click the “Upload Module” link, then upload the module and follow the directions to complete the install).

Step 2: Once you have the Database Editor installed, it will appear under the FreePBX “Tools” tab, in the “System Administration” section. Click on the “Database Editor” link, and you should see a list of database tables used by FreePBX. The two you are interested in are called outbound_route_patterns and trunk_dialpatterns. Near each pattern name you will see two links for “Export” and “Drop” — do not click either of those (especially be careful not to click drop!), because the “Export” here will export the table in MySQL format, which is not what you want. Instead, click on the name of the table (that is, click on either outbound_route_patterns or trunk_dialpatterns) and a new page will open. Near the top of the page you will see a place where it says, “Export to CSV: pipe – tab – comma – semicolon” — click on comma and it should bring up a file save dialog that will let you save the file to your system.

Step 3. Load the downloaded file into any text editor that can deal with Linux/Unix-style line endings and not change them (so, don’t use Notepad!), or better yet, use a CSV editor if you have one (an excellent free one is CSVed, which runs under Windows but will also install and run under CodeWeavers’ CrossOver on a Mac, which tends to make me think it would probably also run under WINE on a Linux or Mac OS X computer). I do NOT recommend opening the file in a spreadsheet application such as Excel, because if you have any patterns that start with one or more leading zeroes, those might be removed, and it’s also possible that any non-numeric characters may be misinterpreted or removed.

The first number in each line is associated with a particular outbound route or trunk, so, you want to cut out the lines not applicable to the route or trunk you want to keep. Don’t erase the top (header) line. If you have many routes or trunks, it may be a bit tricky to figure out which is which, since the numbers don’t tell you the name of the route or trunk they are associated with.

Deleting columns using CSVed

After you do that, you also have to get rid if the first column in each line. So let’s say you are using route 3, and each line starts with 3,. What you want to do is a search and replace on <newline>3, (or expressed as a regular expression: n3,) and replace it with a newline only (n as a regular expression). In a CSV editor you may be able to just delete the first column. For a trunk, the principle is the same except that you will need to remove the first AND last columns, leaving only the middle three.

While you’re at it, it’s also possible to use search and replace in other ways. For example, if you are duplicating list of outbound route patterns but need to change the extension field pattern in all lines, you could do that using search and replace, if you understand what you’re doing.

Step 4. Once you have edited out all the lines except the ones pertaining to the route or trunk you want, you need to change the header line at the top. It’s important to get this right. For an outbound route you want to change it from this:

route_id,match_pattern_prefix,match_pattern_pass,match_cid,prepend_digits

(Note that the route_id may be missing after the previous edit) to this:

prefix,match pattern,callerid,prepend

Visually inspect the lines following the header to make sure you have four fields separated by exactly three commas.

For a trunk, you will need to change the first header line from this:

trunkid,match_pattern_prefix,match_pattern_pass,prepend_digits,seq

(Note that the trunkid and seq may be missing after the previous edit) to this:

prefix,match pattern,prepend

Visually inspect the lines following the header to make sure you have three fields separated by exactly two commas. Also, and this applies to trunks only, if it is important that trunk dial patterns be in a particular order then you will want to check to make sure they are in the correct order in the CSV file, since the “seq” column is not preserved. In many situations this is not an issue but in certain special cases the order of trunk dial patterns can make a difference in how they are processed.

Step 5: Save the modified file to a file with the .csv extension (if using a CSV editor make sure you are saving in comma-delimited format). Again, try to make sure your editor doesn’t change the line endings – I don’t know for certain that it would make a difference, but it might.

Step 6: Now you can create a new route or trunk, and in the “Dial patterns wizards” or “Dial Rules Wizards” dropdown select “Upload from CSV” and select your file to upload. Note that if you are using a beta version of FreePBX 2.9, it may complain if you try to submit an outbound route with no patterns, even if you are uploading a CSV file. In that case, just put a single “X” in the “match pattern” field. After you submit changes, be sure to scroll through the patterns to make sure they appear to be correct. In particular, make sure that all values are in the correct fields.

There are probably other ways to accomplish this, and maybe eventually the FreePBX developers will add an export function on the route and trunk pages (obviously, it would probably not be a good idea for me to request it, and besides, it appears that someone already has).

EDIT:  Here is a way to export the data using Webmin or phpMyAdmin (replacing steps 1 and 2 above).  Use Webmin if you have it, because it produces cleaner output:

If using Webmin, from the main Webmin page, go to “Servers”, then “MySQL Database Server.”  Under “MySQL Databases”, click on “asterisk”, and it should take you to the “Edit Database” page (be very careful from here on out because if you do the wrong thing you could really mess up your system).  In the “Edit table” dropdown, select either outbound_route_patterns or trunk_dialpatterns, depending on which you want to work with.  That should take you to an “Edit table” page, but at the bottom of that page you should see a button labeled “Export as CSV.” Click on that button, and it will take you to a “CSV export options” page.  You want to select the following:

  • CSV with quotes
  • Yes to “Include column names in CSV?”
  • For export destination, use whichever is more convenient for you (note that if you “Save to file” it will be placed in a directory on your server, so you might find it easier to display it in a browser window and then save it from there).
  • Export all rows
  • Leave all columns selected in “Columns to include in CSV” (you’ll discard the first column in step 3, but you’ll still need it to allow you to determine which rows to keep for each route or trunk)

Then click the “Export Now” button.  If you exported to a browser window, use Ctrl-A to select all the lines, and Ctrl-C to copy them (⌘A and ⌘C on a Mac), then proceed with Step 3 above (except use Ctrl-V or ⌘V to paste the lines into the text editor). Or, if you prefer to use a CSV editor, then in your browser simply go to File | Save Page As… and save the entire page as a plain text file with a .csv extension to your local machine, and then proceed with Step 3 above.

If using phpMyAdmin, from the main page select “asterisk” in the left-hand column, then (still in the left-hand column) either outbound_route_patterns or trunk_dialpatterns, depending on which you want to work with. Then click the “Export” tab in the main window, and then under “View dump (schema) of table”, in the “Export” section select CSV.  The view should change to show an “Options” section, and there you want to change “Fields terminated by:” from a semicolon to a comma, and then check the “Put fields names in the first row” checkbox.   Then click the “Go” button and it should display the data in a format that can be copied and pasted into a text editor. Sorry, that’s the best I can advise you with regard to phpMyAdmin, since I seldom use that program (and I may have an older version, so things might have changed slightly).

16 thoughts on “How to export Outbound Route Dial Patterns and Trunk Dialed Number Manipulation Rules to a CSV file in FreePBX

  1. Would a module be helpful for this? Seems like I could create a quick one and put it up to help out?

    1. Andrew, I sure wouldn’t mind seeing one, but I don’t know if the current FreePBX development team would welcome it or not. But yes, a module would be very helpful, especially if it could export the fields in the format that the CSV import function wants to see them, so you wouldn’t have to load them into a word processor or CSV editor and do anything if you were simply wanting to transfer them to another system, or use them to assist in cloning a route/trunk, or something like that.

  2. Unfortunately the Database Editor (version 1.0) – for mysql and pgsql does not seem to work with FreePBX 2.9.0rc1.0 as of 03/24/2011. It produces an empty page.

    Just in case it is not a known issue.

    Also the link at the bottom, For updates, bug reports, or feature requests visit Database Editor Home, leads to the “The requested page could not be found.” page.

    1. Vladimir Mikhelson and anyone else who is having problems with the Database Editor module: I added a couple of edits showing alternate ways to export the data using Webmin or phpMyAdmin. I’m aware that many FreePBX users have one or both of those programs installed, so if you do, you have an alternate way to export the data to a CSV file.

  3. Hi
    thanks for great tutorila but problem still the same as I don’t have option to upload cvs files under wizard drop down menu

    1. Zaik, re-read the first paragraph and be sure to check out this link:
      http://www.freepbx.org/trac/ticket/4691
      If you apply that patch then you should see the csv upload capability. Alternately, see:
      http://www.freepbx.org/forum/freepbx/users/how-to-do-csv-upload-of-route-trunk-dial-rules
      Either way the principle is the same, you are patching or replacing a couple of files in FreePBX and that gives you the CSV upload capability. FreePBX 2.9 is also supposed to have that capability built in, but then the previous commenter says the Database Editor module doesn’t work in that version (could the FreePBX developers POSSIBLY have made made this any more of a PITA for users?!).

  4. I am really new to patch loading and cannot find clear “how-to”
    may be you could tip how it can be done?
    Thanks in advance and please go easy on me I am just learning and not very stupid -))

    1. Obviously you are not a regular reader of my blog and therefore don’t know how much I hate trying to use the Linux command line for anything. And one of the reasons I hate it so much is because unless you have a photographic memory (and I have whatever the opposite of that is), you can’t remember the damn syntax for any of the commands, because every writer of a Linux utility did things his own way and there’s absolutely no consistency.

      Which is to say, I’ll take a guess but it’s probably wrong, and if so you’ll either need to use Google or Wikipedia to figure it out, or use another method (download the complete files from the 2.9 repository and copy them over the existing files, but make sure you save backups of the originals if you do that).

      My guess is:

      patch < patchfile

      Where patchfile is the file you downloaded. If you're not running as root then you may need to put sudo in front of that.

      If that's not right then happy Googling! 😉

  5. Thanks
    I found nice way round patch file
    here http://pbxinaflash.com/forum/showthread.php?t=8970&page=2
    Yep, I followed your tip and have upload to cvs option in freepbx 2.8. Thanks!
    Here’s my outline for the steps.

    You can download the modules here.
    http://www.freepbx.org/trac/browser/…nches/2.9/core

    The files to download are
    page.routing.php
    page.trunks.php

    Just click on the file to view, and scroll all the way down to download the default format to your windows pc.

    then I ran winscp and navigated to
    /var/www/html/admin/modules/core

    I renamed my two original files in case of an issue!

    copied over the two files with winscp into the directory /var/www/html/admin/modules/core
    right clicked on properties and modified it so it had the same rw-rw-r– rights as the old files.

    logged on with putty to change ownership to asterisk.
    cd /var/www/html/admin/modules/core
    chown asterisk:asterisk page.routing.php
    chown asterisk:asterisk page.trunks.php

    I’m sure there are more elegant ways to do this all with putty, but this worked for me.
    I haven’t uploaded anything yet but the browse feature worked.

    1. Zaik, glad to hear you got it working. For anyone else running 2.8 that wants the CSV upload option, what Zaik suggests is probably the easiest path.

  6. Yes I did ,and it is working only using CSVed and past clipboard content to colum 2 starting from row 2 real pain in the neck to me any way.
    Keep learning -)))

    1. (WARNING – If you find profanity offensive don’t read this comment. You’ve been warned!)

      Just keep in mind that none of this pain would have been necessary if the FreePBX developers had just left well enough alone and stuck with the entry method used in 2.7. Or, if they’d provided CSV import AND export right from the start. No matter what else they may so, I will always remember how much they disrespected their users with this single act. And were I to say what I really think of them (or at least the ones behind this turd of an idea), I’m sure they would be highly offended, given their apparent low tolerance for profanity. But you know what? I don’t really care, because this was such an offensive act against their user base.

      To the FreePBX developers, as far as I am concerned, if you proposed or in any way were involved in the implementation of this new entry method for routes and trunks, you were being an inconsiderate asshole. And if you find that offensive, rest assured that it is very much intentional, because I don’t like you and I probably never will.

      If you’re a FreePBX developer but were not involved in this decision, or actually opposed it, or got just as much advance notification as the rest of us before the decision was set in stone (which is to say, virtually none at all), then the above comment does not apply to you.

      And to anyone reading this blog that may find that comment offensive, there are thousands of other blogs out there, and chances are you don’t actually USE FreePBX and haven’t had to suffer with this shitty and unnecessary change. I defy anyone to show me one advantage that this new method brings, other than that some people think (with no proof or research whatsoever to back it up) that the new method may help new users make fewer mistakes. I call bullshit on that — if a new user doesn’t know what they are doing they will simply fill in the fields incorrectly. You have to know what you’re doing either way, and I haven’t heard from a single user, be they new or old, that has said they really love this new method, or that it has really helped their understanding. But it has sure caused problems and extra work for experienced users.

      My feeling is that any FreePBX developer that was behind this change ought to hang their head in shame and go hide somewhere, and let developers who might actually care about users make the GUI decisions from now on (preferably with advance notice and actually seeking input from the user base), but of course that never happens because it’s always the jerks in any organization that claw their way to the top and then fight to stay there, and that sort of person just doesn’t care how much pain they cause the users.

      Of course the FreePBX developers behind that change will probably never see this big F U to them, in this obscure comment in this obscure little blog that nobody reads (or so it’s been characterized by one of them), and I’m sure they won’t back down from their rotten decision two versions later. Now they are talking about releasing a new distro, and I just wonder how much that might suck if users are given as little consideration as they were when the decision to change the route and trunk entry method was made (to be fair, I haven’t seen it, and maybe the developers that made the decision about the routes and trunks aren’t involved with the new package, but since this change I really just can’t get excited about ANYTHING having to do with FreePBX anymore).

  7. Did you notice mickecarlsson’s comment, “There is no need to follow that article, it is incorporated in 2.9.” Really? There’s a CSV export function in 2.9? Funny that I’ve never seen any mention of it anywhere else!

    Actually, it wouldn’t completely surprise me if he quickly went and wrote one, rather than admitting he was wrong (especially, that he was wrong in dissing this blog).

  8. NOTICE: All comments above this one were imported from the original Michigan Telephone Blog and may or may not be relevant to the edited article above.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Recent Posts

Recent Comments

Archives

Categories

Meta

GiottoPress by Enrique Chavez