Importing from Excel: loading the item list

Loading the list is the most important preparation for a count. The counting software only knows what is in your table: an item missing from it is invisible, and a wrong code will not be recognised. Much of the time is lost not in counting itself but through a sloppy table. Below: which columns you need, how to clean the table and what to check after loading.

Which columns you need

The minimum set is four columns:

• the article number or another unique internal ID;
• the product name, so the worker can confirm by eye that the right item opened;
• the barcode, the code the scanner reads;
• the booked balance: the quantity in 1C or another system.

Extra columns help, provided they are used later: unit of measure (pieces, cartons, kg), zone or bin, purchase price (to value differences in so'm), batch and expiry date. The more columns, the more room for error. Decide beforehand whether the purchase price should appear on the counters' screens.

Preparing and cleaning the list

The list is usually taken from 1C or another accounting system as a stock balance report. Run it for one specific warehouse and one specific date, then save it to Excel. Beware: reports often show product groups and subtotals as separate rows. They are not products, yet they load as products. Run the report ungrouped, as a plain list.

Before loading, copy the file and work on the copy, leaving the original untouched. Then:

• the first row holds the column names, each once and spelled exactly: "Article", "Name", "Barcode", "Balance";
• remove merged cells, empty rows and the "Total" row under the table, or "Total" will load as a product;
• get rid of hidden columns, active filters and extra sheets: the data must sit on one open sheet;
• trim stray spaces at the start and end of names, and make sure no item is written two ways;
• look for duplicate codes; Excel's "Duplicate values" highlighting is enough. If one code belongs to two items, the scanner opens the wrong position.

The code column: zeros and long numbers

Two problems with codes come up most. First, Excel takes a code for a number and drops the leading zeros (123 instead of 00123). Second, a 13-digit code turns into 4.6E+12 and loses its last digits. The fix is simple: format the column as "Text" before entering data. If the damage is already done, the digits usually cannot be recovered and the codes must be exported again from the source system. Opening a CSV file in Excel, changing it and saving it back is just as risky.

Two smaller traps. If quantities are fractional (kilograms, metres), do not mix commas and points: 2,5 and 2.5 are read differently by different programs. With CSV, use UTF-8 encoding, otherwise Uzbek letters like o' and g' and Cyrillic turn into unreadable symbols.

Checking after the load

A finished load does not mean everything is right. Most programs show how many rows were accepted and which were rejected. The checks:

1. Accepted plus rejected rows must equal the number of rows in the file.
2. Note the total of all balances before loading and compare it with the total in the software.
3. Pick 10 to 15 items at random and compare name, code and balance with the source table; scan at least one for real.
4. Read the reasons for rejected rows: usually an empty code or a duplicate article.

Common mistakes

• The list was exported with balances a week old while goods kept moving. Refresh the balances the day before the count.
• The table contains only booked items, with no plan for adding an item found in the warehouse but missing from the books.
• Units are mixed: "cartons" in one row, "pieces" in another, and the difference is clear to nobody.
• The whole chain's list sits in one file, so each counter searches thousands of irrelevant rows. Split the list by zone or warehouse.

Conclusion

A well-prepared table saves most of the time on counting day: the code is found, the name matches, the difference comes out right. If you lack the time to prepare the list and run the count, order it from StockCheck: the team counts against your table and hands over the result as Excel, PDF or a file for 1C.