| Both sides previous revision Previous revision Next revision | Previous revision |
| import_and_export:import_items_and_stock [2020/09/23 05:19] – [Importing items and stock] Mark Glover | import_and_export:import_items_and_stock [2023/04/21 03:10] (current) – [Import file format] Mark Glover |
|---|
| {{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: \\ |
| | \\ |
| {{ :import_and_export:item_stock_import.png? |}} | * You take a backup of your datafile before attempting any imports. |
| | \\ |
| == Importing items and stock == | * You test any import on a copy of your data before you do it on the real data. |
| The //Import items and stock// command lets you import item codes, names //and the initial stock of those items//. | \\ |
| | \\ |
| <WRAP center round alert> | Don't say you haven't been warned ;-)** |
| * 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 }} | |
| | |
| <wrap em>You have been warned!</wrap> | |
| | |
| Before proceeding with this command on your live system, we recommend this procedure: | |
| | |
| - 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. | |
| - 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> | </WRAP> |
| |
| == More than one batch of an item == | 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). |
| 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). | Choose //File > Import > Import stock// from the menus. You will be shown a warning: |
| |
| == Note on pack sizes == | {{ :import_and_export:2017-01-18_10_15_40-msupply_alert.png?350 |}} |
| 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 == | 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! |
| 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 == | After clicking the **OK** button you will be told the next step is to select the 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. | {{ :import_and_export:2017-01-18_10_16_55-alert....png?350 |}} |
| |
| 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! | 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. |
| |
| As mentioned in the table above, you can omit the values in any column after the first five. | This window will open: |
| |
| {{ :import_and_export:2017-01-18_09_53_02-png_stock_data_entry_template_161207.xlsx_-_excel.png |}} | {{ :import_and_export:screenshot_2022-02-08_at_10.59.04.png?600 |}} |
| |
| <WRAP center round important 60%> | * **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. |
| Make sure every line has an item code assigned: any lines without an item code will be skipped when importing. | * **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). |
| </WRAP> | * **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. |
| |
| Once you have completed the spreadsheet list: | 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). |
| |
| * 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. | |
| |
| {{ :import_and_export:2017-01-18_10_15_40-msupply_alert.png |}} | ==== 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. |
| |
| {{ :import_and_export:2017-01-18_10_16_55-alert....png |}} | ^ 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. | |
| * You will then be prompted to select the file to be imported. | | 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. | |
| * After selecting the import file. The following dialogue box will open. Make selections as described below. | | 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. | |
| | | 4 (D) | Pack size | **Mandatory**, Integer. The number of units in a single pack for this stock line. | |
| {{ :import_and_export:2017-01-18_10_24_35-import_item_and_stock....png |}} | | 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''. | |
| | | 6 (F) | Cost price | **Optional**, Real number. The price you pay for one pack. | |
| - Tick this box if the first line of the import file contains column headers. This is normally the case :-) | | 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. | |
| - 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. | | 8 (H) | Batch | **Optional**, Text, Up to 20 characters. The batch of this stock line. | |
| - 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. | | 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. | |
| - 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. | | 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! | |
| - Click **Import now** to proceed, or **Cancel** if you have cold feet... | | 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.| |
| <WRAP center round tip 60%> | | 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. | |
| 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. | | 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. | |
| * **Importing stock into a store**, which may be done relatively frequently - every time a new store is added to the system. | | 15 (O) | Outer pack size | **Optional**, Integer. The number of packs that fit in a carton (NOT the number of units in a carton!). | |
| * **Creating new items**, which should be done rarely and with care, and not accidentally while importing stock! | | 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>. | |
| | | 17 (Q) | Weight per pack | **Optional**, Decimal number. The weight of a single pack of this batch in kg. | |
| 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. | |
| |
| \\ | \\ |