Upload outbound route CSV file => Unsupported Pattern file format

FreePBX Configuration
1

Hi, have a file for outbound routes for 8xx toll-free dialing. When attempting to upload FreePBX states Unsupported Pattern file format.

The file is a simple 4 column file with line 1 consisting of:

prepend prefix match pattern callerid

The lines 2 and onward have values for prepend if applicable and match pattern. That’s it.

I have tried exporting same same route from other FreePBX server and this new FreePBX server Sangoma Linux release 7.8.2003 (Core) won’t take it.

The entries look like this:

1 800NXXXXXX
1 833NXXXXXX
1 844NXXXXXX

The flie looks like this in a Ubuntu server:

prepend,prefix,match pattern,callerid
,1800NXXXXXX,
,1833NXXXXXX,
,1844NXXXXXX,
,1855NXXXXXX,
,1866NXXXXXX,
,1877NXXXXXX,
,1888NXXXXXX,
1,800NXXXXXX,
1,833NXXXXXX,
1,844NXXXXXX,
1,855NXXXXXX,
1,866NXXXXXX,
1,877NXXXXXX,
1,888NXXXXXX,
At a loss as to why this FreePBX server will not take it.

Thanks for any help.

2

The CSV is incomplete. Do a bulk export of DIDs (create one manually first if you have to) to get a sample of the file format.

3

Hi, thank you for the reply. The problem with this is the header words are different for DIDs vs. outbound routes. So where would we find the entire line 1 ?

Thanks.

4

You have the wrong format. You’re missing a comma to delimit the prefix. So your headers have 4 columns and your data has 3.

Should be this:

prepend,prefix,match pattern,callerid
,,1800NXXXXXX,
,,1833NXXXXXX,
,,1844NXXXXXX,
,,1855NXXXXXX,
,,1866NXXXXXX,
,,1877NXXXXXX,
,,1888NXXXXXX,
1,,800NXXXXXX,
1,,833NXXXXXX,
1,,844NXXXXXX,
1,,855NXXXXXX,
1,,866NXXXXXX,
1,,877NXXXXXX,
1,,888NXXXXXX,
5

Hi, ok I got the file with the example above from another FreePBX server. Very strange.
Thanks Blaze Studios.

7

Still fails even though in VIM the file looks exactly like the format in your example

prepend,prefix,match pattern,callerid
,1242NXXXXXX,
,1246NXXXXXX,
,1264NXXXXXX,
,1268NXXXXXX,
,1284NXXXXXX,
,1340NXXXXXX,
,1345NXXXXXX,
,1441NXXXXXX,
,1473NXXXXXX,
,1649NXXXXXX,
,1664NXXXXXX,
,1670NXXXXXX,
,1671NXXXXXX,
,1684NXXXXXX,
,1758NXXXXXX,
,1767NXXXXXX,
,1784NXXXXXX,
,1787NXXXXXX,

When pasting in the text box, it has 2 x ‘,’ but on the right side where it shows what it will look like when Reply is pressed it ONLY shows 1 x ‘,’

WTF???
,1808NXXXXXX,
,1809NXXXXXX,
,1829NXXXXXX,
,1849NXXXXXX,
,1867NXXXXXX,
,1868NXXXXXX,
,1869NXXXXXX,
,1876NXXXXXX,
,1900NXXXXXX,
,1907NXXXXXX,
,1939NXXXXXX,
1,242NXXXXXX,
1,246NXXXXXX,
1,246NXXXXXX,
1,268NXXXXXX,
1,284NXXXXXX,
1,340NXXXXXX,
1,345NXXXXXX,
1,441NXXXXXX,
1,473NXXXXXX,
1,649NXXXXXX,

8

outbound
outbound434×413 10.1 KB

This is the format when looking at the csv in vim
what is wrong with it?

Thanks.

9

Why do you need to list every NPA out there? That seems like a waste.

10

It’s the caribbean, and we have 1. in north america routes

Also, exported a valid outbound route, tried to import it as a new route ‘test’ and it fails unsupported file format

11

I’m well aware on how the NANP works. The term NPA is for North American number plans.

What format are you using? What CSV format are you using for this?

12

image

13

Try opening that file in Notepad instead of excel and make sure there are no hidden/blank characters that are breaking the formatting of the contents of the file.

14

No, not notepad, wrong line termination, but linux has dos2unix and unix2dos utilities for converting between ms,mac and unix(linux) formts

15

You are correct.

16

What is wrong with vim in ubuntu?

17

Nothing , but hexdump the file and count the fields and commas and spaces between the verified line endings ?

18

HI, ran hexdump filename.csv and it just looks like this…

0000000 7270 7065 6e65 2c64 7270 6665 7869 6d2c
0000010 7461 6863 7020 7461 6574 6e72 632c 6c61
0000020 656c 6972 0a64 2c2c 3231 3234 584e 5858
0000030 5858 2c58 2c0a 312c 3432 4e36 5858 5858
0000040 5858 0a2c 2c2c 3231 3436 584e 5858 5858
0000050 2c58 2c0a 312c 3632 4e38 5858 5858 5858
0000060 0a2c 2c2c 3231 3438 584e 5858 5858 2c58
0000070 2c0a 312c 3433 4e30 5858 5858 5858 0a2c
0000080 2c2c 3331 3534 584e 5858 5858 2c58 2c0a
0000090 312c 3434 4e31 5858 5858 5858 0a2c 2c2c
00000a0 3431 3337 584e 5858 5858 2c58 2c0a 312c
00000b0 3436 4e39 5858 5858 5858 0a2c 2c2c 3631
00000c0 3436 584e 5858 5858 2c58 2c0a 312c 3736
00000d0 4e30 5858 5858 5858 0a2c 2c2c 3631 3137
00000e0 584e 5858 5858 2c58 2c0a 312c 3836 4e34
00000f0 5858 5858 5858 0a2c 2c2c 3731 3835 584e
0000100 5858 5858 2c58 2c0a 312c 3637 4e37 5858
0000110 5858 5858 0a2c 2c2c 3731 3438 584e 5858
0000120 5858 2c58 2c0a 312c 3837 4e37 5858 5858
0000130 5858 0a2c 2c2c 3831 3930 584e 5858 5858
0000140 2c58 2c0a 312c 3238 4e39 5858 5858 5858
0000150 0a2c 2c2c 3831 3934 584e 5858 5858 2c58
0000160 2c0a 312c 3638 4e38 5858 5858 5858 0a2c
0000170 2c2c 3831 3936 584e 5858 5858 2c58 2c0a
0000180 312c 3738 4e36 5858 5858 5858 0a2c 2c2c
0000190 3931 3030 584e 5858 5858 2c58 2c0a 312c
00001a0 3339 4e39 5858 5858 5858 0a2c 2c31 322c
00001b0 3234 584e 5858 5858 2c58 310a 2c2c 3432
00001c0 2e36 0a2c 2c31 322c 3634 584e 5858 5858
00001d0 2c58 310a 2c2c 3632 2e38 0a2c 2c31 322c
00001e0 3438 2c2e 310a 2c2c 3433 2e30 0a2c 2c31
00001f0 332c 3534 2c2e 310a 2c2c 3434 2e31 0a2c
0000200 2c31 342c 3337 2c2e 310a 2c2c 3436 2e39
0000210 0a2c 2c31 362c 3436 2c2e 310a 2c2c 3736
0000220 2e30 0a2c 2c31 362c 3137 2c2e 310a 2c2c
0000230 3836 2e34 0a2c 2c31 372c 3835 2c2e 310a
0000240 2c2c 3637 2e37 0a2c 2c31 372c 3438 2c2e
0000250 310a 2c2c 3837 2e37 0a2c 2c31 382c 3930
0000260 2c2e 310a 2c2c 3238 2e39 0a2c 2c31 382c
0000270 3934 2c2e 310a 2c2c 3638 2e38 0a2c 2c31
0000280 382c 3936 2c2e 310a 2c2c 3738 2e36 0a2c
0000290 2c31 392c 3030 2c2e 310a 2c2c 3339 2e39
00002a0 0a2c
00002a2

19

does it match a csv with four comma delimited fields and a ‘\r’ eol ?

Or are you waiting for someone to do your homework for you ? :wink:

20

Dicko. No idea. What I can see when opening that file in VIM in Ubuntu, looks correct to me.

prepend,prefix,match pattern,callerid
,,1242NXXXXXX,
,,1246NXXXXXX,
,,1264NXXXXXX,
,,1268NXXXXXX,
,,1284NXXXXXX,
,,1340NXXXXXX,
,,1345NXXXXXX,
,,1441NXXXXXX,
,,1473NXXXXXX,
,,1649NXXXXXX,
,,1664NXXXXXX,
,,1670NXXXXXX,
,,1671NXXXXXX,
,,1684NXXXXXX,
,,1758NXXXXXX,
,,1767NXXXXXX,
,,1784NXXXXXX,
,,1787NXXXXXX,
,,1809NXXXXXX,
,,1829NXXXXXX,
,,1849NXXXXXX,
,,1868NXXXXXX,
,,1869NXXXXXX,
,,1876NXXXXXX,
,,1900NXXXXXX,
,,1939NXXXXXX,
1,,242NXXXXXX,
1,,246.,
1,,246NXXXXXX,
1,,268.,
1,,284.,
1,,340.,
1,,345.,
1,,441.,
1,,473.,
21

Made new outbound route in Libre Office in Ubuntu. Type (text) csv. Comma delineated. Typed in the routes. Save and upload to the PBX. PBX displays:

 TypeError
Argument 1 passed to FreePBX\modules\Core\Components\Outboundrouting::areAllPaternsUnique() must be of the type array, null given, called in /var/www/html/admin/modules/core/Components/Outboundrouting.php on line 168


/var/www/html/admin/modules/core/Components/Outboundrouting.php

            $sql = 'SELECT a.*, b.seq FROM `outbound_routes` a JOIN `outbound_route_sequence` b ON a.route_id = b.route_id WHERE a.route_id=?';
            $sth = $this->Database->prepare($sql);
            $sth->execute(array($id));
            return $sth->fetch(PDO::FETCH_ASSOC);
        }
     
        public function updateTrunks($route_id, $trunks, $delete = false){
            if ($delete) {
                $this->deleteTrunkRouteById($route_id);
            }
            $stmt = $this->Database->prepare('REPLACE INTO `outbound_route_trunks` (`route_id`, `trunk_id`, `seq`) VALUES (?,?,?)');
            $seq = 0;
            foreach ($trunks as $trunk) {
                $stmt->execute([$route_id, $trunk, $seq]);
                ++$seq;
            }
            return [];
        }
     
        public function areAllPaternsUnique(array $array, $column){
            $arraySize = count($array);
            $uniqueColumnSize = count(array_unique(array_column($array,$column)));
            return $arraySize === $uniqueColumnSize;
        }
     
        public function updatePatterns($id, $patterns, $delete = false){
            $filter = '/[^0-9\*\#\+\-\.\[\]xXnNzZ]/';
            $insert_pattern = [];
            /**
            * This was a todo in functions inc. Throwing an exception here may be to big of a functional change
            * For now we log this and later we can make it do magic. ¯\_(シ)_/¯
            **/
            if(!$this->areAllPaternsUnique($patterns,'prepend_digits')){
                dbug(sprintf(_("All the patterns for route id %s were NOT unique which can cause unexpected behavior This may be unallowed in the future."),$id));
            }
            foreach ($patterns as $pattern) {
                $match_pattern_prefix = preg_replace($filter, '', strtoupper(trim($pattern['match_pattern_prefix'])));
                $match_pattern_pass = preg_replace($filter, '', strtoupper(trim($pattern['match_pattern_pass'])));
                $match_cid = preg_replace($filter, '', strtoupper(trim($pattern['match_cid'])));
                $prepend_digits = preg_replace($filter, '', strtoupper(trim($pattern['prepend_digits'])));

Arguments

    "Argument 1 passed to FreePBX\modules\Core\Components\Outboundrouting::areAllPaternsUnique() must be of the type array, null given, called in /var/www/html/admin ▶"