MikroTik Script: Bulk create VPN users from a file

Creation of many VPN users at once is a rare MikroTik task, but it is found in the practice of a network administrator. The script creates many PPP (Point-to-Point Protocol) users from a CSV template file.

A comma-separated values (CSV) file is a delimited text file that uses a comma to separate values. Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the source of the name for this file format. A CSV file typically stores tabular data (numbers and text) in plain text, in which case each line will have the same number of fields.

Wikipedia

Content

1. Planned sequence of actions
2. Create script
3. Script code
4. Video: script work
5. Fixing issues

Article in other languages:
?? – MikroTik Script: Crear usuarios de VPN de forma masiva a partir de un archivo
?? – MikroTik Скрипт: Массовое создание VPN пользователей из файла
?? – MikroTik Script: Créer en masse des utilisateurs VPN à partir d’un fichier
?? – MikroTik-Script: Massenerstellung von VPN-Benutzern aus einer Datei
?? – MikroTik-script: Maak in bulk VPN-gebruikers aan op basis van een bestand

Planned sequence of actions

1. Download the CSV file template (FileTemplate.zip on GitHub);
2. Add users to the file (checked by Microsoft Excel);
3. Download the file to the MikroTik device;
4. Create a script for importing PPP users;
5. Turn on MikroTik Safe Mode;
6. Run the script;
7. Checking the device log and the list of PPP users;
8. If all is well, turn off MikroTik Safe Mode;
9. Write a comment to the article that everything went well (write the device model and the RouterOS version).

Create script

The following permissions are required to run the script: read, write, test, policy.

[System] -> [Scripts] -> [+] -> [Name: CreatePPPUsers] -> [Policy: read, write, test, policy]

Before running the script, I recommend turning on MikroTik Safe Mode, and turning off Safe Mode, making sure that the script worked correctly – VPN users were created.

Script code

# Name: CreatePPPUsers v1.1
# Description: Bulk create VPN users from a file
# Author: Yun Sergey, MHelp.pro 2020
# License: GPL-3.0 License
# Description, purpose and questions: https://mhelp.pro/mikrotik-script-bulk-create-vpn-users-from-a-file/
# More scripts Mikrotik: https://mhelp.pro/tag/mikrotik/

:local FileName "FileTemplate.csv";
:local Separator ",";

:log warning "Script CreatePPPUsers: running. Import from file: $FileName";

:if ([/file get $FileName size]  > 4096) do={
    :log error "Error run script CreatePPPUsers: file size exceeded 4 KB (size constraint of a variable in Router OS 6). Split the file $FileName into several parts.";
    :error "File size exceeded 4 KB. Stop script."
};

:local Content [/file get $FileName contents];
:local ContentLen [:len $Content];
:set Content [:pick $Content 62 $ContentLen];

:local StartCursor 0;
:local EndCursor;
:local LineEndCursor;

:while ($StartCursor < [:len $Content]) do={

    :set LineEndCursor [:find $Content "\r" $StartCursor];

    :local  Cont;

    :local ColumnsArray { "01Name"=""; "02Password"=""; "03Service"=""; "04Profile"=""; "05LocalAdress"=""; "06RemoveAddress"=""};

    # START PARSING STRING
    :foreach Key,Value in=$ColumnsArray do={

        :local Symbol [:pick $Content $StartCursor];

        :if ($Symbol=$Separator) do={:set StartCursor ($StartCursor - 1)};

        :set EndCursor [:find $Content $Separator $StartCursor];

        :if (($EndCursor > $LineEndCursor) or ([:typeof $EndCursor]="nil")) do={:set EndCursor [:find $Content "\r" ($StartCursor-1)];};

        :set Cont [:pick $Content $StartCursor $EndCursor];

        :set ($ColumnsArray -> $Key ) $Cont;

        :set StartCursor ($EndCursor+1);
    };
    # END PARSING STRING

    # START CREATE COMMAND
    :local UserName ($ColumnsArray -> "01Name");

    :if ([/ppp secret find name=$UserName ]) do={
        :log info "Add PPP user: $UserName - already exist! Skipped.";
    } else={
        :local Command "/ppp secret add name=$UserName";

        :local UserPassword ($ColumnsArray -> "02Password");
        :if ($UserPassword != $Separator) do= {:set Command ("$Command" . " password=$UserPassword")};

        :local UserService ($ColumnsArray -> "03Service");
        :if ($UserService != $Separator) do= {:set Command ("$Command" . " service=$UserService")};

        :local UserProfile ($ColumnsArray -> "04Profile");
        :if ($UserProfile != $Separator) do= {:set Command ("$Command" . " profile=$UserProfile")};

        :local UserLocalAdress ($ColumnsArray -> "05LocalAdress");
        :if ($UserLocalAdress != $Separator) do= {:set Command ("$Command" . " local-address=$UserLocalAdress")};

        :local UserRemoveAddress ($ColumnsArray -> "06RemoveAddress");
        :if ($UserRemoveAddress != $Separator) do= {:set Command ("$Command" . " remote-address=$UserRemoveAddress")};

        [:parse $Command];

    };
    # END CREATE COMMAND

    :set StartCursor ($EndCursor+2);
};
:delay 2;
:log warning "Script CreatePPPUsers: completed.";

In the line :local FileName “FileTemplate.csv”; you can give your import file name.

Video: script work

Fixing issues

Device log messages

1. Script CreatePPPUsers: running. Import from file: FileTemplate.csv— script started importing users from FileTemplate.csv file;
2. Error run script CreatePPPUsers: file size exceeded 4 KB (size constraint of a variable in Router OS 6)  — the size of the variable for RouterOS has been exceeded 6. Divide the file into small parts or import users in several stages;
3. Script CreatePPPUsers: completed — the script has finished correctly.

The script does not work at startup

Check the script launch rights, they must correspond to those specified in the article (an error was noticed in ROS 6.47.8 – the maximum rights set by default may cause an access error).

---

? MikroTik script – add multiple VPN (PPP) users, from csv file, discussed in this article. I hope that now adding a large number of VPN/PPP users to the MikroTik device will not be difficult. However, if you run into any problems while setting up, feel free to write in the comments. I will try to help.

✅ The script is verified: MikroTik hAP ac lite (RouterBOARD 952Ui-5ac2nD), RouterOS 6.47.8 (stable).