XL-Tools Documentation

First start

When you start XL-Tools for the first time, you are asked if you want to set default configuration. If you click Yes, you will be asked to select a directory where:

  1. The GeoIP Database (GeoLiteCity.dat) will be downloaded if it doesn't already exist. It could exist in the XL-Parser user directory;
  2. The OUI Database (oui.txt) will be downloaded and converted (OUI.db) if it doesn't already exist. It could exist in the XL-Parser user directory;
  3. The IIN Database (IIN.zip) will be downloaded and converted (IIN.db) if it doesn't already exist. It could exist in the XL-Parser user directory;
  4. The Datetime Database (DT.db) will be downloaded if it doesn't already exist. It could exist in the XL-Parser user directory;
  5. The XL-Whois Database is searched and selected. If it can't been found, you will be asked if XL-Whois is installed on the system and where is the database. The XL-Whois Database is created and is only updated when you are using XL-Whois. If you don't have this database (or don't need it), just click on Cancel;
  6. Finally, following default options will be selected or set:
    • Check for update at startup for the tool is selected;
    • Maximum size for lists is set to 5 000 000 characters;
    • NsLookup timeout is set to 10 seconds;
    • User-Agent is set to XL-Tools (http://www.le-tools.com);
    • When no result option is set to Leave a blank;
    • Check for update at startup for GeoIP database is selected;
    • Default language is set to en-US;
    • Default charset is set to cp1252;
    • Default timezone is set to America/New York;

A XL-Tools.ini configuration file will also be created to keep your preferences in memory. You can also set elements above manually, in case you already have some of these databases.

Top of the page

Config Window

Xl-Tools Config Window - General

General

In Tool section, we have the following functions:

  • Export Lang.ini: Use this function to translate XL-Tools GUI. See Translation for help about this functionality.
  • Check Update: Check on le-tools.com if a tool update is available.
  • Check for update at startup: Check if and update is available everytime XL-Tools is started.

In Functions section, you can:

  • Choose to start XL-Tools in Full Screen;
  • Choose to Remember position of the GUI (usefull if you have multiple monitors);
  • Change Maximum size for list value. This value represents the maximum number of characters that can displayed in the GUI for each list. If you try to insert more than this limit, you receive a warning that the list will be truncated. You then have the possibility to use a list in a file or increased this value. By default, the maximum size is set to 5 000 000 characters;
  • Set the NsLookup timeout: Associated to the Utils - Nslookup function. This is the time it will wait for the answer. Default is 10 seconds.
  • Customize the User-Agent of XL-Tools: The User-Agent is used to identify the tool when requesting databases from Website. Default is XL-Tools (http://www.le-tools.com).
  • Set the When no result option. Associated with many functions. This is the default value when function is not able to get a result (no valid input, error, no match, ec.). Default is Leave a blank;

Top of the page

Databases

Xl-Tools Config Window - Databases

In this tab, you have to set the location of three databases:


Top of the page

XL-Toolkit Databases

Xl-Tools Config Window - XL-Toolkit Databases

In this tab, you have to set the location of two databases:

Top of the page

Datetime database Window

Xl-Tools - Datetime Database Window

The Datetime database is used to store and manage all datetime formats that will be available in the tool. This database is used with sort by date and time function and with all functions in Time.

In the bottom of the window, you have the following option:

  • Local timezone: The local timezone is used to identify your local timezone. On some system, the local timezone couldn't be determined so you will have to set it manually.
  • Default language: Some elements of a datetime are specific to a language like the month or the day name. To get those element parsed from or translated to another language, you should change this setting.
  • Output charset: If you change the language, you may also have to change the charset.
  • Default output: This is the default output that will be used if you select Default in the main interface. This default output is saved as well as other settings. Same as input can only be used if input type is a Datetime object. It cannot be used with ChromeTime for example.

You can add, edit or delete any datetime object in the database.

Datetime database - Edit

Xl-Tools - Datetime Database - Edit

When you add or edit a datetime object, you must provide the following details:

  • Sample: The sample is an exemple of the datetime object. The sample will be used to identify the datetime object so it must be unique. The From List 1 button will gather the first item in List 1 as sample.
  • Pattern: The pattern is based on the well known strftime function. You can use the combobox on right side of the textfield to select appropriate symbol. The most important symbols are listed in the combobox. You should also be able to use any symbol supported by strftime, even if not listed.
  • Regex: The regex must match the pattern and is used to extract the datetime object from any string. If the Match pattern option is selected, it will be created automatically while you're entering the pattern.
  • Timezone: This is the default timezone to be used if no timezone is provided in the datetime object (ex.: timezone is provided if pattern contains %Z, %z or %s symbol).
  • Parsed: If regex and pattern match the sample (which should indicate that everything is ok), the parsed datetime object will be shown here.
  • Use as: The datetime object could be used as input format, output format or both. It can also be set to none so it will be stored in the database, but not available for input or output in XL-Tools main interface.
  • Comment: This is optional. The comment is used to give more information about the datetime object.

If the Add or Edit button are still disabled after you filled all the fileds, you can click on the ? to see what is missing or is incorrect.

Ambiguous timezone abbreviations

Some timezone abbreviations are ambiguous, because there are matching more than one timezone. For example, CST could be Central Standard Time (USA), China Standard Time, or Cuba Standard Time. You can take a look here. So, I decided to set those timezone with the following values:

  • ADT = -0300 (Atlantic Daylight Time)
  • AST = -0400 (Atlantic Standard Time)
  • BST = +0100 (British Summer Time)
  • BT = +0600 (Baghdad Time)
  • CAT = +0200 (Central Africa Time)
  • CCT = +0630 (Cocos Islands Time)
  • CDT = -0500 (Central Daylight Time)
  • CST = -0600 (Central Standard Time)
  • EAST = -0600 (Easter Island Standard Time)
  • ECT = -0500 (Ecuador Time)
  • EDT = -0400 (Eastern Daylight Time)
  • EST = -0500 (Eastern Standard Time)
  • FDT = -0100 (Fernando de Noronha Daylight Time)
  • FST = -0200 (Fernando de Noronha Standard Time)
  • GST = -0200 (South Georgia Time)
  • IDT = +0300 (Israel Daylight Time)
  • IST = +0200 (Israel Standard Time)
  • MET = +0100 (Middle European Time)
  • NFT = -0230 (Newfoundland Daylight Time)
  • NST = -0330 (Newfoundland Standard Time)
  • PST = -0800 (Pacific Standard Time)
  • SAST = +0200 (South Africa Standard Time)
  • SST = +0800 (Singapore Standard Time)
  • WAST = +0200 (West Africa Summer Time)
  • WST = +0800 (Western Standard Time)

I have try to set it to the most popular value, but I could be wrong. If the datetime you need to parse have a different offset, you can parse the datetime without the timezone and set the appropriate offset in the input format.

Main Interface

Xl-Tools Main Window

The main interface is divided in three parts:

  • The header that contains the functions in five categories: Lists, Sorting, Conversion, Time and Utils;
  • The body that contains the list of items;
  • The footer that contains information about the state of the process and buttons: process (or stop when process running), open settings, help and about;

If Not Ready ? Click here is visible, click on it if you want to know which step comes next.

Top of the page

Lists

The Lists category contains various functions to deal with list of strings:


Lists - No duplicate

This function simply removes duplicate from a list of strings.

Lists - Only duplicates

With this function, you can analyse one or two lists of string. The function produces a list of items that appear more than one time (overall). If you want to compare two lists, make sure each list doesn't contain duplicates.

Lists - Count items

This function enumerates items in the list and produces a list of unique items with the number of occurrence for each item.

Lists - Count characters

This function counts the number of characters in each string.

Lists - L1-L2

This function removes items from List 1 that are in List 2. If there are duplicates in List 1, there will be removed. Exemple:

List 1 List 2 Results
				dog
				dog
				cat
				mouse
				rabbit
				
				cat
				mouse
				
				dog
				rabbit
				

Lists - Column to row

This function takes items in List 1 and produces a single row that contains every items separated by expression you must provide in the With textfield. Exemple with comma in the With textfield:

List 1 Results
				dog
				cat
				mouse
				rabbit
				
				dog,cat,mouse,rabbit
				

Lists - Row to column

This function does the opposite of the previous function. Exemple with comma in the With textfield:

List 1 Results
				dog,cat,mouse,rabbit
				
				dog
				cat
				mouse
				rabbit
				

Lists - List to regex

This function takes items in List 1 and produces a regular expression. Each item is separated with the alternative sign "|" and every ASCII non-"word" characters are backslashed. Exemple:

List 1 Results
				c:\temp
				c:\Users
				c:\Windows
				
				c\:\\temp|c\:\\Users|c\:\\Windows
				

Concat

This function concats each items of each lists with the expression in With textfield. It's also possible to use a blank, or to only use List 1 or List 2 if you want to add something before or after a list of items. Exemple with ": " in the With textfield:

List 1 List 2 Results
				Address
				City
				State
				Country
				
				123 Broadway
				New York
				New York
				United States
				
				Address: 123 Broadway
				City: New York
				State: New York
				Country: United States
				

Split strings

This function splits strings of List 1 into columns. In the With textfield, you must indicates the expression that we found between each field. You can use a regular expression. Exemple with "\:\s*(?:\.\s)+" in the With textfield:

List 1 Results
				Introduction:  . . . . . . . . . . . . . . . . 1
				Chapter 1: . . . . . . . . . . . . . . . . . . 2
				Conclusion:  . . . . . . . . . . . . . . . . . 3
				
				Introduction	1
				Chapter 1	2
				Conclusion	3
				

A simpler example with comma in the With textfield:

List 1 Results
				dog,cat,mouse,rabbit
				
				dog	cat	mouse	rabbit
				

Split and extract

This function does the same as the previous function, but you can extract one or multiples columns. You must insert the column numbers in the Columns textfield. Exemple with "1, 3" in the Columns textfield and "comma" in the With textfield:

List 1 Results
				dog,cat,mouse,rabbit
				
				dog	mouse
				

To extract the last column no matter how many columns there are, use the "-1". Example with "1, -1" in the Columns textfield and "comma" in the With textfield:

List 1 Results
				dog,cat,mouse,rabbit
				dog,cat,mouse
				
				dog	rabbit
				dog	mouse
				

Merge lines

This function merges lines into multiples rows. The number of lines for each group must be provided in the Columns textfield. Better way to explain is by an example. Below is the function result with "3" in the Columns textfield:

List 1 Results
				City: New York
				State: New York
				Country: United States
				City: Toronto
				State: Ontario
				Country: Canada
				City: Seattle
				State: Washington
				Country: United States
				
				City: New York	State: New York	Country: United States
				City: Toronto	State: Ontario	Country: Canada
				City: Seattle	State: Washington	Country: United States
				

Split and merge

This function combine the split and the merge function. Each line must contain a pair of field-value and the number of lines by group must be the same like the merge function. The field name of the first group will be used as column headers and will be inserted at the first line of the result. In the With textfield, you must indicates the expression that we found between each field. In the Columns textfield, you must indicate the number of lines for each group. Below is the function result with ": " in the With textfield and "3" in the Columns textfield:

List 1 Results
				City: New York
				State: New York
				Country: United States
				City: Toronto
				State: Ontario
				Country: Canada
				City: Seattle
				State: Washington
				Country: United States
				
				City	State	Country
				New York	New York	United States
				Toronto	Ontario	Canada
				Seattle	Washington	United States
				

Replace

This function replaces a sequence of characters in a string with another set of characters. You can find that kind of function in many text editors. By default, the expression is interpreted as a string which means that you can use capture groups (equivalent to sprintf() with Eval option, see examples below).

The Eval option tells XL-Tools to evaluate the expression so you can also use some Perl functions (see here for list of functions). Please note that this can be dangerous if you don't know what you're doing. Some examples:

Operation List 1 Replace: Regex: By: Eval: Results
Extract data
				Dalvik/1.6.0 (Linux; U; Android 4.4.2; SGH-I747M Build/KOT49H)
				Dalvik/1.6.0 (Linux; U; Android 4.4.2; SGH-T999V Build/KOT49H)
				
.+ ([^ ]+) Build.+
Yes
$1
No
				SGH-I747M
				SGH-T999V
				
Shift data
				FTP (File Transfer Protocol)
				HTTP (Hypertext Transfer Protocol)
				
(\w+) \(([^\)]+)\)
Yes
$2 ($1)
No
				File Transfer Protocol (FTP)
				Hypertext Transfer Protocol (HTTP)
				
To lowercase
				NEW YORK
				LOS ANGELES
				
(.+)
Yes
\L$1
No
				new york
				los angeles
				
To UPPERCASE with \U
				new york
				los angeles
				
(.+)
Yes
\U$1
No
				NEW YORK
				LOS ANGELES
				
To UPPERCASE with sprintf
				new york
				los angeles
				
(.+)
Yes
sprintf("\U$1")
Yes
				NEW YORK
				LOS ANGELES
				
To UPPERCASE with uc
				new york
				los angeles
				
(.+)
Yes
uc($1)
Yes
				NEW YORK
				LOS ANGELES
				
Various
				new york
				los angeles
				
(\w+)\s+(\w+)
Yes
City\: \u$1 \u$2
No
				City: New York
				City: Los Angeles
				
Why dangerous? Try this...
				new york
				los angeles
				
.+
Yes
exit(0)
Yes
Program exits.

Reverse strings

This function reverse strings in List 1. For example, hello world become dlrow olleh.

Transliterate

This function replaces many characters at once. You must provide a sequence of characters to replace and a sequence of replacement characters. Each character in the replace textfield is replaced by the one in the by textfield at the same position. Example:

List 1 Replace: By: Results
				This is a simple example.
				
aest
4357
				Thi5 i5 4 5impl3 3x4mpl3.
				

Lowercase

This function converts each characters of a string to lowercase.

Uppercase

This function converts each characters of a string to UPPERCASE.

Add line number

This function add an incremental number to each lines of List 1. Example:

List 1 Results
				dog
				cat
				mouse
				
				1	dog
				2	cat
				3	mouse
				

Top of the page

Sorting

The Sorting category provides functions to sort items of List 1 based on:

Items can be sort in ascending or descending order (except for Randomize).


Alphabetical order

The most common sorting function. Based on position of letter in the Greek alphabet. Example in ascending order:

List 1 Results
				dog
				cat
				rabbit
				mouse
				
				cat
				dog
				mouse
				rabbit
				

Numerical order

This function compares the first number that can be found on each lines. Example in descending order:

List 1 Results
				cat 2
				mouse 5
				dog 19
				rabbit 4
				
				dog 19
				mouse 5
				rabbit 4
				cat 2
				

String length

This function compares the length of each lines. Example in ascending order:

List 1 Results
				tiger
				elephant
				lion
				giraffe
				
				lion
				tiger
				giraffe
				elephant
				

IPv4 Address

This function compares the first IP address found on each line. Example in ascending order:

List 1 Results
				65.52.0.0-65.55.255.255	Microsoft Corp
				33.0.0.0-33.255.255.255	DoD Network Information Center
				12.0.0.0-12.255.255.255	AT&T WorldNet Services
				207.46.0.0-207.46.255.255	Microsoft Corp
				
				12.0.0.0-12.255.255.255	AT&T WorldNet Services
				33.0.0.0-33.255.255.255	DoD Network Information Center
				65.52.0.0-65.55.255.255	Microsoft Corp
				207.46.0.0-207.46.255.255	Microsoft Corp
				

Date and time

This function compares the first datetime object found on each line. If the date and time format of each line is the same, you gonna get better performance if you select the appropriate format instead of using Guess (or variable).... Use the Guess button to find the appropriate input format in your list. If you have various formats, leave it to Guess (or variable).... Example in ascending order:

List 1 Results
				01/06/2016 00:07:28 GET index.html
				01/06/2016 10:55:12 GET 404.html
				01/06/2016 08:11:14 GET product.html
				01/06/2016 00:11:14 GET contact.html
				
				01/06/2016 00:07:28 GET index.html
				01/06/2016 00:11:14 GET contact.html
				01/06/2016 08:11:14 GET product.html
				01/06/2016 10:55:12 GET 404.html
				

If you have various timezones, it doesn't care because each timestamp is converted to GMT before being compared. Example:

List 1 Results
				03/Feb/2016:17:03:55 -0700
				03/Feb/2016:17:03:55 -0500
				03/Feb/2016:17:03:55 -0200
				
				03/Feb/2016:17:03:55 -0200
				03/Feb/2016:17:03:55 -0500
				03/Feb/2016:17:03:55 -0700
				

Randomize

This function randomize a list of items.

List 1 Results
				dog
				cat
				rabbit
				mouse
				
				rabbit
				dog
				cat
				mouse
				

Top of the page

Conversion

The Conversion category contains functions to convert various string formats. These functions are:


Hex to ASCII

This function convert a list of hexadecimal strings to a list of ASCII strings. You may have spaces in string or not. Example:

List 1 Results
				63 61 74
				646f67
				6d6f757365
				726162626974
				
				cat
				dog
				mouse
				rabbit
				

ASCII to Hex

This function does the opposite of the previous function. Example:

List 1 Results
				cat
				dog
				mouse
				rabbit
				
				636174
				646f67
				6d6f757365
				726162626974
				

Hex to Base10

This function converts hexadecimals value into decimals. If there are spaces between values, there will be interpreted as separators. Otherwise, each characters will be converted separately. Example:

List 1 Results
				646f67
				6d 6f 75 73 65
				
				6 4 6 15 6 7 
				109 111 117 115 101 
				

Base10 to ASCII

This function extracts and converts decimal values into ASCII. Example:

List 1 Results
				CHAR(116)+CHAR(101)+CHAR(115)+CHAR(116)
				
				t e s t 
				

URI Decode

This function converts encoding in URL and other Internet addresses. The encoded characters are represented by a "%" sign followed two hexadecimal digits, but this function will also converts some other representations like in the example below:

List 1 Results
				The%20browser\u00A0can%27t%20display%20the%20\u00A9%20and%20the%20%c2%ae%20symbols.
				
				The browser can't display the © and the ® symbols.
				

URI Encode

This function does the opposite of the previous function. Example:

List 1 Results
				The browser can't display the © symbol
				
				The%20browser%C2%A0can%27t%20display%20the%20%C2%A9%20symbol
				

HTML Decode

This function converts HTML entities into ASCII. Example:

List 1 Results
				{"type":1,"tn":"K"}
				{"type":3}
				
				{"type":1,"tn":"K"}
				{"type":3}
				

HTML Encode

This function does the opposite of the previous function. Example:

List 1 Results
				some reserved chars are <, >, &
				
				some reserved chars are &lt;, &gt;, &amp;
				

Base64 to ASCII

This function convert Base64 encoded string into ASCII. Example:

List 1 Results
				SGVsbG8gV29ybGQgIQ==
				
				Hello World !
				

ASCII to Base64

This function does the opposite of the previous function. Example:

List 1 Results
				Hello World !
				
				SGVsbG8gV29ybGQgIQ==
				

SHA1 - Base32 to Base16

This function converts Base32 SHA1 hash values to Base16. Example:

List 1 Results
				DZ2UNZISKKGDLT4TBXCYZWVBL3NAMCM7
				
				1E7546E512528C35CF930DC58CDAA15EDA06099F
				

Top of the page

Time

The Time category contains functions to deal with various datetime objects:

You should read the Datetime database Window section before using these functions.


Anytime to Anytime

This function can be used to do a lot of things depending of the combination of input and output format. As input, you can select between Datetime, ChromeTime, LDAPTime, Filetime, SystemTime, MAC Absolute time or Mac HFS+ time. For Datetime, you have access to any formats you have in your Datetime database. Here are some combinations you can do:

Convert timezone:

If you're using Guess (or variable)... as input and a format like ISO 8601 as output:

List 1 Results
				03/Feb/2015:17:03:55 -0700
				03/02/2015 14:15:29
				
				2015-02-04 00:03:55 +0000
				2015-02-03 19:15:29 +0000
				

Convert a datetime object to a weekday

You must select the weekday format as output from your Datetime database.

List 1 Results
				2016-02-21 19:12:11 +0000
				
				Sunday
				

Convert unixtime to a datetime object:

You must select the Unixtime format as input from your Datetime database.

List 1 Results
				1456063931
				
				2016-02-21 19:12:11 +0000
				

Convert a datetime object to unixtime:

You must select the unixtime as output from your Datetime database.

List 1 Results
				2016-02-21 19:12:11 +0000
				
				1456063931
				

Convert a ChromeTime to a datetime object:

ChromeTime (WebKit/Chrome timestamp) can be found in cache data of the Google Chrome browser. It's a 17-digit number that correspond to the number of 100-nanoseconds which had elapsed since the 1601-01-01. Example:

List 1 Results
				13100537833000000
				
				2016-02-21 14:17:13 +0000
				

Convert a LDAPTime to a datetime object:

A LDAP/Active Directory/Filetime timestamp is a 18-digit number that correspond to the number of 100-nanoseconds which had elapsed since the 1601-01-01. Example:

List 1 Results
				131003024880000000
				
				2016-02-18 20:54:48 +0000
				

Convert a Filetime to a datetime object:

Windows Filetime can be found in the registry. It corresponds to a 64-bit hexadecimal value (Little Endian) representing the number of 100-nanoseconds which had elapsed since the 1601-01-01. See on msdn.microsoft.com for more details. XL-Tools accepts string that contain spaces or not. Example:

List 1 Results
				88 33 9d cb 38 36 d0 01
				88339dcb3836d001
				
				2015-01-22 06:44:35 -0500
				2015-01-22 06:44:35 -0500
				

Convert a SystemTime to a datetime object:

Windows SystemTime can be found in the registry. It corresponds to a 128-bit hexadecimal structure. See on msdn.microsoft.com for more details. XL-Tools accepts string that contain spaces or not. Example:

List 1 Results
				D9 07 0B 00 01 00 02 00 06 00 09 00 13 00 00 00
				D9070B00010002000600090013000000
				
				2009-11-02 06:09:19 -0500
				2009-11-02 06:09:19 -0500
				

Convert a MAC Absolute time to a datetime object:

A MAC Absolute timestamp (Apple Cocoa Core Data timestamp) is the number of seconds since 2001-01-01. Example:

List 1 Results
				519568056
				
				2017-06-19 08:27:36 -0400
				

Convert a Mac HFS+ to a datetime object:

A Mac HFS+ timestamp is the number of seconds since 1904-01-01. Example:

List 1 Results
				3580719448
				
				2017-06-19 08:17:28 -0400
				

Time difference

This function calculates the duration between two dates (with time or not). You can use a single date and time (default is actual date and time) or you can use a list of datetime objects in List 2. If you choose to use a list, number of items in List 2 must be the same as number of items in List 1. If the format is not the same in all datetime objects of the two lists, you better select Guess (or variable)... as input format.

As output format, you can select string that includes number of years, months, days, hours, minutes and seconds. Examples:

List 1 List 2 Results
				2015-02-03 14:15:29 -0100
				2016-02-29 14:15:29 -0700
				2016-02-20
				2016-02-20
				2016-02-20
				
				2015-12-31
				2016-01-01
				2016-02-15
				2016-01-15
				2015-01-01
				
				0 years, 10 months, 24 days, 12 hours, 44 minutes, 32 seconds
				0 years, 01 months, 28 days, 16 hours, 15 minutes, 29 seconds
				0 years, 00 months, 5 days, 00 hours, 00 minutes, 00 seconds
				0 years, 01 months, 5 days, 00 hours, 00 minutes, 00 seconds
				1 years, 01 months, 19 days, 00 hours, 00 minutes, 00 seconds
				

Or you can select each value separately. Examples with number of years (calculates age):

List 1 List 2 Results
				1939-01-23
				1956-10-15
				1989-05-24
				
				2017-03-05
				
				78
				60
				27
				

Add time

This function take a datetime object as input and add the provided number of years, months, days, hours, minutes or seconds. Because XL-Tools keep context, you can modify all date and/or time in a log at once. Example if we add 18 hours to the following lines using Same as input as output format:

List 1 Results
				01/06/2016 00:07:28 GET index.html
				01/06/2016 00:11:14 GET contact.html
				01/06/2016 08:11:14 GET product.html
				01/06/2016 10:55:12 GET 404.html
				
				01/06/2016 18:07:28 GET index.html
				01/06/2016 18:11:14 GET contact.html
				01/07/2016 02:11:14 GET product.html
				01/07/2016 04:55:12 GET 404.html
				

Substract time

This function does the opposite of the previous function.

Top of the page

Utils

The Utils category contains functions for various usages:


NSLookup

This function resolves a hostname from an IP address or an IP address from a hostname. In case of multiple results, XL-Tools place it on a single line, separated by commas. Examples:

List 1 Results
				www.twitter.com
				le-tools.com
				
				199.16.156.38, 199.16.156.230, 199.16.156.102, 199.16.156.6
				65.39.193.50
				

CIDR to IP Range

This function converts a CIDR to its corresponding IP range (IPv4 or IPv6). Example:

List 1 Results
				195.66.8.0/21
				2001:db8:1234::/48
				
				195.66.8.0 - 195.66.15.255
				2001:db8:1234:0000:0000:0000:0000:0000 - 2001:db8:1234:ffff:ffff:ffff:ffff:ffff
				

IP Range to CIDR

This function does the opposite of the previous function. Example:

List 1 Results
				195.66.8.0 - 195.66.15.255
				2001:db8:1234:0000:0000:0000:0000:0000 - 2001:db8:1234:ffff:ffff:ffff:ffff:ffff
				
				195.66.8.0/21
				2001:db8:1234::/48
				

CIDR to IP list

This function lists all IP addresses (IPv4 or IPv6) based on a CIDR. Example:

List 1 Results
				195.66.8.0/28
				2001:db8:1234::/124
				
				195.66.8.1
				195.66.8.2
				195.66.8.3
				195.66.8.4
				195.66.8.5
				195.66.8.6
				195.66.8.7
				195.66.8.8
				195.66.8.9
				195.66.8.10
				195.66.8.11
				195.66.8.12
				195.66.8.13
				195.66.8.14
				2001:DB8:1234:0:0:0:0:1
				2001:DB8:1234:0:0:0:0:2
				2001:DB8:1234:0:0:0:0:3
				2001:DB8:1234:0:0:0:0:4
				2001:DB8:1234:0:0:0:0:5
				2001:DB8:1234:0:0:0:0:6
				2001:DB8:1234:0:0:0:0:7
				2001:DB8:1234:0:0:0:0:8
				2001:DB8:1234:0:0:0:0:9
				2001:DB8:1234:0:0:0:0:A
				2001:DB8:1234:0:0:0:0:B
				2001:DB8:1234:0:0:0:0:C
				2001:DB8:1234:0:0:0:0:D
				2001:DB8:1234:0:0:0:0:E
				

IP to Arpa

This function converts an IP address (IPv4 or IPv6) to an address in-addr.arpa.. Example:

List 1 Results
				65.39.193.50
				2001:610:240:22::c100:68b
				
				50.193.39.65.in-addr.arpa
				b.8.6.0.0.0.1.c.0.0.0.0.0.0.0.0.2.2.0.0.0.4.2.0.0.1.6.0.1.0.0.2.ip6.arpa
				

Arpa to IP

This function does the opposite of the previous function. Example:

List 1 Results
				50.193.39.65.in-addr.arpa
				b.8.6.0.0.0.1.c.0.0.0.0.0.0.0.0.2.2.0.0.0.4.2.0.0.1.6.0.1.0.0.2.ip6.arpa
				
				65.39.193.50
				2001:610:240:22::c100:68b
				

Resolve MAC Address

This function resolves a MAC address to its corresponding Organizationally Unique Identifier (OUI). To be able to use this function, you must have installed the OUI Database. The common formats are supported and input data can be lowercase or uppercase. The MAC address can be anywhere in the line. Example:

List 1 Results
				00-50-56-C0-00-08
				text before 00:50:56:c0:00:08 text after
				005056C00008
				
				VMware, Inc.
				VMware, Inc.
				VMware, Inc.
				

Resolve IPv4 GeoIP

This function produces GeoIP informations for a given IPv4 address. To be able to use this function, you must have installed the GeoIP Database. Available informations are : Country, Country code, Region, Region code, City, Postal code, GPS coordinates, Timezone name and Timezone offset. If you check the Add headers option, the first line of result will contain the header of each column (columns are tab separated). The IPv4 address can be anywhere in the line. Example with All details and Add headers:

List 1 Results
				65.39.193.50
				
				Country	Country code	Region	Region code	City	Postal code	GPS coordinates	Timezone name	Timezone offset
				United States	US	New York	NY	New York	10004	40.6888, -74.0203	America/New_York	-0500
				

Resolve ISP

This function resolves an IP address to its known Internet Service Provider (ISP). To be able to use this function, you must have a XL-Whois Database. The IP address can be anywhere in the line. Example:

List 1 Results
				199.16.156.38
				text before 65.39.193.50 text after
				2001:4860:4860::8888
				
				Twitter Inc., US
				Host Papa, Inc., CA
				Google Inc., US
				

Resolve User-agent

This function parses a user-agent and extract the information it contains. Available information are : Type, OS, Browser, Device and Lang. If you check the Add headers option, the first line of result will contain the header of each column (columns are tab separated). Example with All details and Add headers:

List 1 Results
				Mozilla/5.0 (Windows NT 6.2; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/44.0.2403.157 Safari/537.36
				Mozilla/5.0 (X11; Linux i686; rv:7.0.1) Gecko/20100101 Firefox/7.0.1 Iceweasel/7.0.1
				Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_6_3; fr-fr) AppleWebKit/531.21.11 (KHTML, like Gecko) Version/4.0.4 Safari/531.21.10
				Mozilla/5.0 (Linux; Android 4.4.2; fr-ca; SAMSUNG SGH-M919V Build/KOT49H) AppleWebKit/537.36 (KHTML, like Gecko) Version/1.5 Chrome/28.0.1500.94 Mobile Safari/537.36
				Mozilla/5.0 (Nintendo WiiU) AppleWebKit/536.30 (KHTML, like Gecko) NX/3.0.4.2.11 NintendoBrowser/4.3.0.11224.US
				
				Type	OS	Browser	Device	Lang
				pc	Windows 8	Chrome 44.0.2403.157		
				pc	Linux Debian	Iceweasel 7.0.1		
				pc	Mac OSX 10.6.3	Safari 4.0.4		French
				smartphone	Android	Mobile Safari 28.0.1500.94	Android (SAMSUNG SGH-M919V)	French
				appliance	Nintendo Wii U	Nintendo Wii U		
				

Credit Card to Issuing Company

This function resolves a credit card (and other payment card) number to its issuing company. The common formats are supported. There are two methods:

  • IIN Local DB: Because the database is local, this method is faster, but result should be less accurate because database is not updated. Example:
  • List 1 Results
    					4110-1441-1014-4115
    					4110 1441 1014 4115
    					4110144110144115
    					5115915115915118
    					
    					VISA card
    					VISA card
    					VISA card
    					Chase Manhattan Bank USA (US) / Mastercard
    					

  • binlist.net: Query the online service (with a maximum of 1,000 queries per hour). Obviously, this method is slower, but result should be more accurate. It produces a table with all available informations (Brand, Sub Brand, Bank, Card Type, Card Category and Country Name). The first line of result contains the header of each column (columns are tab separated). Example:
  • List 1 Results
    					4110144110144115
    					5115915115915118
    					
    					Brand	Sub Brand	Bank	Card Type	Card Category	Country Name
    					VISA	Visa Business		CREDIT		Argentina
    					MASTERCARD	Cirrus		DEBIT		United States
    					

Address to GPS coordinates

This function use the Google Maps Geocoding API to convert addresses to GPS coordinates. You must have a valid API key prior to use this function.

List 1 Results
				1600 Amphitheatre Parkway, Mountain View, CA
				
				37.4219493, -122.0847727
				

Distance between locations

This function calculates distance (in kilometers) between two locations (GPS coordinates). You can use two list of coordinates or a single list with a single location. In the following example, we use 37.4219493, -122.0847727 as single location.

List 1 Results
				37.4233111, -122.0706458
				
				1.256
				

Custom functions...

With Custom functions, you can add any function that matches an item to another. To explain this, lets have a look at the two functions available on le-tools.com:

  • State code to State: This function matches a state code to the state name for Canada and US. Examples:
  • List 1 Results
    					ca
    					qc
    					ny
    					
    					California
    					Quebec
    					New York
    					

  • Resolve TLD: This function matches any Top Level Domain (TLD) to its country name or organisation. Data come from the IANA — Root Zone Database. Examples:
  • List 1 Results
    					ca
    					us
    					pa
    					org
    					info
    					
    					Canada
    					United States
    					Panama
    					Public Interest Registry (PIR)
    					Afilias Limited
    					

You can add (), remove (), create () or edit () any of these functions. Data of custom functions are stored in SQLite database.

Xl-Tools - Custom Functions

  • Add (): You must select the SQLite database file to add. If the database file is not in the Customs subfolder, it will be copied. If the custom function title you add already exists, you will be prompted to modify it.
  • Remove (): You must select the function to be deleted prior to use this function.
  • Edit (): You must select the function to be modified prior to use this function. You can modify the title and the data. Take note that changing the title is the same thing as creating a new function. See also notes about Create function below.
  • Create (): Use this button to create a new function. GUI will change to Edit Custom Functions mode like the image above. Insert your title and data in the two lists and click Save. Take note that :
    • Number of items in List 1 must be exactly the same as the number of items in List 2.
    • When using the function, XL-Tools will match data found in List 1 to the corresponding data in List 2.

Top of the page

 

Credits to Free Website Templates for the template of this Website