Loading the nomenclature in 1s. Accounting info

Do you like the solution?

84 liked this

What opportunities does loading into 1C from Excel provide?

Thanks to the capabilities of modern software you have access to the service of downloading any kind of data from Excel to 1C.

Why do you need it?

To quickly transfer the accumulated data to the 1C accounting system being launched.
To rid yourself of your regular routine of manual update price lists.
To quickly download orders from the online store.
To protect yourself from information processing inside 1C, for unwanted users. For example, outsourcing companies or freelancers.
To quickly update the composition of nomenclature items.

Watch our video to understand how you can use data loading in 1C in your business (when viewing, indicate high quality):

How to properly prepare data for loading into 1C from Excel?

If you have data in MS Excel format and you decide to transfer it to 1C, then you need to know the following:

1. The data structure must be uniform. For example, if you also have phone numbers in the last name column in Excel, then you should move them to a separate field (column). The One Field, One Entity principle applies to any data that you are about to download.

2. Uniform data format within each field. Programs for loading data into 1C from Excel are sensitive to the smallest details. The following data from the point of view of the machine will be different:

"Piece" and "piece" (without a dot and with a dot)
"Man" and "husband." ( miscellaneous description one essence - gender)

Before automated loading of data, you need to make sure that all identical (from a human point of view) entities have the same description - format, otherwise these entities will be loaded into 1C in several versions.

3. Unique identifiers for unique entities. To avoid duplication of data during automated loading of data, it is necessary to provide unique identifiers in advance. For example, when loading data on goods, such an identifier can be the article of the goods, when loading counterparties - legal entities the identifier can be TIN, for individuals - email or discount card number.

Depending on the requirements of the problem, there are different approaches to the interpretation of unique identifiers, but the fact remains - they must be!

4. The absence of extraneous data and design elements is desirable, not related to the download. This data includes service comments, blank lines, footnotes, etc. elements.

5. Unambiguous structure of cells. Combining cells in a region is permissible provided that such a union was performed according to a clear algorithm that lends itself to machine analysis. We do not recommend using such joins for automated data loading.

If you have a file, the data from which you want to upload to 1C, just send it to us for analysis and we will tell you the timing and cost of automatic uploading!

What data can, in principle, be loaded from Excel to 1C?

Any data is suitable for automated loading from Excel to 1C, as long as they are formatted correctly, taking into account the above principles.

Examples include the following:

(Name, contact details)

Download completed sales information(to whom they sold, what they sold, how much money did they receive)

(product, how much it costs, for which categories of buyers it costs so much)(who wants to buy, what he wants to buy, in what quantity)

Download information about accumulated discounts(who saved up, for what amount of purchases received a discount, what is the percentage of the discount)

Please note that these are just examples. Technically, we are able to download any data that is required to solve your specific task.

If you have a need to analyze this data and then upload it to 1C - please contact us! We will advise you free of charge by phone: 8-926-219-21-60.

How does the download from Excel to 1C take place? Is it possible to write a program for self-regular download?

The site team has been making loaders since 2005. We have accumulated vast experience in loading from Excel to 1C, which will allow us to write a bootloader for any purpose:

One-time bootloader to quickly solve a specific problem.
Multifunctional bootloader for multiple use.

It all depends on your goals and objectives.

If the task is urgent, and there is no need for regular import of information into 1C, then you will only need to order a one-time loader. It will cost less than a multifunctional one and will allow you to solve the problem in a short time. As a result of the work of a one-time bootloader, you will receive necessary information in your accounting system soon after ordering.

It will take more time to develop a multifunctional loader. It all depends on the degree of its versatility and the complexity of information processing.

This is how our loaders look like:

Or like this:

This is how our bootloader for the "Trade Management" configuration ed. eleven:

And we have MORE than hundreds of such loaders for any task!

In any case, whichever bootloader you need, call 8-926-219-21-60. We will advise you free of charge and help you make the right choice.

Interested in the ready-made solution "Loading in 1C from Excel"?

We will be happy to answer your questions and discuss the details of cooperation.

Click the button"Send a request" and we will contact you within one working day.

This method is simple. Its essence lies in the fact that the object TabularDocument has methods:

Write down (< ИмяФайла>, < ТипФайлаТаблицы >) to upload data to a file;
Read (< ИмяФайла>, < СпособЧтенияЗначений >) to load data from a file.

Attention!

The Write () method is available on both the client and the server. The Read () method is only available on the server side. Remember this
when planning client-server interaction.

Let's look at an example of saving spreadsheet document to the file. It is necessary to create and fill in the TabularDocument object in any way, and unloading to the file is carried out with just one line:

TabDoc ... Write (FilePath, TabularDocumentFileType. XLSX);

Here TabDoc- generated spreadsheet document, The path to the file- the name of the file to upload, Tabular Document File Type.XLSX- format generated file... The following Excel formats are supported:

XLS95 - Excel format 95;
XLS97 - Excel 97 format;
XLSX - Excel 2007 format.

TabDoc = New TabularDocument;
TabDoc ... Read (Path To File, How To Read Tabular Document Values.);

Here The path to the file- the path to the downloaded Excel file. Method of Reading Tabular Document Values. determines how to interpret the data read from the source document. The options are:

Meaning;
Text.

Exchange over OLE

Exchange via OLE automation technology is perhaps the most common option programmatic work with Excel files. It allows you to use all the functionality provided by Excel, but is slower than other methods. For exchange via OLE, you need to install MS Excel:

On the end user's computer, if the exchange takes place on the client side;
On the 1C: Enterprise server computer, if the exchange takes place on the server side.

Example unloading:

// Create a COM object
Excel = New COMObject ("Excel.Application");
// Disable display of warnings and questions
Excel ... DisplayAlerts = False;
// Create a new book
Book = Excel. WorkBooks. Add ();
// Positioning on the first sheet
Sheet = Book. Worksheets (1);

// Write the value to the cell
Sheet ... Cells (LineNumber, ColumnNumber). Value = Cell Value;

// Save the file
Book ... SaveAs (FileName);

Excel ... Quit ();
Excel = 0;

Examples of reading:

// -- OPTION 1 --

// Create a COM object
Excel = New COMObject ("Excel.Application");
// Open the book
Book = Excel. Workbooks. Open ( The path to the file );

Sheet = Book. Worksheets (1);

// Close the book
Book ... Close (0);

// Close Excel and free memory
Excel ... Quit ();
Excel = 0;

// —— OPTION 2 ——

// Open the book
Book = GetCOMObject ( The path to the file );
// Positioning on the desired sheet
Sheet = Book. Worksheets (1);

// Read the cell value, usually this is the cell traversal loop
Cell Value = Sheet. Cells (LineNumber, ColumnNumber). Value;

// Close the book
Book ... Application. Qui t ();

For bypass of all filled rows of an Excel sheet, you can use the following techniques:

// -- OPTION 1 --
Number of Lines = Sheet. Cells (1, 1). SpecialCells (11). Row;
For Line Number = 1 By Number Of Lines Loop
Cell Value = Sheet. Cells (LineNumber, ColumnNumber). Value;
End of Cycle;

// —— OPTION 2 ——
LineNumber = 0;
While Truth Cycle
LineNumber = LineNumber + 1;
Cell Value = Sheet. Cells (LineNumber, ColumnNumber). Value;
If NOT ValueFilled (Cell Value) Then
Abort;
EndIf;
End of Cycle;

Instead of sequentially traversing all the rows of the sheet, you can dump all data into an array and work with him. This approach will be faster when reading large amounts of data:

Total Columns = Sheet. Cells (1, 1). SpecialCells (11). Column;
Total Rows = Sheet. Cells (1, 1). SpecialCells (11). Row;

Region = Sheet. Range (Sheet. Cells (1, 1), Sheet. Cells (TotalLines, TotalColumns));
Data = Region. Value. Unload();

The table below shows the most popular properties and methods for working with Excel through OLE:

Action	Code	A comment
Working with the application
Setting the visibility of the application window	Excel ... Visible= False;
Setting the mode of displaying warnings (display / do not display)	Excel ... DisplayAlerts= False;
Closing the application	Excel ... Quit ();
Working with a book
Creating a new book	Book = Excel. WorkBooks. Add();
Opening an existing book	Book = Excel. WorkBooks. Open (FileName);
Saving a book	Book ... SaveAs (FileName);
Closing the book	Book ... Close (0);
Working with a sheet
Setting the current sheet	Sheet = Book. WorkSheets (Sheet Number);
Setting the name	Sheet ... Name = Name;
Setting protection	Sheet ... Protect();
Removing protection	Sheet ... UnProtect();
Setting page orientation	Sheet ... PageSetup. Orientation = 2;	1 - portrait, 2 - landscape
Setting the left border	Sheet ... PageSetup. LeftMargin = Excel. CentimetersToPoints (Centimeters);
Setting the upper bound	Sheet ... PageSetup. TopMargin = Excel. CentimetersToPoints (Centimeters);
Setting the right border	Sheet ... PageSetup. RightMargin = Excel. CentimetersToPoints (Centimeters);
Setting the lower bound	Sheet ... PageSetup. BottomMargin = Excel. CentimetersToPoints (Centimeters);
Working with rows, columns, cells
Setting the column width	Sheet ... Columns. ColumnWidth = Width;
Deleting a line	Sheet ... Rows. Delete();
Column deletion	Sheet ... Columns. Delete ();
Deleting a cell	Sheet ... Cells (LineNumber, ColumnNumber). Delete();
Setting the value	Sheet ... Cells (LineNumber, ColumnNumber). Value = Value;
Merging cells	Sheet ... Range (Sheet. Cells (RowNumber, ColumnNumber), Sheet.Cells (RowNumber1 ,ColumnNumber1)). Merge();
Installing the font	Sheet ... Cells (LineNumber, ColumnNumber). Font. Name = Font Name;
Setting the font size	Sheet ... Cells (LineNumber, ColumnNumber). Font. Size = Font Size;
Setting the bold font	Sheet ... Cells (LineNumber, ColumnNumber). Font. Bold = 1 ;	1 - bold, 0 - normal
Setting italic	Sheet ... Cells (LineNumber, ColumnNumber). Font. Italic = 1 ;	1 - italic, 0 - normal
Setting the underlined font	Sheet ... Cells (LineNumber, ColumnNumber). Font. Underline = 2 ;	2 - underlined, 1 - not

In order to find out which property needs to be changed or which method to call, you can use macros Excel. If you record a macro with the required actions, then you can then look at the VBA program code of the recorded macro.

Using COMSafeArray

When unloading large amounts of data from 1C to Excel, you can use the object to speed up COMSafeArray... According to the definition from the syntax helper, COMSafeArray is an object wrapper over a multidimensional array SafeArray from COM. Allows you to create and use SafeArray to exchange data between COM objects. In simple terms, it is an array of values that can be used to exchange between applications using OLE technology.

// Create COMSafeArray
ArrayCom = New COMSafeArray ("VT_Variant", TotalColumns, TotalStrings);
// Populate the COMSafeArray
For Lines = 0 By Total Lines - 1 Cycle
For Number = 0 Total Columns - 1 Cycle
ArrayCom ... SetValue (Count, Page, Value);
End of Cycle;
End of Cycle;
// Assign sheet area Excel values from COMSafeArray
Sheet ... Range (Sheet. Cells (1, 1), Sheet. Cells (Total Rows, Total Columns)). Value = ArrayCom;

Exchange via ADO

An Excel file exchanged through ADO is a database that can be accessed using SQL queries. Installation of MS Excel is not required, but an ODBC driver must be present, with which the access will be carried out. The ODBC driver to use is determined by specifying a connection string to the file. Usually the required driver is already installed on the computer.

Exchange via ADO is noticeably faster than exchange via OLE, but when unloading, there is no way to use Excel functionality to design cells, markup pages, set formulas, etc.

Example unloading:

Connection = New COMObject ("ADODB.Connection");

Compound ... ConnectionString = "

| Data Source = " + FileName + “;
;
Compound ... Open (); // Open connection

// Create a COM object for the command
Command = New COMObject ("ADODB.Command");
Command

// Assign the command text to create the table
Command ... CommandText = "CREATE TABLE [Sheet1] (Column1 char (255), Column2 date, Column3 int, Column4 float)";
Command ... Execute (); // Execute the command

// Assign a command text to add a table row
Command ... CommandText = "INSERT INTO [Sheet1] (Column1, Column2, Column3, Column4) values (‘ ‘‘ ‘’ ’,‘ 8/11/2017 ’”, ‘12345’, ‘12345.6789’) ”;
Command.Execute (); // Execute the command

// Delete the command and close the connection
Command = Undefined;
Compound ... Close();
Connection = Undefined;

To create a new sheet and form its structure, you can use the objects ADOX.Catalog and ADOX.Table... In this case, the code will take the form:

// Create a COM object to work with the book
Book = New COMObject ("ADOX.Catalog");
Book ... ActiveConnection = Connection;

// Create a COM object to work with the data structure on the sheet
Table = New COMObject ("ADOX.Table");
table ... Name = "Sheet1";
table ... Columns. Append ("Column1", 202);
table ... Columns. Append ("Column2", 7);
table ... Columns. Append ("Column3", 5);
table ... Columns. Append ("Column4", 5);

// Create a sheet with the described structure in the book
Book ... Tables. Append (Table);
Table = Undefined;
Book = Undefined;

In the above example, in the method

table ... Columns. Append("Column1", 202);

the second parameter specifies the type of the column. The parameter is optional, here are some values for the column type:

5 - adDouble;
6 - adCurrency;
7 - adDate;
11 - adBoolean;
202 - adVarWChar;
203 - adLongVarWChar.

Example reading:

// Create a COM object for the connection
Connection = New COMObject ("ADODB.Connection");

// Set the connection string
Compound ... ConnectionString = "
| Provider = Microsoft.ACE.OLEDB.12.0;
| Data Source = " + FileName + “;
| Extended Properties = "" Excel 12.0 XML; HDR = YES ""; ";
Compound ... Open (); // Open connection

// Create a COM object to get the selection
Selection = New COMObject ("ADODB.Recordset");
Request Text = "SELECT * FROM [Sheet1 $]";

// Executing the request
Sample ... Open (QueryText, Connection);

// Bypass the selection result
NOT Sampling yet. EOF () Loop
Column1 value = Sample. Fields. Item ("Column1"). Value ; // Referencing by column name
Column2-Value = Sample. Fields. Item (0). Value; // Referring to the column index
Sample ... MoveNext ();
End of Cycle;

Sample ... Close ();
Sample = Undefined;
Compound ... Close ();
Connection = Undefined;

In the connection string, the parameter HDR determines how the first line on the sheet will be perceived. The options are:

YES - the first line is interpreted as the column names. Values can be accessed by name and column index.
NO - the first line is treated as data. Values can only be accessed by column index.

Only a few of the ADO objects have been discussed in the examples provided. Object Model ADO consists of the following entities:

Connection;
Command;
Recordset;
Record;
Fields;
Stream;
Errors;
Parameters;
Properties.

Upload without programming

To save data from 1C to Excel, it is not always advisable to resort to programming. If in Enterprise mode the user can display the data required for uploading, then it is possible to save them in Excel without programming.

To save a spreadsheet document (for example, a report result), you can call the command Save or Save as… main menu.

In the window that opens, you need to select the directory, name and format of the saved file.

To save data dynamic lists(for example, a list of items) you must:

Output data to a spreadsheet document using the command More ⇒ Display list ...;
Save the spreadsheet document in the required format.

Configuration: 1c accounting

Configuration version: 3.0.44.177

Date of publication: 20.01.2017

In the 1C: Accounting program, it became possible to routinely download and upload data from Excel spreadsheet documents, without the use of third-party processing and configuration changes. Loading and unloading is so easy that experienced users they will quickly fall in love with this convenient mechanism, and new users will quickly master it.

Download from Excel 1C: Accounting 8.3 ed. 3.0

For example, we have such an Excel spreadsheet document for loading a list of items and prices (retail and purchasing).

Come in.

At the top, click Nomenclature and Prices - Download... This is where loading and unloading from spreadsheet documents is hidden.

The download supports xls (old Excel 97-2003 format) and xlsx (new format), as well as mxl and ods formats. Now we select our file with the nomenclature and wait for the download.

The spreadsheet document has been loaded in the form as we saw it in Excel, now we will assign the columns and delete the excess.

Click on Specify the requisite and select the desired item from the list.

Select the line that contains unnecessary records, namely the table header and click Delete line.

Now remove the purchase price column, note that processing is able to fill in only 1n type of price for 1n time !!! Then you can do this operation again and download the 2nd type of price, for the 1st download, Retail for the 2nd purchase.

Fill in the blank data, Price type, Set prices for. The buttons All, Found, New, Duplicates will greatly facilitate the search and help to match the item. Fill in and click Download after which the data will be loaded into 1C: Accounting.

New items appeared in the item list, filled and with assigned prices. If you need to load another type of price, do all the points again, but select the column with a different price, 1C itself will find already created positions and will not try to duplicate them, but will simply add the new kind prices.

As you can see, the download created 2 item pricing documents, for the Purchase price and for the Retail price. The difference between this processing is Downloading from Excel to 1C: Accounting 8.3, the fact that this is a regular processing and it works without failures and taking into account all the features of filling out the document.

Export to Excel from 1C: Accounting 8.3 ed. 3.0

Highly useful function in the version 3.0.44.177 and higher is the export to Excel from 1C: Accounting 8.3, this processing will help you create a price list of the company or transfer your list of items to suppliers or buyers immediately with prices.

All in the same directory Nomenclature - Nomenclature and Prices - Upload.

In point Loudspeakers, select those columns that need to be built in Excel document. Selection serves for the selection of items by Groups, properties, names ... and so on.

For uploading to excel, we will add a new column, namely the VAT rate, it seems not really necessary, but we are practicing.

How to load a spreadsheet document into the 1C 8.3 Accounting program?

In 1C 8.3, it is possible to bulk load a list of items from a spreadsheet document, for example, from an Excel file.

To download we use external processing LoadingDataFrom TabularDocument.epf for managed forms(8.2 and 8.3). It is universal and should be suitable for any configuration written for a managed application.

To start external processing, you need to go to the "File" menu, then "Open" and select this processing from the directory where it was saved:

Processing Loading data from spreadsheet document 1C 8.3 (managed forms)

After the processing has opened in 1C, you can start working with it. First of all, we need to decide where and what we will load:

Let's give an example using the Nomenclature reference book. We create a certain file with data in xls format.

Processing can also load files of the following format:

any other format from where the data can be copied

This is how it looks Excel file with the initial data, which we will load into the "Nomenclature" reference book:

We did not write all the fields of the directory, as this would be enough to understand the principle of loading. In addition, this data is enough to start working with these positions.

These positions are not currently in information base 1C, and we will now upload them there.

Click on the "Open" icon (denoted as "Source selection" in the figure) and select the data file. Simple copying of information can be used. The data will be loaded into processing automatically. Now let's check if we have filled in our data correctly. To do this, press the button "Fill control".

As you can see, we have errors! We will eliminate. Go to the "Settings" tab:

Before correcting errors, let's pay attention to one important detail. Initially, the program does not know which field it should use to search for a directory element in case it is already there. Therefore, she needs to indicate it. For example, we search by code, as it is usually unique. In the column "Search field" in the line "Code" put a check mark. Now, if a record with such a code is found, it will be replaced, otherwise it will be created.

Important! If you do not specify the search field, then duplicate items may appear. Especially after the second download of a similar file!

Now let's see why he swears on the "Unit" field. The fact is that the units of measurement in 1C 8.3 are stored in a separate directory and the default processing looks for this unit by name. But in fact, the word unit is spelled out in the "Full name" field.

Unfortunately, processing can only search by "Name" and "Code" (for a programmer, the possibilities are wider). If you pay attention to the pictures above, you will see that there is a code in the “Unit” column. And in processing, you need to indicate that the search must be carried out by code. We double-click on the “Communication condition” column opposite the “Units” and change to “Code”.

Now we look at what they say to us about the "Service" in the list of errors. Another important note. The columns in the file must be in the exact same order as the lines of the dictionary fields. And with us, the "Nomenclature type" is at the very bottom, and in the file after the "Comment" column.

In order to raise the line "Item view" up, there are blue arrows at the top of the form. Use the Up arrow to raise the required line and place it under the Comment.

We press "Load data" and this time everything goes without errors:

Based on materials: programmist1s.ru

Price list in Excel "PriceOnlineChemSnab.xls" - Microsoft Office Excel 97-2003 (.xls)
Typical configuration Trade Management Rev.10.3, release 10.3.10.4, platform 8.2 (8.2.10.77)
It is necessary to load into 1C from Excel not only the names of goods and prices as in Example 1, but also articles and barcodes, while it is necessary that the nomenclature is not loaded into one catalog (group), but distributed among manufacturers in the 1C nomenclature reference.

In order to download the nomenclature reference book from the Price List into 1C: Trade Management 8 with automatic distribution into groups (manufacturers), it will be necessary to prepare a file form for uploading. You will need to add a column indicating the unit of measurement (pcs.) And the manufacturer of the goods, while the name of the manufacturer in the price list must correspond to the name of the group in the reference book of the nomenclature of the 1C program: Trade Department 8.

We use typical processing"LoadingDataFrom TabularDocument.epf" which goes to the ITS.

1. Launch the 1C program

2. To start processing, select the menu item "File">« Open» .

3. In the window that opens, we find the processing file, which is located on the ITS disk in the \ 1CIts \ EXE \ EXTREPS \ UNIREPS82 \ UploadFromTableDocument directory

If you have previously copied the processing file to your computer, you can select it.

A window will open

Please note that data loading will be carried out in three stages:

Stage 1- loading the names and setting the values of the requisites of the new elements of the directories necessary for starting work (unit of measure, VAT rate, attribute product / service). - loading barcodes

Install the "Download Mode" - Download to the directory

"Directory view" - Nomenclature

5. In the tab “Spreadsheet document”, press the button “Open file”, in the appeared selection window we find the directory in which we have a price list in Excel from which we plan to load data into 1C.

Initially, the file may not be displayed. Set the File Type "Excel Sheet (* .xls)"

Selecting our file

The details of the item card are reflected.

8. Configuring the parameters for loading titles

For convenience, we will set the "Manual column numbering"

At this stage, we load only those details that will allow us to start working with the program, so we uncheck the boxes that we are not interested in yet.

"The first line of data in the spreadsheet document" - we look at our price list, discard the header, the data for loading is read from the ninth line - we put "9" (see Fig. 2 "Price list")

"Do not create new elements" - do NOT check the box, the option is needed if we only plan to change existing elements.

Lines "Name" and "Full name" - set the Download mode "Search", "Column number of the spreadsheet document" - in our price list the name in the third column - put "3"

Line "Parent" - in the column "Download mode" select "Search", in the column "Column number of the spreadsheet document" - in our price list manufacturers in the fourth column - put "4".

If there is no need to distribute by manufacturer and all new elements need to be loaded into one folder, then in the "Download mode" column, select "Install", in the "Default value" column, select the nomenclature group into which we will load data from the price list.

The line "Article" - put a checkbox in the "Search field" column (second column) - the program will check the presence of an item with such an article in the 1C database, and if it already exists, then new item with this name will not be created. Set the Download Mode "Search", "Column # of the spreadsheet document" - in our price list, the article in the second column - set "2"

The line "Base unit of measurement" - in the column "Download mode" select "Install", in the column "Default value" select the unit of measurement (in our case - pcs.)

The line "Weight" - in our case, the product is NOT by weight, therefore, "Load Mode" select "Install", in the column "Default value" select "No"

The line "VAT rate" - in our case, for all loaded elements we set VAT 18%, for this in the column "Loading mode" select "Set", in the column "Default value" select "18%".

If the goods come with different VAT, which is indicated in a separate column of the downloaded price list, then in the column "Download mode" set "Search" and the number of the corresponding column of the price list in the "No. Column of the spreadsheet document".

Line "Service" - in the column "Download mode" select "Install", in the column "Default value" select "No" (in our case, all items are goods).

The line "Type of nomenclature" - in the column "Download mode" select "Install", in the column "Default value" select "Product".

We asked minimum values new items of the nomenclature reference book, which will allow you to get started.

If you are ready to set the values of the missing details now, then indicate them as well.

Click "Download" in the lower right corner, click "Yes" to the question

8. Check for the presence of new elements in the corresponding groups of the stock list reference book.

On currently we have loaded from the price list in Excel titles goods in the 1C nomenclature reference book.

As you can see, the requisites "Unit for reports" and "Unit of storage of balances" were left blank.

The next step is to set these details, since they will be required when loading prices, barcodes and further work.

There are two options:

Manual.

These details are created by the program automatically when you first open and save the item card. If manual installation is acceptable, then you can skip this step and go directly to the stage of downloading prices. If you do not plan to download barcodes, then it is quite acceptable even to leave it as it is - the first time you use the item, you will need to open and save the product card.

Disadvantage: The manual opening and recording procedure can be quite time consuming and take quite a long time when a large number elements of the directory.

Auto.

Possible and automatic loading units of measurement, but it is designed for advanced users and will require basic 1C programming skills.

There are two options: with identification by article or name.

Identification by Article is preferable, but the price list may not have a column with articles.

Option 1. Identification by Article

We put a "daw" in the "Do not create new elements" field and remove the "daws" from all lines.

Line "Article" - put a checkbox in the column "Search field" (second column). Set the Download Mode "Search", "Column # of the spreadsheet document" - in our price list, the article in the second column - set "2"

In the lines "Unit for reports" and "Unit of storage of residuals" in the "Loading mode" field, select "Calculate". In the "Expression" field, write the algorithm in the built-in 1C programming language. For example, our specialist from the implementation department, in a couple of minutes, suggested the following code:

NomenclatureLink = Reference books.Nomenclature.FindByData ("SKU", CurrentData ["SKU"]); BaseUnitMeasurement = Directories.UnitsMeasurement Classifier.FindByCode (796); TekSpr = Spravochniki.EdinitsyIzmereniya.NaytiPoRekvizitu ( "EdinitsaPoKlassifikatoru, BazovayaEdinitsaIzmereniya, SsylkaNomenklatury) If TekSpr.Pustaya () Then NaydennayaEdinitsaObekt Spravochniki.EdinitsyIzmereniya.SozdatElement = () = NaydennayaEdinitsaObekt.Naimenovanie SokrLP (BazovayaEdinitsaIzmereniya) NaydennayaEdinitsaObekt.EdinitsaPoKlassifikatoru = BazovayaEdinitsaIzmereniya; NaydennayaEdinitsaObekt .Feed = 1; FoundUnitObject.Owner = ReferenceNomenclature; Attempt FoundUnitObject.Write (); Result = FoundUnitObject.Ref; Exception Report ("Failed to write"); End Attempt; Otherwise Result = Current; Ref.End

If you plan to repeat this procedure multiple times, then you must additionally add a verification procedure to the algorithm in order to exclude re-entering of units of measurement with the same name.

Option 2. In case of identification by Name

We put a "tick" in the field "Do not create new elements"

We remove the "jackdaws" from all lines

Line "Name" - put a checkbox in the column "Search field" (second column). Set the Download Mode "Search", "Column No. of the spreadsheet document" - in our price list, the names in the third column - put "3"

NomenclatureLink = Directories.Nomenclature.FindByName (CurrentData ["Name"]);
BaseUnitMeasurement = Directories.UnitsMeasurement Classifier.FindByCode (796);
TekSpr = References.Units of Measurement.FindBy Attributes ("
UnitByClassifier ", BaseUnit, Nomenclature Reference);
If TekList.Empty () Then FoundUnitObject = Dictionaries.DimensionUnits.CreateElement ();
FoundUnitObject.Description = Abbreviation (BaseUnit);
FoundUnitObject.UnitByClassifier = BaseUnit;
FoundUnitObject.Feed = 1;
FoundUnitObject.Owner = Nomenclature Reference;
Attempt FoundUnitObject.Write ();
Result = FoundUnitObject.Ref;
Exception Report ("Failed to write");
End of Attempts;
Otherwise Result = TekSpr.Link;
EndIf;

// Company "Online" 2010, www.online-ufa.ru

9. We open the product card, check the correctness of the loading of the details

If everything is correct, then you can proceed to the second stage - loading prices from the price list into Excel.

If in the future you plan to use this procedure loading data with the same boot parameter settings, we recommend that you save the current settings. To do this, click the "Save settings" button and clearly name the current setting, for example, "Loading product names from the Excel price list".

In "Trade Management" Rev.10.3, prices are set using the document "Setting item prices".

We use the same processing of "LoadingDataFrom TabularDocument.epf" from the ITS disk.

11. Set the "Download Mode" - "Load into the tabular section".

The document journal "Setting item prices" opens.

Here you can select an existing document to which we want to add new elements, or create a New one (recommended), in the document we indicate which "Price Type" we will load (in our example, "Retail").

Click "OK" and save the empty document for now.

Now select this new saved document from the journal in the "Link" line.

In field " Tabular part"Select" Products ".

13. Please note that now you need to go to the "Spreadsheet Document" tab and again select our price list in Excel from which we loaded the names, and now we will load the prices (see paragraph 5).

14. Configuring the download parameters

Again, set the "First row of data sheet of the spreadsheet document" - as well as when loading names, set "9" and "Manual column numbering".

In the line "Nomenclature" set the download mode "Search", "Column No. of the spreadsheet document" - in our example, set "3". If identification by article is required, then in the column "Search by" select "Article", and in the "Column No. of the spreadsheet document" we put the number of the column with articles - in our example, "2".

In the line “Price” we set the download mode “Search”, “Column No. of the spreadsheet document” - we put the number of the column with the prices that we want to download, first we will load the retail price, which means we put “5”.

In the "Currency" line, set the Download Mode to "Install", select the currency (in the example, "RUB")

In the line "Price type" set the Download Mode "Install", select the price that will be loaded "Retail".

If in your price list the prices of goods are given in different currencies, which is indicated in a separate column of the downloadable price list, then in the column “Download mode” set “Search” and the number of the corresponding column of the price list in “No. Column of the spreadsheet document”.

In the line "Price calculation method" set the Download Mode "Install", select "By percentage markup for the base type"

Click "Download" and answer "Yes"

15. Open the document "Setting item prices", into which the data was loaded (by clicking the button with the magnifying glass in the "Link" line)

16. Check the document, if everything is in order, click "OK".

Prices should be fixed.

17. Open the stock list reference book, check the "Prices" tab in the loaded items card, click the "Re-read current prices" button.

If everything is in order, then you can start loading the rest of the prices (wholesale, purchase, etc.). The procedure is the same.

Please note that 1C: Trade Management 8 supports the calculation of some prices based on others. Therefore, it may be enough to download only one base price, for example, Retail or vice versa Purchase, and for other prices, specify the formation algorithms (% discount,% markup, rounding to a sign, etc.).

If in the future you plan to use this data loading procedure with the same boot parameter settings, we recommend that you save the current settings. To do this, click the "Save Settings" button and clearly name the current setting, for example, "Loading Retail Prices from the Excel Price List".

Next time you can load the saved settings by clicking on the "Restore settings" button and choosing the one you need from the list.

We use all the same processing of "LoadingDataFrom TabularDocument.epf" from the ITS disk.

19. Set the "Download Mode" - "Loading into the register of information".
"Register type" select "Barcodes".

20. Please note that now you need to go to the "Spreadsheet Document" tab and again select our price list in Excel from which we loaded the names and prices, and now we will load the barcodes (see paragraph 5).

21. Configuring the download parameters

Again, set the parameter “First row of spreadsheet document data” - set “9” and “Manual column numbering”.

Configuring boot parameters

We remove the "jackdaws" from all lines from which it is possible.

In the remaining lines, set the parameters

In the line "Barcode" set the Download Mode "Search", in the column "Column number of the spreadsheet document" write as in our price list "9".

In the line "Owner" in the column "Description of types", open the window "Edit data type", set (leave) the checkbox only in the "Nomenclature" field. Set the download mode to "Search". For identification by Article, in the column "Column No. of the spreadsheet document" we write the number of the column in which the articles are indicated in our price list - i.e. "2". In the column "Search by", set "Article".

If the price list does not contain a column with articles, then in the “Search by” column, select “Name”, and in the “Column # of a spreadsheet document” cell, indicate the number of the price list column with names.

In the line "Barcode Type" in the "Boot Mode" column, select "Install", in the "Default Value" column, select "EAN13".

In the line "Unit of measurement" in the "Load mode" field, select "Calculate". In the "Expression" field, write the algorithm in the built-in 1C programming language. For example, code like this:

Nomenclature Reference = Directories.Nomenclature.FindByName (CurrentData ["Owner"]); Result = NomenclatureLink.Remainst Storage Unit;

In the line "Quality" in the column "Download mode" select "Install", in the column "Default value" select "New".

In the lines "Item characteristics", "Nomenclature series", "Delete code" in the "Load mode" column, select "Install", in the "Default value" column, leave empty cells.

Click "Download"

We open the product card, check for the presence of a barcode.

If in the future you plan to use this data loading procedure with the same boot parameter settings, we recommend that you save the current settings. To do this, click the "Save settings" button and clearly name the current setting, for example, "Loading product barcodes from the Excel price list".

Next time you can load the saved settings by clicking on the "Restore settings" button and choosing the one you need from the list.