17.02. Importing items and stock

Importing items

On the File menu, choosing Import items…. This only imports item codes and descriptions. This still leaves you with the task of entering quantities for all items (usually by entering an inventory adjustment).

Importing items and stock

The Import items and stock command lets you import item codes, names and the initial stock of those items.

  • While you can create new items by use of this method, creating items should be a carefully controlled process and we do not recommend this method of doing it.
  • We strongly recommend that this import process be used only for the purposes of loading in stock of items that already exist in the system.
  • We recommend that you test this import on a copy of the live data file to ensure that all the stock you are loading is identified with existing item codes.
  • The message you get at the end of the import will tell you whether the process has ended up creating a new item:

Before proceeding with this command on your live system, we recommend this procedure:

  1. If you are on a synced system, edit sync preferences to pause syncing (by setting the sync time interval to 0 and wait for batch of currently syncing records to finish syncing.
  2. Run a manual backup before proceeding with this command.
  3. Import the stock and check that no new items have been created.
  4. If a new item was created, you'll need to fix it:
    1. Investigate and find the suspect item(s). Contact Sussol if you need ideas on how to.
    2. Fix the text file.
    3. Repeat from step 3
  5. If the import succeeded without creating new items:
    1. If you are on a synced system, edit sync preferences to un-pause syncing (by setting the sync time interval to 1.

You have been warned!

More than one batch of an item

It is possible to enter in various batches of an item separately. For example, if your import file contains two batches of “ace250t” on separate rows. mSupply will first fill in the item description and code and then enter in the stock for the first batch the item. For the next line of data, mSupply carries out a search for the code, and adds another batch as the same item already exists, and then updates the total quantity.

It is not necessary to group the same batches together, but making sure that various batches of the same item have the same identical code is important, otherwise extra items will be created. The actual full name of the product is taken from the first batch the program encounters; subsequent batches that have the same code as an item that already exists in the data file will use the item name of the exisiting item. (You can even leave the item name blank for subsequent lines if you want to).

Note on pack sizes

The “pack size” and “quantity” field should be entered taking into account the way you will issue stock (For example, say you are importing Amoxycillin stock of 3000 capsules that are in boxes of 100. If you are going to issue stock of Amoxycillin individually (28 to this patient, 21 to another patient), then enter a pack size of “1” and a quantity of “3000”. If you will only issue whole boxes, use a pack size of “100” and a quantity of “30”. If you enter a pack size and quantity that does not fit, you will still have the option of repacking the items (using the “repack” function from the “item” menu) to meet your supply needs.

Duplicate options

You will notice several options for what to do when a duplicate is found. The first three are disabled because these are for importing items only, the others are available for choosing:

  • Stop the import: Stops the import and removes any stock already imported before the duplicate was found (i.e. the data is exactly as it was before the import was started).
  • Update item details and add stock: If this option is chosen, you will effectively add all stock in the import file on to the stock already recorded in mSupply, and also update the item details with any changes recorded in the import file.
  • Update stock only: If this option is chosen, you will effectively add all stock in the import file on to the stock already recorded in mSupply, but not change any item details.
Field order for import file
Column Field Note
1 (A) Item code Text, up to 18 characters
2 (B) Item name Text, up to 80 characters
Both of the above fields are mandatory for the rows to be interpreted in the import process at all.
3 (C) Units Text up to 60 characters, but recommend ea, ml, mg etc.
4 (D) Pack size Integer.
5 (E) Quantity Integer. The number of packs. This is only equivalent to the total quantity if the pack size is 1.
The above three fields are mandatory if you are actually adding new stock, as distinct from just updating item details.
The following fields are optional and may or may not be included.
6 (F) Cost price Real number. The price you pay for one pack
7 (G) Sell price Real number. The selling price of one pack. Set to the same as the cost price if you do not actually sell the stock that you issue.
8 (H) Batch Text, Up to 20 characters
9 (I) Expiry Text, in short format (dd/mm/yy or mm/dd/yy, depending on the format set in your computer's operating system)
10 (J) Description Text, Up to 32,000 characters. Full description of the item. Be careful not to have any carriage return characters in the text though, or the import file will not be valid!
11 (K) Item Department Text, up to 30 characters. If you import a department name that doesn't exist, the department will be created.
12 (L) Shelf Location Text, Up to 60 characters. The code for the shelf location of the batch being imported.
Note that this does not set the default location for the item; only the location of this particular stock line.

In order to create a text file for importing purposes, first you need to enter your stock details into a spreadsheet (e.g. Microsoft Excel). It is useful for the first row to be populated by column descriptions. If you do this, remember to tick the relevant tick-box during the import process.

Here is a blank spreadsheet with the right columns you can use: msupply_stock_import.xlsx Note: the first row of data in red is an example row - delete it before you import it!

As mentioned in the table above, you can omit the values in any column after the first five.

Make sure every line has an item code assigned: any lines without an item code will be skipped when importing.

Once you have completed the spreadsheet list:

  • Save the file as a tab-delimited text file.
  • Open mSupply and choose File > Import items & stock.
  • The following alert windows pop up, click OK to proceed on each one.

  • You will then be prompted to select the file to be imported.
  • After selecting the import file. The following dialogue box will open. Make selections as described below.

  1. Tick this box if the first line of the import file contains column headers. This is normally the case :-)
  2. Tick this box to remove quote characters on import. Quote characters will be added by Excel or other equivalents if there is a space character in the field, which is often the case for the Item name field. Quote characters are not normally part of a valid item description. It is generally safer to tick this tick-box.
  3. Tick the radio-button accordingly. If you are loading stock into the store for the first time, and you have not already defined / loaded the items with Importing Items (refer tip box below), then you should tick the Update item details and add stock radio button. If you are just loading stock in to a store and you don't want any new items created, the default option of Update stock only is appropriate.
  4. Clicking on the Previous and Next buttons allows you to step through the import file so as to review what will be imported, and particularly that the fields have matched up correctly.
  5. Click Import now to proceed, or Cancel if you have cold feet…

mSupply allows the Import Items and Stock function to both add new stock to the store, and add new items to the whole system. Particularly in multi-store environments, it is good practice to separate these two processes.

  • Importing stock into a store, which may be done relatively frequently - every time a new store is added to the system.
  • Creating new items, which should be done rarely and with care, and not accidentally while importing stock!

It is therefore important which radio button is selected in the above dialogue box.

mSupply does a quick scan of the import file, and may come up with the following alert:

This is not necessarily a problem. In fact, you will always get this message if the import file was created by MS Excel and you haven't manually edited out the <CR><LF> at the end of the last line of data! If you have edited out the last <CR><LF> (recommended), then you likely have a problem with your import file which needs to be investigated. Unfortunately, you are committed to doing the import at this point. That's why you are testing this on a dummy version of your data file, right? ;-)

The import process will show a progress bar and then the following message once the import is complete:

This message needs interpreting:

  • Number of new items imported: This should only be >0 if you selected the Update item details and add stock radio button
  • Number of old items edited: This should only be >0 if you selected the Update item details and add stock radio button
  • Number of items skipped: This should be =1 if you did not edit out the last <CR><LF> in the import file. If it is >1, then some lines of the import file have a blank name or code, which is likely a problem and needs to be investigated.

If there are non-printable characters before or after the item name they will be removed. Any non-printable characters in the name will be replaced with an underscore (_).

  • Last modified: 2021/10/08 09:30
  • by Gary Willetts