17.01. Importing items
You can import a tab-delimited text file of items by choosing File > Import > Import items….
- This command only imports (creates) items. You will need to do this when you are configuring mSupply for the first time, or when you are wanting to create a number of new items to your system.
- This process does not introduce stock. If you would like to import stock, see the 17.02. Importing stock page.
During import you can specify a number of things including:
- What happens with duplicates
- Which master lists the new items will be added to.
- Which stores the new items will be visible in (if master lists aren't controlling visibility)
After selecting the menu item you are shown a message asking you to select the file to be imported on the next window:
After clicking OK, a window opens allowing you to select the file to import. After selecting it (by double clicking on the file in the window or clicking on it and clicking on the Open button) this window is displayed:
First line contains column headers: If you have used the first line of your import file for column headers, check this box. Then mSupply will ignore the first row in the file, and start importing items from the second row.
Remove quote characters on import: If this is checked then double quote characters ("
) are removed from item names as they are imported. This is useful if you have exported the import file from spreadsheet software and it has placed double quote characters around the text.
If you have used double quote characters in item names (e.g. as a symbol for inches) then they will also be removed if this option is checked.
Handling duplicate items: During the import process, mSupply compares the item code in the import file (1st column - see below) against the codes of items already in its database to determine whether an entry is a duplicate item or not. If a duplicate item is found in the import file mSupply will handle it according to your radio button selection in the If duplicate codes are selected section:
- Create new duplicate items and continue and Ask each time options are disabled for Item Import.
As of v5 it should not be possible to create an item with the same code as another item. Hence, item with duplicate Item code will be ignored.
- Skip the duplicate without asking: the duplicate is ignored and not imported.
- Stop the import: if a duplicate is detected mSupply will stop and cancel the whole import i.e. the database will be exactly that same as it was before you started the import.
- Update the existing item: the item with the same code already in mSupply will be updated with all the details of the item in the import file.
Preview tab
This tab enables you to see a preview of what will be imported into mSupply and helps you check that mSupply is reading what you expect from the import file.
The left hand column of the table (“Field”) shows you all the fields mSupply is expecting to see (in that order, first at the top, last at the bottom of the table). The right hand column (“Value”) shows you the value that mSupply has read from the file for that field. Use the Next and Previous buttons to move between the records in the import file.
Stores tab
If the preference Automatically update item visibility in all stores to match master lists used by that store (see Preferences > Item) is checked, then item visibility is controlled by master lists and you do not have the option of selecting stores in which the item is visible. The table on this tab will be replaced with this message telling you to use the Master lists tab instead:
Item visibility in each store will be turned on in accordance with the master lists you have selected, but visibility won't be turned off in stores in which the item is already visible but not associated with the selected master lists. This prevents inadvertently removing existing items from stores when your intention is to import items to specific master lists.
- This is the tab you use to specify which stores the items you import will be visible in.
- The store you are logged into is ticked by default. Simply check the “Visibility” column checkbox for the stores you wish all imported items to be visible in.
Only imported item visibility is affected during the import. If a duplicate item is found in the import file then:
- If you choose to ignore duplicates, then the visibility of the item already in mSupply is not affected by the import process.
- If you chose to update duplicates, then the visibility of the item already in mSupply will be updated to match what you have set on this tab.
Master lists tab
This is the tab you use to determine which master lists the items in the import file will be added to as they are imported. Simply check the “Add to list” column checkbox for the master lists you wish all the items in the import file to be added to.
Any master lists that have the “Automatically add new items to this master list” option checked will have their “Add to list” checkbox checked by default on this tab and you will not be able to uncheck them.
Note that only imported items will be added to the master lists selected. This means that items already in mSupply which match duplicates in the import file that you choose to ignore will not be added to the selected master lists. Similarly, items already in mSupply which are updated because they match duplicates in the import file and the “Update the existing item” option was chosen are added to the selected master lists. And don't worry, if an item is already on a msster list it won't be added again.
Import file format
Field order for the text file:
Column number | Column letter | Field | Description |
---|---|---|---|
1 | A | item code | 18 alphanumeric characters (must be a unique value) |
2 | B | item name | 80 alphanumeric characters |
3 | C | units | 3 alphanumeric characters recommended (ea, ml or mg). Entries in the units list will be created if you import a unit that does not already exist in the data file. 60 characters supported |
4 | D | reference pack size | integer |
The above fields are mandatory, while the following fields are optional and may or may not be included as per your requirements |
|||
5 | E | Default shelf location code | 60 alphanumeric characters. The code of the shelf location that stock of this item is normally placed in. Will be offered by default when stock of the item is received on a goods received note or supplier invoice. |
6 | F | user field 1 | 30 alphanumeric characters |
7 | G | user field 2 | 30 alphanumeric characters |
8 | H | user field 3 | 30 alphanumeric characters |
9 | I | user field 4 | “true” or “false” |
10 | J | edl | “true” or “false” |
11 | K | ATC category | 30 alphanumeric characters |
12 | L | ddd value | 8 alphanumeric characters- the ddd and its units |
13 | M | ddd factor | number greater than zero (default=1) |
14 | N | item description | The long description for the item. Do not include carriage returns (new lines made with the return or enter key) in your text file. |
15 | O | item department | The name of the department that the item belongs to. If the department does not exist, mSupply will create it. |
16 | P | account code for stock | The account code used for stock purchases . Note that these accounts will be created if they don't exist. |
17 | Q | account code for cost of goods sold | |
18 | R | income account code | |
19 | S | Default sell price | |
20 | T | Category | Category 1. This category is hierarchical and has 3 levels. You can enter different levels separated by :: (for example “Pharmaceuticals::Controlled::Anti-depressants” or “Controlled::Anti-depressants”) or just the lowest level with no separator (for example “Anti-depressants”). mSupply will look for an existing category with the levels you define, if it doesn't find one then it will create it and assign the category to the name. If you enter only the lowest level category (for example “Anti-depressants”), mSupply will look for any lowest level category “Anti-depressants” 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. We strongly recommend you use the WHO EDL categories for this field |
21 | U | Category 2 | 60 alphanumeric characters |
22 | V | Category 3 | 60 alphanumeric characters |
23 | W | VEN category | The WHO VEN category of the item. One character only, must be V, E or N, will be ignored of it is neither of these. |
23 | X | Weight | The weight per default pack size (column D) in kg |
24 | Y | Critical stock | “true” or “false” |
25 | Z | Normal stock | “true” or “false” |
26 | AA | User field 5 | number |
27 | AB | User field 6 | 30 alphanumeric characters |
28 | AC | User field 7 | “true” or “false” |
29 | AD | Default volume per pack | The volume of a single pack of the default pack size in m3. This volume is filled in by default when receiving stock but can simply be overridden. |
30 | AE | Default outer pack size | The number of default packs that fit in a carton. Filled in by default when receiving goods but can be simply overridden. |
31 | AF | Default outer pack volume | The volume of an outer pack in m3, filled in by default when receiving goods but can be simply overridden. |
32+ | AG+ | Item custom fields | After column AF (31), any custom item 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 item's appropriate custom field. |
All these are in this handy template: mSupply Item Import template
The chances are that you already have a set of codes that you use internally. In this case open up Excel or a similar spreadsheet program, fill in the item description in the order shown above. For a quick solution, you only need to fill in the first four columns, the rest can be ignored.
The third column representing “Units”, can be left blank, however, it is better to fill this in. The fourth column “reference pack size” can be set to “1” for all rows unless you know what pack size you want to use as the “standard” (Don't worry about this. The reference pack is used for comparing quotations. You can receive and issue any pack size you like).
Save a spreadsheet file as shown above as a tab-delimited text file, and then import it as described at the top of this page.
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 there are non-printable characters before or after the item name and code they will be removed. Any non-printable characters in the name or code will be replaced with an underscore (_).
Import new item codes
If you need to change the item codes for a selection of items, it is a technically challenging process that is easy to get wrong if done manually, especially if you intend to re-use existing codes (not recommended!). Custom code has been developed to do this in bulk. Please contact Sustainable Solutions on support@msupply.foundation to discuss this. Include a link to this heading Import new item codes .
The relevant details are:
- Method:
importCsvToUpdateItemCode
Previous: 17. Data Import and Export | | Next: 17.02. Importing stock |