Importing and updating of items data from Excel or CSV

The LoMag warehouse program allows you to import and update an item list and its prices according to specific schemes. Through this program, it is possible to import main values ​​such as: name, suggested prices, status. In addition, the tool allows you to import dedicated columns. The LoMag program can read Excel file or a file with the extension '.csv'. This function is especially useful when we start working with the software and we intend to enter all our items in the database. For instance, if we want to update price of items, list of items, customer's list then by importing functionality for specific items we can do this in the tool.

1. Import of New items with Basic Parameters

For training purposes, a new warehouse with the TEST symbol with the name 'Test warehouse' has been created in LoMag. The number of documents and items in it will therefore be zero. Double-click the warehouse name with the left mouse button, or select it with the same button to open the selected warehouse.

The next step is to open the item list. To do this, use the [CTRL + T] keyboard shortcut or the items tab and select the item list from the drop-down list, which will display the desired window - where you can see that the list of items in the 'TEST' warehouse is empty.

Pressing the 'Import' button will display the Import wizard window (the same window can be displayed by clicking directly on the items tab and by selecting the Import items option from the expanded list). In the following instructions, we will present imports of new items to newly added warehouse. To do this, select the default option 'Import products to the current warehouse' and then press the next button.

If you are importing items that are not in stock, ignore the step two and continue to the next step by pressing the next button. The functions of this importer step will be described in other chapters of this manual.

In the third step of the items importer, select the file from the disk using the 'select file' button. This will open a window where you can select the file in .xls, .xlsx or .csv format - and then load it by double-clicking on it with the left mouse button, or by selecting it and press the open icon.

After determining the path of file from hard drive that needs to be imported, press the next button.

In case the user receives the following message presented in the screenshot, it means that some other software is using this file at the same time. The most common mistake is the openening of Excel - in this case, close Excel with the import document open, use the back button to return to the previous step, and then without changing the file path, press next.

After importing the file with new items correctly, the final step of importing the Excel file will be opened in which the columns should be assigned to the appropriate item parameters.

If the column names in the imported file are identical to those in LoMag, then system will automatically recognize them and assign them to the importer.

In case when the name of the column in the import file differs from the existing names in LoMag, then by default system will not import those columns and they will be ignored automatically. This can be assigned manually by clicking on the 'Do not import' text - which will expand the list with all possible parameter names as we can see from below screenshot.

Pressing the import button will initiate the procedure of transferring items to the database. After the import process is completed, the program will inform by displaying below dialog box.

You can see that the items have been imported correctly into the given warehouse by opening items's list.

2. Importing Items in all Warehouses

The warehouse program also enables the importing of items in all existing warehouses in the SQL database. To do this, prepare a file in the .xsl, .xlsx (Excel) or .csv extension as shown below. The names of items have been introduced specifically so that the imported values ​​can be determined in other warehouses correctly.

When choosing the items module, select 'Import from Excel' option from the drop-down menu. This will display a new window in which you should choose the import items to all warehouses. The last step is pressing the next button.

In step two and three, proceed in the same way as in the first point of this manual - because the purpose of this manual is to import new items to all warehouses - updating items in the database will be described in another part of this manual. After verifying the data from the entered file for import, use the import button to start the process of adding items to all warehouses. After correct information has been carried out, LoMag will inform us by displaying message the quantity of new items have been added to the database (this message will be repeated depending on the quantity of warehouses in the LoMag program).

When choosing a group of items in the 'User Warehouse' warehouse, we can see three imported items correctly.

Opening the effect of any other warehouse, existing in the LoMag warehouse program will be the same - below is a screenshot of 'Public Warehouse' - in which you can notice items are added accurately.

3. Importing Items to Various and New Warehouses

The LoMag warehouse program makes it possible to import an item to a specific warehouse or to one that does not exist in the database. For this purpose, an additional column should be used, responsible for new warehouse that we want to import the items into it. The screenshot below shows the current list of warehouses in LoMag software, and the file that will be imported.

The name of the warehouse in the imported file must be identical to the one in the list of warehouses we have in the LoMag database.

To import items to various warehouses, use the items tab in a way that we already know, and then select 'Imports from Excel' option from the drop-down list. In the new window, select the option to import items to various warehouses, and then press the next button.

In the last step of the import wizard, make sure that the column from the imported file (to which warehouse we wanted to add the item) has been assigned to the appropriate value - i.e. 'Warehouse name'.In case, the column has not been recognized by the LoMag system, expand the list and select 'Name' of the warehouse from the drop-down list item, as shown in the screenshot below.

By pressing the import button, LoMag warehouse program first checks the correctness of warehouse names. In the screenshot below you can see that the user wants to add a new warehouse called Office, or has made a mistake in the name of the warehouse - which he should correct. By clicking YES option, we can add a new warehouse to our SQL database.

If the import of items has been carried out without any errors - a message will be displayed that will contain information about the changes made by the user.

When opening the list of warehouses, it should be stated that a new warehouse called 'Office' with one item (Office Chair) has been added correctly, but without any documents. The opening balance was not created in this case because the quantity of items in stock has not been imported.

Checking the file in other warehouses to which the items were imported - we can see that the data transfer has been completed successfully. Below are screenshots of different warehouses: Large, Public, User and Office.

Item list for Public warehouse

Item list for User warehouse

4. Making changes after Importing Data

It may turn out that the LoMag software user has imported some new items incorrectly. In this case, select the opening balance document and correct the erroneous data for the item (s). We will import items into the empty warehouse that are presented in the screenshot below. The wrong group for the Dell laptop, office chair and Samsung headphone and the price for the Apple Laptop were intentionally written wrong - instead of $ 1000.50, they entered $900.

The file has been correctly imported and the program informed us in a new window about the effects of the operation. If we do not have a group for which we are importing in a given warehouse - it will be automatically added to the warehouse. In the example below, this is a group called fruits - which did not exist in the current warehouse prior to import.

To open the list of OB documents, select the Documents tab, and then hold the mouse cursor on inventory option, this will display additional options in which you can select the Opening balance (OB).

In the list of OB documents, double-click with the left mouse button on the desired opening balance, or use the 'Edit' button to open the editor. You will find the incorrectly entered values ​​on it and double-click with the left mouse button on the desired product or click the 'Edit' icon to open the parameters window of the item. To change the group of office table, find its parameter in a new window, change it to the correct one and confirm the changes by pressing the OK button. This will update the group of the item on the opening balance.

5. Imports of Items with Warehouse Locations.

The LoMag inventory program allows you to import items with inventory. In order to import properly with this function, make sure that the option for handling warehouse locations is selected in the settings.

A detailed instruction on how to use warehouse locations can be found at this address

To check existing warehouse locations, select the items tab and then open warehouse locations from the drop-down list - this will open a new window with a list of location codes. In the screenshot below we can see that only one location with code # 3/01/4 is available in a given warehouse. In the prepared import file named 'Import items.xls', the above-mentioned location for the item which is the Amica Washing Machine was given. For the other two items, which are the Kingston 32GB flash drive and the GoPro Hero 5 black sports camera, a storage location that currently does not exist in the LoMag program warehouse was given. With proper import, a new warehouse location should be added to the warehouse.

In order to import items with stock levels, it is necessary to use the 'Import items with stock level' options, which can be found in the drop-down menu from the items tab. In the newly opened window, use 'import item list' button, which will open the next window where you can select the file to be loaded from the user's hard disk. After selecting the appropriate file, press the OK button to load the file.

By choosing the'import items with stocks' option, the user does not have to go through a few importer points as in the previous examples of this manual. He can do direct and immediate configuration of the columns which he had in the file with columns of the LoMag program. If all columns are properly assigned then press the import button to start the procedure of adding items to the warehouse.

To verify the correctness of imported data, click on the items tab. By choosing the item list option - you will notice that new items have been added correctly to the warehouse - in the locations specified in the .xls file. When choosing the warehouse locations option - it should be stated that the Amica washing machine was placed in a previously existing warehouse location (# 7/04/2), and the GoPro Hero 5 Black product and Kingston 32GB pendrive in the newly created location by the importer.

6. Imports of Items with Stocks, Photos and Dedicated Columns.

The sixth point of this manual will show you how to import items with stocks, initial prices, photos and how to use columns dedicated to the items (for which detailed instructions are described at this address). The value for the dedicated column named text field will be imported, which is currently added to the tables of the item.

Below is the import file that will be used. It should be stated that we expect the Samsung TV product to be imported, with a defined barcode, group and unit. In addition, its initial price will be set at USD 50 and the initial state having a amount of two hundred pieces. In order to import and assign a photo to an item, provide its path. We can find it by right clicking on the image file that we want to add and then selecting the properties option. A new window will display the location where the file is located - you can copy it and then add the file name that is in the folder with the correct extension.

When importing items with stock levels, go to the items tab and then select the 'Import items with stock levels' option from the drop-down list. A new window will be displayed in which we can select the name of file to load it from hard drive (this option does not import items to other warehouses or update items).

After loading the file to the importer, make sure that the appropriate columns contain the appropriate attributes in the LoMag software - if everything is correct, press the import items button. After successful operation, the program will inform us in a new window about the result.

In order to check the uploaded items, the user should go to the item list by using the items tab. A new product should appear on the list called 'Samsung Remote TV'.

By pressing the 'Edit' button, the user can open the edit window. In which he will be able to notice correctly imported data such as name, bar code, initial state, initial price, photo and value in the dedicated column named additional description.

7. Imports of Items with Serial Numbers.

To import items with serial numbers we will use a item called the LG TV remote control. An important element of creating an import file is providing the same bar code for the every item - otherwise we will create a new item in the warehouse (s) (depending on whether the item is imported into one or many warehouses). For instruction purposes, the last item has a different bar code than the others.

Make sure that the import columns from the file that has been loaded into the importer match with the columns of the LoMag software. If everything is configured correctly, use the 'Import items' button - after the process, the window will appear with information about the results of the operation is completed.

When opening the item list by using the 'Items' tab for this note that two new items with the same name, but with different bar codes have been appeared on the list.

To edit the LG TV Remote Control item with bar code 66633 press the 'Edit' button a new window will be displayed to the LoMag software user in which he can check the correctness of imported serial numbers. To do this, use the S/N button

8. Updating Items

The LoMag warehouse program not only allows import of new items to warehouses, but also updates quantities, statuses and parameters of existing items in warehouses. Updating imported items can be done in three different ways - which will be presented in the present chapter of the manual. Updates can be made for the current warehouse, all or selected - these options are described in chapters 1-3 of this manual.

8.1 Updating items - if the items have the same name, bar code and unit

This option is used to update items if the import file meets all three conditions of the selected option. This will include name, bar code and unit must match the items already existing in the given (or in the case of import to many warehouses, in another) warehouse. Incorrect entry of even one or more of the parameters will not be updated by LoMag. The following screenshot shows how to update items using the items tab. In the second step of the importer wizard, select the first option.

After matching the columns in the wizard to the imported file, please note that for the item called LG TV Remote Control with the 600 bar code, an incorrect unit, kilograms has been entered. In this case, LoMag warehouse program should not update this item because one of three conditions has not been met. Pressing the import button in the program will inform us about accurate updation of number of items.

When re-opening the item list, the user will notice that the item called LG TV Remote Control with the bar code number 66633 has been correctly updated because its quantity compared to point 6 of this manual has changed from 8 to 500 items, and the purchase price has been changed from 0 out of USD 94.99. Item with the same name but 600 barcode has not changed.

Editing the above two items in the LoMag software user will notice that the item with a bar code 66633 additionally has a default markup of 10.00% and filled with the value 'Test' in text field. The same item with barcode 600 was not correctly updated in the same parameters, because its unit was entered incorrectly in the import file.

8.2 Update items - if the item has the same name in the import file

The importer's second option is the function in which importer recognizes the item only by name. This allows you to update the bar code (to another) or change the unit for the item. For this purpose, the file in Excel was created below, and then it was loaded into the importer in LoMag.

In the warehouse where Zelmer mixer and Amica dishwasher were imported previously in the file list. But with stocks and prices equal to zero, and the Dishwasher unit was in pieces, not kilograms. After the correct import operation, the program should inform us in a new window about two correctly updated items.

By opening the item list window, you can see that the items have changed their barcodes, stocks to 200, initial prices and that the Amica dishwasher unit has been converted to kilograms.

8.3 Updating and importing new items - when the items have the same barcode.

To update the name of an item in a warehouse, use the import option that recognizes the item by bar code. In all three update cases described, apart from the updated items, there may be items in the file that do not exist in the warehouse, they will be created according to the parameters they have in the importer file (in the example below it is a 64GB flash drive). If the user only wants to update items and not add new ones (he may put the wrong bar code or name in the file by mistake) check the box "Update items only - new items from the source file will not be imported".

The result of a successful import is the addition of one new item to the warehouse and the update of the parameters for the other two.

When launching the item list window it should be confirmed that a new product named 64Gb flash drive has been added and names and parameters for the other two items have been updated.