Import data online store in 1C: Trade Management

A friend recently asked for help with the task of importing data from a small online store to 1C: Trade Management 11. Because on the main job I deal with SharePoint and the .Net stack, I liked the idea of ​​expanding my horizons and trying something new. The task was set as follows:
- carry out a one-time download of data in 1C, followed by one-way synchronization from 1C to an online store;
- store data is stored in a database on MS SQL Server;
- exporting data from SQL Server and importing into 1C is desirable to implement using standard tools with a minimum of code.

This article is based on the study of forums and documentation 1C. It describes the process of loading data into 1C. If the topic will be in demand, another time I will write how to set up synchronization from 1C to an online store. I tried to make the process as detailed as possible, so that people can use it, as well as I who have no experience with 1C. I hope that it will help save time on this typical task and avoid errors. Although the article talks about exporting data from SQL Server, it will be useful with other DBMS.

The standard delivery of 1C: Trade Management includes processing Loading data from a tabular document (for those who have not had experience with 1C, I will explain: this is a single file Downloading Data from a TabularDocument.epf, which can be found on the 1C distribution disk), allowing you to download data to various 1C references, for example, from MS Excel file. We will use this processing for import. The advantage of using standard functionality out of the box is the savings on the development of your solution. On the Internet you can find several examples of the use of this treatment. The main difference of this article will be the description of the import procedure not only for the standard requisites of the goods, but also for the additional requisites that we will create ourselves (see below). I did not find such an example, but in real scenarios it can be very popular. For example, we will need an additional requisite for goods for storing an Id of goods on the site, so that later we can synchronize the goods back from 1C to the online store. It would be possible to write Id into one of the standard details (to use it for purposes other than its intended purpose), but we will take a more correct, albeit more complicated, path.

Let's start with a description of the database schema. We are interested in tables with products and categories. Each product may fall into several categories, and each category may contain many products. In addition, each category may have several subcategories, for example, there is a category "Sports Goods" and under them categories for various sports. For simplicity, we leave only those attributes that we need for example. Each store has its own set of attributes for goods, but properties such as Name, Price, Article may be available for most. Therefore, we restrict ourselves to 3 tables:

1. Products
2. Categories
3. ProductCategories - an intermediate table in many-to-many relationships between products and categories

Here is the base schema:

')
First we need to unload the goods in the file from the database. This is done quite simply in SQL Management Studio: you can execute a query in a file with a fixed column width and then open it as a csv file in Excel. You can also just copy the results from the table with the results of Management Studio to Excel. It remains to write the desired request.

In the online store, the product may fall into several categories, but in 1C in standard configuration, the products fall into one category (type of nomenclature). Probably in 1C you can, if you wish, adjust the many-to-many relationship between the nomenclature and the types of nomenclature, but we will limit ourselves to the standard configuration. Therefore, when exporting for each product, we will take the first category, which does not have a parent category (category 1 level).

To write a query, we need to work with hierarchical data in the table of categories. For such queries in SQL Server there is a special mechanism for generalized table expressions (Common Table Expressions), but we will go in a simpler and more understandable way using User Defined Functions. First, we define a recursive function for selecting a category of the 1st level according to the given categoryId:

create function dbo.get_parent_category(@categoryId int) returns nvarchar(256) begin declare @parentId int set @parentId = (select ParentCategoryId from dbo.Category where CategoryId = @categoryId) if @parentId is null begin return @categoryId end return dbo.get_parent_category(@parentId) end 

Then a function that returns the name of the first category of the 1st level for a given product:

 create function dbo.get_parent_product_category(@productId int) returns nvarchar(256) begin declare @categoryId int, @parentCategory int declare @categoryName nvarchar(256) set @categoryId = (select top 1 c.CategoryId from dbo.Product p inner join dbo.ProductCategory pc on p.ProductId = pc.ProductId inner join dbo.Category c on pc.CategoryId = c.CategoryId where p.ProductId = @productId) set @parentCategory = dbo.get_parent_category(@categoryId) return (select Name from dbo.Category where CategoryId = @parentCategory) end 

It remains to write the request itself for the exported goods. With the functions described, it is simple:

 select Name, Price, Articul, Vendor, dbo.get_parent_product_category(ProductId), ProductId from Product order by p.Name 

Perhaps SQL experts will be able to provide simpler solutions without using custom functions, but for this example, the above solution will suffice.

So, we exported the data to Excel. The next step is import into 1C. Products in 1C: Trade Management (UT) are stored in the Nomenclature directory. To begin with, this reference book needs to add additional requisites for storing the Id of the goods in the database of the online store (as already mentioned, this is necessary for the subsequent synchronization of goods from 1C to the online store). You can do this as follows: open any product in edit mode and select the Additional Details button (inconspicuous icon on top of the form):


In the window that opens, select Add> General Properties> Selection> Create and enter the name of the new requisite, for example, “Product Id on the site”:


After we added a new attribute to the set, we need to rediscover the product edit form. The form for editing the attribute should appear on the form:


To start the processing of Loading Data from a Public Document, you need to open 1C in the Normal application mode (by default, 1C UT 11 opens in the Managed application mode). To switch to the regular application mode in the configurator, select Administration> Users> select a user, and in the edit form on the Other tab, in the Start mode field, install the Normal application. Also, in the settings for launching the information database, the field Basic mode should be set to Select automatically.

Now we open the information database in the Normal application mode and load the processing of Loading Data from the Table Document (File> Open). In the window that opens, choose to open the Excel file, which we exported from the store database:


Before importing goods, you need to import data into dependent directories, Manufacturers and Types of nomenclature, otherwise these fields will remain empty. To import manufacturers at the top, select Download Mode = Download to Directory, Directory Type = Manufacturers. On the Settings tab, set the following values:


Here it is important to specify the column number and select the Search field (otherwise duplicates will be created). After clicking on Download manufacturers will be imported into the directory.

By analogy, we import data into the Types of the nomenclature directory. But unlike the manufacturers, in the standard configuration, on the Settings tab, you also need to set the Sales clearance option and the Item type:


Now you can start importing goods. Set the settings as shown in the following image:


To load data into the additional requisite “Id of goods on the site”, which we created above, we add the following handler to Events> When writing object:

 Object.AdditionalRequisition.Clean ();

 PropertyId =
 Plans of Types of Characteristics. Additional Details of Details. FindName ("Product Id on the Site", Truth);
         
 Site ID = Object. Additional Details. Add ();
 identifier at site. property = property id;
 identifier on the site. Value = CellTells [6];
 Object.Write ();

After that we load the goods. If everything is done correctly, the import will be successful and when you open the editing form with one of the products you will see its properties, which should be the same as in excel.

There is only one step left - import prices. This step is highly dependent on the specific configuration and price type. Of the general steps are the following. On the processing form, select Loading Mode = Loading to the tabular part, Link = Setting the prices of the item. Then again click Link again and add a new document:

After creation, select it (the document should appear in the form reference field), in the Tabular part field, select Products and set the following settings on the Settings tab:

Here it is important that we indicate from which column prices will be taken at import. After that you can download prices. As I already wrote, the process of downloading prices is highly dependent on the specific configuration, so here I will not go into details. If you are interested in the details, I advise the following article: Loading data into the nomenclature reference book from the Excel price list . I will also note that it may still be necessary to agree on the created document for setting the prices of the nomenclature.

Thus, we downloaded data from the online store database in 1C for later use in the account. I hope that this article will help someone save time and money to perform a similar task. I do not work with 1C, so if experts find inaccuracies in this article, please treat them condescendingly and consider the article only as a guide for solving a specific applied task.