phone-link-speed-dials-to horizon-integrator.md (3575B)
1 --- 2 title: "Migrating LG Phone-Link contacts to Horizon Integrator" 3 date: 2019-07-23T12:48:00 4 tags: ["Databases", "Formats", "Guides", "Linux", "PBX", "Servers", "Windows", "Work"] 5 --- 6 7 If you've used LG's Phone-Link software for any reasonable period of time, you should have a large XML file in your `%appdata%\PHONE-LiNK` directory named `Recent.xml`. 8 9 Make a copy of this and get it on a computer with a Linux environment. For this process, we're going to need `xmllint` from the `libxml2-utils` package and `xmlstarlet` from the `xmlstarlet` package. 10 11 Once these are installed, verify your `Recent.xml` file is the expected unholy mess we've come to know and love via `less Recent.xml`. Once you've been sufficiently disheartened, close `less` by pressing `q`. 12 13 From this, make a backup as that's always a good idea: 14 ``` 15 cp "Recent.xml" "backup-Recent.xml" 16 ``` 17 18 Now we're going to lint the file, merge the incoming and outgoing blocks, and build a CSV file: 19 ``` 20 xmllint "Recent.xml" --format --output "Recent.xml" 21 sed -i 's/CalledContact/CallerContact/g' "Recent.xml" 22 xmlstarlet sel -T -t -m /Recent/CallerContact -v "concat(Name,';',Contact,';',Tel,';;',Email,';',Company)" -n "Recent.xml" > "Recent.csv" 23 ``` 24 25 Now we have a messy CSV file with the information in it we wanted. 26 27 We're going to sort this, remove unecessary entries (from places with no names) and fix up the delimiters (as Phone-Link will save Names as Contact, Company which breaks CSV files): 28 ``` 29 sort -b -u -o "Recent.csv" "Recent.csv" 30 sed -i '/^(/d' "Recent.csv" 31 sed -i 's/, / - /g' "Recent.csv" 32 sed -i 's/;/,/g' "Recent.csv" 33 ``` 34 35 Last but not least, we're going to add our header row, again using `sed`: 36 ``` 37 sed -i '1 i\First Name,Last Name,Number,Extension,Email,Company' "Recent.csv" 38 ``` 39 40 Now you can start working your way through the inane and sometimes arcane Horizon Company Directory requirements, as detailed below: 41 * No brackets 42 * No @ symbols (except for email addresses) 43 * No apostrophes 44 * No ampersands 45 * No slashes 46 * No hashes 47 * No periods 48 * No hyphens 49 * No spaces 50 * 15 characters max 51 * No empty fields (except for extension, and email) 52 53 The way I go about this is to split the columns into separate files, apply the filters and then rebuild the file afterwards. 54 55 So, let's get splitting: 56 ``` 57 for i in {1..6}; do cut -f"$i" -d\, "Recent.csv" > "column-$i.txt"; done 58 ``` 59 60 Here is where I manually split the *Name* field into *First Name* and *Last Name*. I did this manually as some names didn't nicely fit into the *A B* format. 61 62 Now we've got our columns split, lets get processing those invalid characters. You'll need to process each file individually, as certain files need certain filters. 63 ``` 64 sed -i -e "s/#//g" file.txt # hash symbols 65 sed -i -e "s/'//g" file.txt # apostrophes 66 sed -i -e "s/@//g" file.txt # at symbols 67 sed -i -e "s/\///g" file.txt # slashes 68 sed -i -e "s/&//g" file.txt # ampersands 69 sed -i -e "s/(//g" file.txt # open brackets 70 sed -i -e "s/)//g" file.txt # close brackets 71 sed -i -e "s/-//g" file.txt # hyphens 72 sed -i -e "s/ //g" file.txt # spaces 73 sed -i -e 's/\.//g' file.txt # periods 74 sed -i -e 's/^$/x/' file.txt # replace blank lines 75 sed -i -e "s/^\(.\{15\}\).*/\1/g" file.txt # snip columns to length 76 ``` 77 78 Once done, merge the files back together using paste, removing duplicates with uniq: 79 ``` 80 paste -d, column-{1..6}.txt | uniq > Recent-processed.csv 81 ``` 82 83 Now try to upload the resulting file, and manually fix any inevitable errors that will have cropped up like duplicate entries and trailing invalid characters. Above is the process I used for my dataset.