import_and_export:import_items_and_stock

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision		 Previous revision
import_and_export:import_items_and_stock [2022/02/08 12:52] – Gary Willettsimport_and_export:import_items_and_stock [2023/04/21 03:10] (current) – [Import file format] Mark Glover
Line 1: Line 1:
 {{indexmenu_n>20}} {{indexmenu_n>20}}
 ====== 17.02. Importing stock ====== ====== 17.02. Importing stock ======
 +<WRAP center round important 60%>
 +**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>
 +
 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). 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).
  
 Choose //File > Import > Import stock// from the menus. You will be shown a warning: Choose //File > Import > Import stock// from the menus. You will be shown a warning:
  
-{{ :import_and_export:2017-01-18_10_15_40-msupply_alert.png |}}+{{ :import_and_export:2017-01-18_10_15_40-msupply_alert.png?350 |}}
  
 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! 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!
Line 11: Line 22:
 After clicking the **OK** button you will be told the next step is to select the import file: After clicking the **OK** button you will be told the next step is to select the import file:
  
-{{ :import_and_export:2017-01-18_10_16_55-alert....png |}}+{{ :import_and_export:2017-01-18_10_16_55-alert....png?350 |}}
  
 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. 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.
Line 24: Line 35:
  
 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). 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).
- 
- 
- 
- 
  
  
 ==== Import file format ==== ==== 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. These are the fields you should have (and the order they must be in) in each row of the file:+  * 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.
  
 ^ Column  ^ Field            ^ Description                                                                                                                                                                                           ^ ^ 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. | +| 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. | 
-| 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.  | +| 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.  | 
-| 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.  | +| 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.  | +| 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''.  | +| 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.  | +| 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.  | +| 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.  | 
-| 8 (H)   | Batch            | Optional, Text, Up to 20 characters. The batch of this stock line.  | +| 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.  | +| 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.  | 
-| 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!  | +| 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!  | 
-| 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.  | +| 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.  | +| 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.| 
-| 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.  |+| 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. | 
 +| 15 (O)  | Outer pack size | **Optional**, Integer. The number of packs that fit in a carton (NOT the number of units in a carton!). | 
 +| 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. | 
 + 
 +<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>
  
 Here is a blank spreadsheet you can use with the right columns: {{ :import_and_export:msupply_stock_import.xlsx |}} Here is a blank spreadsheet you can use with the right columns: {{ :import_and_export:msupply_stock_import.xlsx |}}
Line 52: Line 69:
 **Note**: the first row of data in red is an example row - delete it before you import it! **Note**: the first row of data in red is an example row - delete it before you import it!
  
-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. +You can create the import file in various ways: 
- +  - Manually using the blank template above. 
-== More than one batch of an item == +  - Manually using any text editor. 
-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. +  - Export the current stock from a store in this or another mSupply datafile (and modify it if necessary) and import that.
- +
-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). +
- +
- +
- +
-{{ :import_and_export:2017-01-18_09_53_02-png_stock_data_entry_template_161207.xlsx_-_excel.png |}} +
- +
-<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. +
-</WRAP> +
- +
-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. +
- +
- +
- +
-{{ :import_and_export:2017-01-18_10_24_35-import_item_and_stock....png |}} +
- +
-  - Tick this box if the first line of the import file contains column headers.  This is normally the case :-) +
-  - 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. +
-  - 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. +
-  - 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. +
-  - 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> +
- +
-mSupply does a quick scan of the import file, and may come up with the following alert: +
- +
-{{ :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: +
- +
-{{ :import_and_export:2017-01-18_10_27_52-alert....png |}}+
  
-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. +
-  +
-<WRAP info center round 60%> +
-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 (<wrap em>_</wrap>). +
-</WRAP>  +
  
 \\ \\
  • Last modified: 2022/02/08 12:52
  • by Gary Willetts