17.03. Importing names
In mSupply, Names are customers, suppliers, manufacturers, patients, donors and benchmark suppliers.
Introduction
- Importing a large number of names can really bog down the server. How much depends on many factors, but a realistic minimum is an hour per 10,000 names. This will increase dramatically with the number of names and sync sites that are visible to the names…
- We recommend that you do it when other users are not trying to do anything with mSupply!
The File > Import > Import customers & suppliers… menu item allows you to import all name types into an mSupply data file. You can import any name type in any store.
- If you are in a multi store system, do the names need to be visible in all stores? If not, consider setting the Names created in other stores not visible in this store or Patients created in other stores not visible in this store preferences in other stores. See the Store preferences section for details.
- If the relevant preference is turned on, all new names will be visible in the store you are logged into only. If it is turned off, then all new names will be visible in all stores.
Setting up the import file
A Tab-delimited file (see notes below) must be set up to import the data. The required field order is:
Column | Field |
---|---|
A | Code. Mandatory |
B | Name. Mandatory |
C | Type - see below for more details. Mandatory |
D | Address 1 |
E | Address 2 |
F | Country |
G | |
H | Phone |
I | Category 1. This category is hierarchical and has 3 levels. You can enter different levels separated by :: (for example “State1::District2::Area1” or “District1::Area3”) or just the lowest level with no separator (for example “Area2”). mSupply will look for an existing category with the levels you define, if it doesn't find one then it will create them and assign the category to the name. If you enter only the lowest level category (for example “Area1”), mSupply will look for any lowest level category “Area1” with any or no parent. If it finds one, it will assign the first one it finds to the name being imported, otherwise it will create it with no parent and assign it to the name |
J | Customer group. If the group name does not exist, it will be created by mSupply on the fly. |
K | Last name. This column is the difference between a customer and a patient: A customer will have a blank last name, a patient must have a non-blank last name |
L | First name (applies only to people) |
M | Date of birth (use 4-digit years) |
N | Gender (enter “F” or “True” for females. All other values taken as male) |
O | Custom 1 |
P | Custom 2 |
Q | Custom 3 |
R | Category 2 |
S | Category 3 |
T | Address 3 |
U | Address 4 |
V | Address 5 |
W | Postcode (Zip code) |
X | On hold (“T” or “Y” for true, anything else, including blank, for false) |
Y | Latitude (positive or negative decimal number) |
Z | Longitude (positive or negative decimal number) |
AA | ID. The ID of the name record in the mSupply database. Optional and ignored unless updating a name. If you're updating a name then it is mandatory. During an update, the name this ID belongs to is compared with the name the code in column A belongs to. If they are the same record then the update will proceed, otherwise this line in the import file will be skipped. |
AA+ | After column AA, any custom name fields set up in the preferences (see the 16.11. Custom fields section for details) can be added. The column header must be exactly the name of the custom field (case insensitive). The values entered in these columns will be saved to the name's appropriate custom field. |
Column C describes the type of the name being imported. That is, whether it is a customer, supplier, manufacturer, donor or patient. A name could be any one of these or several of them. To indicate which of these the name is, simply include the letter shown in the table below in the type field (letter order is not important):
Value | Meaning |
---|---|
C | Customer or Patient. To be recognised as a patient, the line in the import file must have a non-blank last name |
P | Patient |
S | Supplier |
M | Manufacturer |
D | Donor |
B | Benchmark supplier (for use in tenders) |
(blank) | Both a customer and a supplier |
So, for example, a name which is a donor and supplier would have DS
or SD
in the type field.
You might find this template useful: name_import_template.xlsx or this export settings file: names_export_order.4si.zip
The first 3 fields (Code, Name and Type) are mandatory for each name imported. If a row in the import file does not have values for these 3 fields then it will be skipped and ignored during the import process.
- The customer or supplier category1, 2 and 3 fields will be created if they don't already exist. If the name is a customer and a supplier, a customer category will be created.
- You must always include every column in every import file. For all names except patients, the first 3 columns are mandatory. You can leave the other columns blank and mSupply will only import the information you have provided. See below for more on importing patients.
- When you import the file you can choose whether or not to:
- Ignore the first line. You will want to if you have used the first row for column headings
- Strip out line feeds and quote characters. You will want to do if you've used Excel to save the text file.
- If there are non-printable characters before or after the name, code, last name or first name they will be removed. Any non-printable characters in the name, code, last name or first name will be replaced with an underscore (_) - but note that spaces in the code will not be affected.
- Save the template as tab-delimited text (rows end with a carriage return, line feed and columns are separated with a tab character) before importing into mSupply.
- MS Excel tends to add extra characters (line feeds and quotes), but mSupply will gracefully remove these without complaining providing you select that option -see info box above.
- Avoid having the Code field containing a space character as this can cause problems during import
- If the file you are importing has non-English characters (for example, accents), it is important to make sure the file is saved with a UTF-8 charset. You can check this by opening the file in Notepad (for Windows). When you save the file, you can choose the Charset used, as shown in image below.
If you import patients into a store which is not a dispensary, they will be imported but they will be invisible everywhere. You must make them visible in the appropriate dispensary stores before they can be used.
Importing the file
When you choose File > Import > Import customers & suppliers… you'll be shown an open/save window where you choose the file to import.
Once you've done that, you'll be shown this window:
If the first line of your import file contains column headers then check the First line contains headers checkbox. This tells mSupply to ignore the first line and start importing form the second line.
Check the Remove quote characters on import checkbox if some of the fields in the import file have had quote marks added to them. This can often happen if you have created the import file by exporting it from Excel or some other spreadsheet application.
Now, in the If duplicate codes are selected section, select what you want to happen if a name code is found in the import file which is already used by a name in mSupply.
You can use the Previous and Next buttons to go through the import file one line at a time to see what mSupply can see in each field and therefore what it will try to import. Can be useful for checking whether extra quote characters have been added to any fields or if the first line of the text file contains column header information only.
Importing patients
If you are importing patient names, leave column B (the full name) blank and make entries in the Last name (col K) and First name (col L) columns. Below is an example of a spreadsheet for importing patients, customers and suppliers which demonstrates how to fill in the fields with respect to each import type.
Column K is what defines the difference between a customer and a patient: A customer will have a blank last name, a patient must have a non-blank last name. A customer can have a blank first name.
If you have a lot of patients there will be a short delay once you have chosen the import file while mSupply counts how many records there are to import, then a progress window will be shown displaying the rate of progress:
Update Name codes in bulk
If you need to change many Name Codes at once, custom code has been developed for this purpose. It is a technically challenging process that is easy to get wrong. Please contact the mSupply Foundation on <support@@msupply.foundation> to discuss this. Include a link to this heading Update Name code in bulk .
The relevant details are:
- Method:
z_importExcelToUpdateNameCode
- GitHub issue Code to replace name codes #13670
- Some notes Here
Previous: 17.02. Importing stock | | Next: 17.04. Importing prescribers |