Technical guide: database migration from 1C/BAS and Excel to Torgsoft

Migrating a record-keeping system is a critical stage that requires a clear understanding of the architecture of the new program and proper data preparation. This article examines the technical aspects of switching from 1C/BAS systems or regular Excel spreadsheets to the Torgsoft program, focusing on migration limits, encoding issues, memory errors (Out of memory), the specifics of text item codes, and typical failures.

1. Migration limits: what can and cannot be transferred from 1C/BAS

The architecture of the Torgsoft program is fundamentally different from 1C/BAS. Torgsoft is designed for management, inventory and financial record-keeping, while 1C/BAS is focused on accounting.

What cannot be migrated:

• Transaction history and product movement. It is impossible to transfer the entire database together with the history of orders, previously created invoices, cash shifts or financial documents.

• Automatic integration. There is no direct “gateway” or ready-made processing tool for synchronization between 1C and Torgsoft.

What can be transferred (balance migration):

The only correct migration path is to export the required directories from 1C to Excel format with subsequent import into Torgsoft. You can transfer:

1. Product catalog: names, barcodes, item codes, product types (categories), manufacturers.

2. Opening balances and prices: current quantity of goods in warehouses, purchase prices (in foreign currency or hryvnia), retail and wholesale prices.

3. Customer database: customers’ full names, their barcodes (card numbers), discount percentages, phone numbers and accumulated bonuses.

2. Excel file formatting and text item codes

For successful data upload, the file must be prepared correctly. Torgsoft works best with .xls (Excel 97-2003) formats.

The problem of mixed data types (text item codes). A common problem occurs when an Excel column (for example, “Item code” or “Barcode”) contains both numeric values (for example, 440) and text values (for example, 78549-AP). MS Office drivers may recognize such a column as numeric, and then text item codes will be imported incorrectly or skipped.

 Technical solution. Simply changing the cell format to “Text” in Excel does not always work. For guaranteed conversion, it is recommended to use a VBA macro that forcibly adds an apostrophe before each value, making it purely textual. The algorithm in Excel:

1. Select the range with item codes/barcodes.

2. Run the macro: For Each cell In Selection: cell.Value = "'" & cell.Value: Next:.

3. Save the file in .xls format.

3. Encoding and system localization issues

During import, you may encounter unreadable characters (“hieroglyphs”) or question marks ? instead of letters.

• Specific characters. Torgsoft supports the Plane 0 Unicode (Basic Multilingual Plane) character set. If a specific character was exported from 1C (for example, a non-standard multiplication sign × instead of the letter x), the program will not be able to recognize it and will replace it with ?.

• Windows system localization. If hieroglyphs appear instead of Ukrainian or Russian letters during import, the problem is in the regional settings of Windows. Go to the Windows Control Panel -> Regional Standards -> Administrative and change the “Language for non-Unicode programs” setting to Ukrainian. Also make sure the beta feature “Use Unicode UTF-8 for worldwide language support” is disabled.

4. “Out of memory” error and ODBC driver failures

“Out of memory” error during import:

This error is not related to a lack of RAM on the PC or server itself. It occurs when, while preparing the Excel file (creating a named range), the user selects the entire worksheet or thousands of empty rows and columns together with the data. The program tries to load this huge empty array into RAM, which causes a crash.

 Solution: in Excel, select only the table with actual data (without empty rows below) and assign the import name specifically to this range. Large databases (over 50,000 items) should be split into parts for stable import.

Typical ODBC driver errors:

1. «The specified module ACEODBC.DLL was not found (Error 126)»: means that the drivers required to work with Excel are damaged or missing. It is resolved by reinstalling Microsoft Office (preferably the 32-bit version) or installing Access Database Engine.

2. «The file is already opened by another user»: occurs if the file is open in Excel and the program cannot open it. Closing Excel on the current workstation where the file is open helps resolve the issue.

3. «Connection is busy with results for another hstmt»: a database stream error. It is resolved by installing the current SQL Server Native Client 11.0/12.0 and restarting the PC.

5. Typical logical mistakes made by users during migration

1. Loss of leading zeros in a barcode. If your barcodes start with zero (for example, 012345) and the zero disappears after import, clear the checkbox «Adjust barcodes that start with zero» in Settings -> Parameters -> Product.

2. Incorrect category structure (parent node). When uploading new products from Excel, users forget to specify “Parent node of the product type” and “Parent node of the manufacturer” in the import settings. If this is not done, new categories or manufacturers will be created chaotically, without the correct hierarchical tree structure.

3. Duplication or incorrect merging of products. If several barcodes are specified in one cell in the import file, or if the barcodes have an incorrect format, the program may merge different products into one item. If this has already happened, the error can be corrected in the «Inventory management -> Error diagnostics -> Product errors» menu by separating or reassigning barcodes.

4. Price mismatch (VAT).  Be careful: if the import settings specify a markup formation method based on the purchase price, and the invoice itself has the “Add VAT to the product purchase price” checkbox selected, the retail price will be formed incorrectly (it will be calculated from the price excluding VAT).

 Final advice. It is better to perform migration using the “clean start” method (recommended by accountants): first conduct a full physical inventory, record the exact balances in an Excel template, and only after that upload them as opening balances in Torgsoft. This will help avoid transferring “garbage” and negative balances from the old record-keeping system.