| Both sides previous revision Previous revision Next revision | Previous revision |
| import_and_export:import_items_and_stock [2020/12/04 00:09] – [Importing items and stock] Mark Glover | import_and_export:import_items_and_stock [2026/02/16 12:23] (current) – [17.02. Importing stock] Gary Willetts |
|---|
| {{indexmenu_n>20}} | {{indexmenu_n>20}} |
| ====== 16.02. Importing items and stock ====== | ====== 17.02. Importing stock ====== |
| == Importing items == | <WRAP center round important 60%> |
| 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). | **We strongly recommend: \\ |
| | \\ |
| | * You take a backup of your datafile before attempting any imports. |
| | \\ |
| | * You test any import on a copy of your data before you do it on the real data. |
| | \\ |
| | \\ |
| | Don't say you haven't been warned ;-)** |
| | </WRAP> |
| |
| {{ :import_and_export:item_stock_import.png? |}} | The Import stock function allows you to import stock into a store after the items have been created (manually or by using the item import function). |
| |
| == Importing items and stock == | Choose //File > Import > Import stock// from the menus. You will be shown a warning: |
| The //Import items and stock// command lets you import item codes, names //and the initial stock of those items//. | |
| |
| <WRAP center round alert> | {{ :import_and_export:2017-01-18_10_15_40-msupply_alert.png?350 |}} |
| * 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 <wrap em>strongly</wrap> 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: | |
| |
| {{ :import_and_export:pasted:20200923-044804.png }} | This is telling you to take care! The stock you are importing will be imported on top of the stock you already have in the store you are currently logged into. If you are not sure what you are doing, stop and make sure! |
| |
| Before proceeding with this command on your live system, we recommend this procedure: | After clicking the **OK** button you will be told the next step is to select the import file: |
| |
| - If you are on a synced system, [[preferences:synchronisation#editing_sync_preferences|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. | {{ :import_and_export:2017-01-18_10_16_55-alert....png?350 |}} |
| - Run a [[admin:backup_and_restore#running_a_backup_manually|manual backup]] before proceeding with this command. | |
| - Import the stock and check that no new items have been created. | |
| - If a new item was created, you'll need to fix it: | |
| - Investigate and find the suspect item(s). Contact Sussol if you need ideas on how to. | |
| - Fix the text file. | |
| - [[admin:backup_and_restore#restoring_your_data|restore from the backup]] | |
| - Repeat from step 3 | |
| - If the import succeeded without creating new items: | |
| - If you are on a synced system, [[preferences:synchronisation#editing_sync_preferences|edit sync preferences]] to un-pause syncing (by setting the sync time interval to ''1''. | |
| |
| <wrap em>You have been warned!</wrap> | Click the **OK** button on this window and an operating system window will open for you to select the import file. Find the file and double-click on it or click on it and click the **Open** button. |
| |
| </WRAP> | This window will open: |
| |
| == More than one batch of an item == | {{ :import_and_export:screenshot_2022-02-08_at_10.59.04.png?600 |}} |
| 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). | * **First line contains column headers checkbox:** Check this by clicking on it if the first line of your file contains the column headers. If this checkbox is checked, mSupply will ignore the first row of the import file and only import from the second row onwards. |
| | * **Remove quote characters on import checkbox:** Check this if you want mSupply to remove quote (''"'') characters from the various fields as it does the import. This is often needed if your import file has come from Excel or another spreadsheet application. You can check if there are unwanted quote characters in the fields using the preview **Next** and **Previous** buttons (see below). |
| | * **Next and Previous buttons:** Use these to move through the rows in the import file. mSupply will show you the information is is going to import from each field in the import file. You will be shown a warning if you try to view lines before the start or after the end of the file. |
| |
| == Note on pack sizes == | If you spot any problems, click on the **Cancel** button, fix them and try again. If all is well, click on the Import button and mSupply will carry out the import. A progress bar will show you how far through the whole process mSupply is. When it gets to the end you will be shown a message telling you how many lines were imported successfully and how many were skipped (because the item could not be identified - because the item code was missing or did not match an item in your datafile). |
| 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) | [[items:department_management|Item Department]] | Text, up to 30 characters. If you import a department name that doesn't exist, the department will be created. | | |
| | 12 (L) | [[items:item_locations|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: {{ :import_and_export: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. | |
| | |
| {{ :import_and_export:2017-01-18_09_53_02-png_stock_data_entry_template_161207.xlsx_-_excel.png |}} | |
| |
| <WRAP center round important 60%> | <WRAP center round important 60%> |
| Make sure every line has an item code assigned: any lines without an item code will be skipped when importing. | Any lines in the import file that belong to items that are **invisible** in the current store will be ignored and not imported. An alert message at the end of the import will inform you of any lines that were not imported because of this. For information on item visiobility in stores, see the [[other_stuff:virtual_stores#master_lists_tab_and_visibility_tab_controlling_item_visibility|26.07. Virtual stores]] page. |
| </WRAP> | </WRAP> |
| | ==== Import file format ==== |
| | * The import file must be a tab delimited text file. |
| | * The first row can contain the column titles but all other rows in the file should contain data to be imported. |
| | * The table below details the fields you should have (and the order they must be in) in each row of the file. |
| |
| Once you have completed the spreadsheet list: | ^ Column ^ Field ^ Description ^ |
| | | 1 (A) | Item code | **Mandatory**, Text, up to 18 characters. mSupply uses this to recognise which item this row of stock belongs to. If this code is missing or does not belong to an item already in your datafile, this line of the import file will be skipped. | |
| * Save the file as a tab-delimited text file. | | 2 (B) | Item name | **Optional**, ignored during the import. Text, up to 80 characters. The name of the item, here for reference so you can easily tell which item each row refers to. | |
| * Open **mSupply** and choose // File > Import items & stock//. | | 3 (C) | Units | **Optional**, ignored during the import. Text up to 60 characters, for your reference only. The unit the item is to be measured in e.g. Tablet, Each, ml, g, bottle, vial, Ampoule. | |
| * The following alert windows pop up, click OK to proceed on each one. | | 4 (D) | Pack size | **Mandatory**, Integer. The number of units in a single pack for this stock line. | |
| | | 5 (E) | Quantity | **Mandatory**, Integer. The number of packs (containing the number of units in column 4) in this stock line. Equal to the total quantity if the Pack size is ''1''. | |
| {{ :import_and_export:2017-01-18_10_15_40-msupply_alert.png |}} | | 6 (F) | Cost price | **Optional**, Real number. The price you pay for one pack. | |
| | | 7 (G) | Sell price | **Optional**, Real number. The price the customers of this store pay for one pack. Set this to the same as the cost price or leave it empty if you do not actually sell the stock that you issue. | |
| {{ :import_and_export:2017-01-18_10_16_55-alert....png |}} | | 8 (H) | Batch | **Optional**, Text, Up to 20 characters. The batch of this stock line. | |
| | | 9 (I) | Expiry | **Optional**, Text, in short format (dd/mm/yy or mm/dd/yy, depending on the format set in your computer's operating system). The date this stock line will expire. | |
| * You will then be prompted to select the file to be imported. | | 10 (J) | Description | **Optional**. Text, Up to 32,000 characters. The 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! | |
| * After selecting the import file. The following dialogue box will open. Make selections as described below. | | 11 (K) | [[items:department_management|Item Department]] | **Optional**, Text, up to 30 characters. If you import a department name that doesn't exist, the department will be created. | |
| | | 12 (L) | [[items:item_locations|Shelf Location]] | **Optional**, Text, Up to 60 characters. The code for the shelf location of this stock line.\\ If the location does not exist, the location will be created as the stock is loaded. You can then edit the location details after the import.| |
| {{ :import_and_export:2017-01-18_10_24_35-import_item_and_stock....png |}} | | 13 (M) | Donor | **Optional**, Text, Up to 60 characters. The name of the donor of this stock line. If the donor doesn't exist, it will be created. | |
| | | 14 (N) | [[receiving_goods:custom_stock_fields#custom_stock_fields|Custom stock field 5]] | **Optional**, Text. The value to be entered in custom stock field 5 for this batch. If the value doesn't already exist as an option for custom stock field 5 then it will be added. See the [[receiving_goods:custom_stock_fields]] page for details of custom stock fields. | |
| - Tick this box if the first line of the import file contains column headers. This is normally the case :-) | | 15 (O) | Outer pack size | **Optional**, Integer. The number of packs that fit in a carton (NOT the number of units in a carton!). | |
| - 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. | | 16 (P) | Volume per pack | **Optional**, Decimal number. The volume that a single pack of this batch occupies on the shelf in m<sup>3</sup>. | |
| - 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 [[import_and_export:importing_items|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. | | 17 (Q) | Weight per pack | **Optional**, Decimal number. The weight of a single pack of this batch in kg. | |
| - 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. | | 18 (R) | Manufacturer | **Optional**, Text. The code of the manufacturer of this batch. If a manufacturer with this code doesn't exist in the datafile then it is created with the Code, Name and Charge to values all set to the contents of this cell. | |
| - Click **Import now** to proceed, or **Cancel** if you have cold feet... | |
| | |
| <WRAP center round tip 60%> | |
| 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. | |
| |
| | <WRAP important center round 90%> |
| | **Please note:** It is not part of the import file but, if an item's default Volume per pack is set (see the **Volume per pack** field on the [[items:item_basics:tab_storage]] page for details) then this will be applied to the stock line's volume per pack. The value will be proportionately increased or decreased if the stock line's pack size differs from the item's default pack size (**Preferred pack size** field on the [[items:item_basics:tab_storage]] page). |
| </WRAP> | </WRAP> |
| |
| mSupply does a quick scan of the import file, and may come up with the following alert: | Here is a blank spreadsheet you can use with the right columns: {{ :import_and_export:msupply_stock_import.xlsx |}} |
| | |
| {{ :import_and_export:2017-01-18_10_27_06-alert....png |}} | |
| | |
| 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: | **Note**: the first row of data in red is an example row - delete it before you import it! |
| |
| {{ :import_and_export:2017-01-18_10_27_52-alert....png |}} | You can create the import file in various ways: |
| | - Manually using the blank template above. |
| | - Manually using any text editor. |
| | - Export the current stock from a store in this or another mSupply datafile (and modify it if necessary) and import that. |
| |
| This message needs interpreting: | If you use a spreadsheet application like Excel remember to export the file in Tab delimited text format before trying to import it into mSupply. |
| |
| * **Number of new items imported:** This should only be >0 if you selected the **Update item details and add stock** radio button | You can import as many batches of an item as you wish, there is no limit. And it doesn't matter whether batches of the same item are next to each other in the import file. |
| * **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. | |
| |
| \\ | \\ |