This method is simple. Its essence is 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. Need to remember this
when planning client-server interaction.

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

TabDoc . Write(FilePath, TabularDocumentFileType. XLSX);

Here TabDoc- generated spreadsheet document, The path to the file— name of the file to upload, TabularDocumentFileType.XLSX— format of the created file. The following Excel formats are supported:

• XLS95 - Excel 95 format;
• XLS97 - Excel 97 format;
• XLSX is an Excel 2007 format.

TabDoc = New TabularDocument;
TabDoc . Read(PathToFile, Method of ReadingTabularDocumentValues.Value);

Here The path to the file— path to the downloaded Excel file. Method of Reading Values ​​of a Tabular Document.Value determines how data read from a source document should be interpreted. Available options:

• Meaning;
• Text.

Exchange via OLE

Exchange via OLE automation technology is perhaps the most common option for programmatically working with Excel files. It allows you to use all the functionality provided by Excel, but is slower compared to other methods. To exchange via OLE, MS Excel installation is required:

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

Example unloading:

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

// Write a value to a cell
Sheet . Cells(RowNumber, ColumnNumber). Value = CellValue;

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

Excel . Quit();
Excel = 0;

Examples reading:

// -- OPTION 1 --

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

Sheet = Book. Worksheets(1);

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

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

// —— OPTION 2 ——

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

// Read the cell value, usually the cell traversal loop is located here
CellValue = Sheet. Cells(RowNumber, ColumnNumber). Value;

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

For bypass For all completed rows of the Excel worksheet, you can use the following techniques:

// -- OPTION 1 --
Number of Rows = Sheet. Cells(1 , 1 ). SpecialCells(11). Row;
For RowNumber = 1 By Number Of Rows Cycle
CellValue = Sheet. Cells(RowNumber, ColumnNumber). Value;
EndCycle;

// —— OPTION 2 ——
RowNumber = 0 ;
While the Truth Cycle
RowNumber = RowNumber + 1 ;
CellValue = Sheet. Cells(RowNumber, ColumnNumber). Value;
If NOT ValueFilled(CellValue) Then
Abort;
endIf;
EndCycle;

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

TotalColumns = Sheet. Cells(1 , 1 ). SpecialCells(11). Column;
TotalRows = Leaf. Cells(1 , 1 ). SpecialCells(11). Row;

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

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

Action	Code	A comment
Working with the application
Setting application window visibility	Excel . Visible= False;
Setting the warning output mode (display/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 workbook	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(SheetNumber);
Setting the name	Sheet . Name = Name;
Setting up protection	Sheet . Protect();
Removing protection	Sheet . UnProtect();
Setting the page orientation	Sheet . PageSetup. Orientation = 2;	1 - portrait, 2 - landscape
Setting the left border	Sheet . PageSetup. LeftMargin = Excel. CentimetersToPoints(Centimeters);
Setting the upper limit	Sheet . PageSetup. TopMargin = Excel. CentimetersToPoints(Centimeters);
Setting the right border	Sheet . PageSetup. RightMargin = Excel. CentimetersToPoints(Centimeters);
Setting the lower limit	Sheet . PageSetup. BottomMargin = Excel. CentimetersToPoints(Centimeters);
Working with rows, columns, cells
Setting Column Width	Sheet . Columns(ColumnNumber). ColumnWidth = Width;
Delete a line	Sheet . Rows(RowNumber). Delete();
Removing a column	Sheet . Columns(ColumnNumber). Delete();
Delete a cell	Sheet . Cells(RowNumber, ColumnNumber). Delete();
Setting the value	Sheet . Cells(RowNumber, ColumnNumber). Value = Value;
Merging cells	Sheet . Range(Sheet. Cells(RowNumber, ColumnNumber), Sheet. Cells(RowNumber1, ColumnNumber1)). Merge();
Setting the font	Sheet . Cells(RowNumber, ColumnNumber). Font. Name = FontName;
Setting the font size	Sheet . Cells(RowNumber, ColumnNumber). Font. Size = Font Size;
Setting bold font	Sheet . Cells(RowNumber, ColumnNumber). Font. Bold = 1 ;	1 - bold, 0 - normal
Setting italics	Sheet . Cells(RowNumber, ColumnNumber). Font. Italic = 1 ;	1 - italic, 0 - normal
Setting the underlined font	Sheet . Cells(RowNumber, 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 code of the recorded macro.

Using COMSafeArray

When downloading large amounts of data from 1C to Excel, you can use the object to speed up COMSafeArray. According to the definition from the syntax assistant, COMSafeArray is an object wrapper over a multidimensional array SafeArray from COM. Allows you to create and use SafeArray for exchanging data between COM objects. Simply put, 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, TotalRows);
// Fill COMSafeArray
For Page = 0 By TotalLines - 1 Cycle
For Number = 0 Total Columns - 1 Cycle
ArrayCom . SetValue(Count, Page, Value);
EndCycle;
EndCycle;
// Assigning an Excel worksheet area to values ​​from COMSafeArray
Sheet . Range(Sheet. Cells(1, 1), Sheet. Cells(TotalRows,TotalColumns)). Value = ArrayCom;

Exchange via ADO

An Excel file, when exchanged via ADO, is a database that can be accessed using SQL queries. Installation of MS Excel is not required, but you must have an ODBC driver with which access will be provided. The ODBC driver used is determined by specifying the connection string to the file. Typically, the required driver is already installed on the computer.

Exchange via ADO is noticeably faster than exchange via OLE, but when uploading, it is not possible to use Excel functionality for designing cells, laying out pages, setting formulas, etc.

Example unloading:

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

Compound . ConnectionString = "

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

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

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

// Assigning command text to add a table row
Team . CommandText = "INSERT INTO [Sheet1] (Column1, Column2, Column3, Column4) values ​​('abvwhere', '8/11/2017', '12345', '12345,6789')";
Command.Execute(); // Execute the command

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

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

// Create a COM object for working 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 workbook
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 column type. The parameter is optional, here are some column type values:

• 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 a connection

// Create a COM object to receive the selection
Select = New COMObject("ADODB.Recordset");
RequestText = "SELECT * FROM [Sheet1$]";

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

// Bypass the sample result
Not a selection yet. EOF() Loop
Column1 value = Selection. Fields. Item("Column1"). Value ; // Access by column name
Column2Value = Selection. Fields. Item(0). Value; // Access by column index
Sample . MoveNext();
EndCycle;

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. Possible options:

• YES - the first line is treated as 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.

These examples only cover a few ADO objects. The ADO object model consists of the following objects:

• 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 downloading, then they can be saved in Excel without programming.

To save a spreadsheet document (for example, the result of a report), 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 from dynamic lists (for example, a list of items), you must:

1. Output data to a spreadsheet document using the command More ⇒ List...;
2. Save the spreadsheet document in the required format.

In order to import prices into 1C 8.3, you need to prepare a file. The structure of the source file can be arbitrary, the main thing is that it contains columns with the name of the item, by which the system can determine whether such a position already exists in the information base, or whether a new one needs to be created:

Processing for loading items and prices into 1C 8.3 from a spreadsheet document is available from the directory list form Nomenclature- chapter Directories:

Before starting the import, the system will prompt you to specify an Excel file with the source data:

Comparison of item fields in 1C

At step 1, the columns of the source file are compared with the nomenclature and prices for those details that are defined for the elements of the Nomenclature directory. If the name of the column matches the name of the attribute, then the system will make such a comparison independently.

This comparison is used both to search the information base for existing items (otherwise a new one would be created each time during import), and in the event that the item is not found. Then, when creating a new item, the details are filled in from the corresponding columns of the file:

Since in our case we need to import item prices, we need to indicate from which column the system should take the price value. If the name of the price column matches the name of an existing price type, then such a column will be determined automatically. But since you can only import values ​​for one type of price at a time, only the first of the found columns will be defined, so this comparison of the type of price being loaded and the column in the file must always be controlled.

If the price column is not defined, then prices will not be imported:

If necessary, extra columns and rows are deleted, that is, data that should not be loaded.

Important: When importing prices, only one column with prices should remain. This is due to the fact that one document in 1C can register prices for only one price type:

After the file data has been properly compared, you can proceed to step 2, that is, directly to loading the data from Excel into 1C 8.3.

Loading price lists and products into 1C Accounting

In the second step, immediately before importing, the system will display information about which item items were matched with those already available and which ones will be created during import:

The user has the opportunity to make adjustments: refuse to create new items and explicitly indicate the item for which to set the price, set up filling rules for the newly created item, and more. If you are not satisfied with something, you can return to the previous stage using the button Back:

Since in this example, not only (and how much) the import of items is performed, but also the loading of prices, it is necessary to check the values ​​of the details Price type And Set prices for. They will determine the date of the document automatically created when loading and the document that will be registered with this document:

When you press the button Download New product items and a product price registration document will be created (if necessary) in the information base (again, if the prices were loaded) and the download form will be closed:

The automatically created document Setting item prices in further work is no different from those created by other methods. For the convenience of users, when loading prices from an external file, such documents have a corresponding comment: “#Loaded from file”:

For documents uploaded to the information base that were received as a result of the exchange, the system automatically assigns service comments: new uploaded, changed, canceled, posted. When a new document is uploaded to the 1C database or an existing one is overwritten as a result of changes, a comment is assigned to make it easier to track document modifications and respond appropriately to changes. For more information about the meaning of official comments in documents, watch our video:

• When exchanging information between counterparties (price lists, reconciliation reports, etc.);
• To facilitate the work of operators in cases where the main accounting is carried out in 1C, and some of it is performed in Excel tables;
• When filling the database for the first time.

To perform this operation, you can use both standard functionality, available with a subscription to information technology support (ITS), and independently written processing, implemented through various connection options. In our article we will try to analyze as fully as possible all possible cases and answer most of the existing questions regarding loading data from Excel into 1C.

Universal mechanism

On the ITS disks, as well as on the 1C portal, by going to the “Technological Support” -> “Universal Reports and Processing” menu, the corresponding processing is located in the “Loading data from a spreadsheet document” folder.

When you launch it, the form opens (Fig. 1):

As you can see from the form, it allows you to transfer information to the following configuration objects:

1. Directory;
2. Tabular part of a document or reference book;
3. Register of information.

Depending on the position in which the switch is set, the object selection field changes.

The processing tabular form is filled in when the downloaded file is opened.

Typical processing supports downloads from:

• Developed by specialists from 1C, file formatmxl;
• Lisztxls saved in the formatExcel 97-2003;
• Text filetxt;
• Tablesdbf.

Excel files of large size can take quite a long time to load, so if it is assumed that data loading can occur in several stages or you know for sure that you will have to open an existing file several times, to save nerves and time, it is better to save the information from it in mxl format. This can be done directly from the processing form.

It is important to understand that if there are line groupings, subheadings and notes in the source file, they will need to be removed manually.

Now let's move on to the “Settings” tab (Fig. 2):

Fig.2

Quite often, Excel tables contain a header with all sorts of details and data (name of the printed form, details of the counterparty, date and number of the incoming document, column names, and so on). To prevent them from being processed by the program on the form, you must specify in the “First line of the spreadsheet document” attribute the first line with the transferred information

After selecting a metadata object into which information will be written, the tabular part of the “Settings” tab will be automatically filled in with the names of details, descriptions of their types and other important information. A separate section should be devoted to the consideration of the columns of the tabular part of the “Settings” tab.

Columns "Settings"

Marking – by checking or unchecking a checkbox in a line, it is determined whether the corresponding attribute will be filled in.

Attribute representation – here the synonym (name) of the metadata attribute is written, as it is specified in the configurator.

Search field - if you check this field, processing will search for elements using the corresponding details and, if successful, change existing data; an additional function of this field is protection against duplicates.

Description of types – displays the data type that a particular metadata attribute has.

Boot mode – offers three options to choose from (Fig. 3):

Fig.3

• Search – a search will be carried out for the corresponding element; if it is missing, a new one can be created;
• Set – a certain value is subordinately set;
• Calculate – in this case, the result of calculating the expression specified in the “Connection condition/Expression for value” column will be set in the field of the created element.

In the latter case, activating the Expression field will open the form (Fig. 4).

Fig.4

Column number is a field used to indicate which column of the Excel table should be filled with data.

Default value – quite often a situation arises when the downloaded file does not contain all the data necessary to record an element; in this case, the information contained in this field will be filled in.

Link condition/Expression for a value – we have already partially touched on this field when we looked at calculated fields; in addition, you can specify the condition in accordance with which the source data will be synchronized.

This is, in principle, all the information that is available on the “Settings” tab.

In order not to waste a lot of time each time loading, writing correspondence and expressions, the developers have provided the ability to save the configuration option to a file with the mxlz extension.

You can check the correctness of the transferred data by clicking on the “Filling Control” button (Fig. 1). After this, you can start the download process. You will be notified separately about the successful completion of the procedure or unusual situations.

There is another mechanism for loading data from Excel into the Management and Trade information databases. It is less universal than the above method, but does not require an ITS subscription and is included in the standard delivery.

This processing can be found on the “Purchases” tab, in the “Service” menu, it is called “Loading supplier prices from files” (Fig. 5)

Fig.5

The processing form contains:

1. A date selection field that will indicate for what time this price is relevant;
2. Field for selecting the counterparty who sent their price list;
3. A button that allows you to select the type of prices to be set;
4. A tabular part that can be filled with downloaded data.

This shape can be seen in Fig. 6

Fig.6

The explanation at the top of the form explains how to use the first tab of the form.

After selecting a counterparty (depending on whether he is a supplier, commission agent or seller), additional columns for each type of price will become available in the table.

When working through the web interface, some browsers may require the installation of a browser add-on (Fig. 7). We need to click the “Start installation” button and restart our connection.

Fig.7

After this, using the clipboard, we will be able to transfer information from one table to another. When the columns we need (“Article”, “Name”, “Price”) are filled in, we click the “Next” button and go to the second page (Fig. 8)

Fig.8

The program will automatically search for matches within the database and, if none are found, offer options for eliminating the error. The appearance of the tabular part can be controlled using a switch. In addition, the user can independently compare the elements of the downloaded file with the data available in the database.

• Register everything;
• Register only those that have changed compared to those already in the database.

In the text field you can enter a comment that will be recorded in the document (Fig. 9):

Fig.9

After processing is completed:

• The corresponding element will be created in the “Supplier Nomenclature” directory (if it did not exist);
• The directory element “Nomenclature” will be associated with it;
• The document “Setting item prices” will be created and posted, indicating: supplier, type of prices and date of data recording.

The “Loading products from external files” processing works in a similar way.

DIY transfer processing options

The main problem in extracting data from an Excel file is that 1C does not have a built-in unambiguous mechanism for opening it. There are several options for connecting Excel to 1C:

• Through Microsoft ADODB - a fairly fast method, which, as a rule, is applicable for both file and client-server database storage options;
• Through the use of Microsoft Office - a method that sometimes fails when working with SQL databases, as a rule, it works somewhat slower than the first method, and it also requires Office installed;
• Via Libre Office - unlike the previous method, it is free, in addition to xls and xlsx formats, it also supports its own tables, but requires the installed LibreOffice package and some preparation of the downloaded file (the first line of the table must contain the names of the columns).

Let's take a closer look at the various methods and options.

Via ADODB.Connection

In general, ADO stands for ActiveX Data Object and is used for programmatic access to various databases. The biggest problem when creating any connection to a third-party file (including Excel) is to correctly compose the connection string.

For Excel files there are three options:

Connection string options:

• Provider – the driver used is defined here;
• Data Source – defines the name of the file that we will open;
• Extended Properties – here you can specify whether a header line is needed for the table (HDR = YES indicates that the data will be read from the first line, HDR = NO - what from the second), whether the file is opened read-only (ReadOnly) and some other additional parameters.

Having created a connection string, we can connect to the downloaded file (Fig. 13)

Fig.13

Now we can use a simple request (Fig. 14) to start retrieving information from the downloaded file.

In this case, the “Sheet” parameter determines which sheet from the Excel workbook we will work with.

The set of records stored in a worksheet can be read using a Recordset object. In this case, the first record of the sheet can be obtained with the BOF (beginning of file) parameter, and the last EOF (end of the file).

Via Excel application

The main difference from the previous method is that in addition to database drivers, Excel must be installed on the computer where the connection is made. Only in this case can we initialize the application to read data from the table (Fig. 16).

This COM object has several child parameters, but the main one for us, given the current conditions of the task, is the WorkBooks parameter (Fig. 17).

After initializing the workbook, it is necessary to determine the sheet from which data will be read (Fig. 18).

After this, you can loop through the rows and columns of the table of the file being opened.

A few words about possible errors

The lion's share of errors when connecting to a file occurs due to the fact that the file is already occupied by another application. It’s good if you can see in the taskbar that Excel is running on the computer, but if you or another user opened it from external processing, this can only be visually determined through the “Task Manager”, so do not forget to close the connection before completing the transfer procedure:

In the case of working through ADO (Fig. 19);

Fig.19

• In the case of working with the application (Fig. 20).

Rice. 20.

It is best to organize the connection and the procedure for completing work with data within the Attempt-Exception-EndAttempt construct, calling an error description in an exceptional situation. Although this sometimes slows down the work, it makes it much easier to determine the cause of the error and, ultimately, how to eliminate it.

Most organizations in their daily practice use the Excel software product from the Microsoft office suite to keep track of various accounting transactions in the old fashioned way. This happens when a company just recently switched to the 1C system and the reporting sections have not yet been properly debugged, or the supplier prefers to send a price list in the form of a summary table in .xls format. It is not surprising that there is often a need to replace manual entry of positions with an automated system. This will significantly reduce the time for filling out nomenclature documents and reduce the number of errors, which is important to consider when it comes to several dozen or even hundreds of items.

This problem can be solved in different ways: some organizations prefer to entrust this work to their IT department, others hire developers working in the franchisee’s company, but this is quite expensive and takes a lot of time.

A much more profitable way out of this situation would be to use the appropriate 1C configuration, which will process the data absolutely free of charge and become a universal assistant in the integration of 1C documents and Excel spreadsheets.

In this section, we will help you figure out how to configure the loading of data from a .xls file into one of the software configurations from the 1C company based on “1C:Enterprise 8”

Every user of the professional version of 1C:Enterprise 8 already has such processing! ITS on disk!

Typical processing “Loading Data From Tabular Document.epf”, located in the section: “Technological support” > “Methodological support 1C: Enterprise 8” > “Universal reports and processing” >

D:1CIts\EXE\EXTREPS\UNIREPS82\UploadFromTableDocument Download

Please note that starting from February 2010 on the ITS disk, this processing for configurations on platform 8.1 is located in another section: “Technological support” > “Methodological support 1C:Enterprise 8” > “1C:Enterprise 8.1 platform” > “Universal reports and processing" > "Loading data from a spreadsheet document".

D:1CIts\EXE\EXTREPS\UNIREPS81\UploadFromTableDocument Download

Source data available:

• Price list in Excel “PriceOnlineKhimSnab.xls” - Microsoft Office Excel 97-2003 (.xls)
• Typical configuration Enterprise Accounting Edition 2.0, release 2.0.12.2, platform 8.2 (8.2.10.77)
• It is necessary to load product names and prices into 1C from Excel

From the Price List you need to download the item reference book into 1C: Accounting 8.

We launch the 1C program. To start processing, select the menu item “File”> “Open”.

In the window that opens we find the processing file

Located on the ITS disk in the directory \1CIts\EXE\EXTREPS\UNIREPS82\UploadFromTableDocument

If you 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 two stages: Stage 1 - loading names and setting the values ​​of the details of new directory elements necessary to get started (unit of measurement, VAT rate, product/service attribute). Stage 2 - loading prices.

Loading titles

Set the “Download Mode” - Upload to the directory “Directory View” - Nomenclature

In the “Table document” tab

Click the ex1_b01.jpg “Open file” button, in the selection window that appears, we find the directory in which we have a price list in Excel from which we plan to load data into 1C.

The file may not appear initially. Set the file type to “Excel Sheet (*.xls)”

Select our file

The details of the item card are displayed.

Setting up title loading options

We want to draw your attention to the aspect of recognizing structural units of nomenclature reference books in the 1C: Accounting program version 8, because one of the key points in identifying these elements is the name. If the information base positions were not initially filled, then you will need to track duplicate positions and, if any, delete them.

Duplicate names are difficult to avoid; it is especially difficult for employees who will use this type of loading not for one-time data entry into the list, but for systematically loading information automatically.

The matter is complicated by the fact that the employee has to deal with various abbreviations and other abbreviations, double spelling of the name and the use of various punctuation marks.

With repeated use and a significant amount of downloaded information, we recommend using other software from the “1C: Enterprise 8” series, for example “1C: Trade Management 8”, “1C: Small Firm Management 8”, or “1C: Integrated Automation 8”, which have the “Article” parameter, which ensures reliable recognition of data loaded into nomenclature directories and eliminates the possibility of duplicating names.

Line “Base unit of measurement” - in the “Load mode” column select “Install”, in the “Default value” column select the unit of measurement (in our case - pcs.)

Line “VAT rate” - in our case, for all downloaded elements we set VAT to 18%, for this in the “Load mode” column we select “Set”, in the “Default value” column we select “18%”.

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

Line “Service” - in the “Loading mode” column, select “Install”, in the “Default value” column, select “No” (in our case, all positions are products).

We have set the minimum values ​​for new items in the nomenclature directory, which will allow you to start working. To close the month, distribute costs, generate accounting and tax reporting, you will need to set the values ​​“Item Group” and “Cost Item”; for imported goods, set the “Country of Origin” and “Customs Declaration Number”, but this can be done later using group processing.

If you are ready to set the values ​​of these details now, then indicate. Click “Download” in the lower right corner, click “Yes” when asked

We check for the presence of new elements in the corresponding group of the item reference book

Open the product card and check that the details are loaded correctly

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

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

Next time you can load the saved settings by clicking on the “Restore Settings” button and selecting the desired one from the list.

Loading prices

In “Enterprise Accounting” Rev. 2.0, prices are set using the document “Setting Item Prices”.

Set “Load mode” - “Load to table section”.

In the “Link” field, select “Setting item prices”.

Here you can select an existing document into 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 the “Tabular section” field, select “Products”

Now you need to go to the “Table document” tab

Again, select our price list in Excel from which we downloaded the items, and now we will download the prices (see point 5).

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

We use external processing for loading Loading Data From TabularDocument.epf for managed forms (8.2 and 8.3), which can be downloaded . It is universal and should be suitable for any configuration written for a managed application.

• If you are a programmer and want to develop “your own” download from Excel, detailed instructions for;
• An example of loading a price list in 1C Trade Management can be found at.

To start external processing, you need to go to the “File” menu, then “Open” and select this processing from the directory in which it was saved:

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

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

I want to give an example in the directory ““. I created a data file in xls format.

Processing can also load files in the following format:

• any other format from which you can copy data.

This is what the Excel file with the source data looks like, which we will upload to the “Nomenclature” reference book:

Get 267 video lessons on 1C for free:

I did not write down all the fields in the directory, since this is enough to understand the principle of loading. In addition, this data is enough to start working with these positions.

These positions are not yet in the 1C information database, and we will now upload them there.

Click on the “Open” icon (indicated in the figure as “Select source”) and select the file with the data. You can use simple copying of information. My example file can be downloaded. The data will be loaded into processing automatically. Now let's check if we filled out our data correctly. To do this, click the “Filling Control” button.

As you can see, we have some errors! We will eliminate it. Go to the “Settings” tab:

Before correcting errors, I want to draw your attention to one important detail. The program initially does not know in which field to look for a directory element in case it is already there. So she needs to point it out. I prefer to search by code since it is usually unique. In the “Search field” column in the “Code” line, put a checkmark. 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 a search field, duplicate items may appear. Especially after the second download of a similar file!

Now let’s see why “Unity” is swearing on the field. The fact is that units of measurement in 1C 8.3 are stored in a separate directory, and processing by default looks for this unit by name. But in fact, the word unit is written in the “Full name” field.

Unfortunately, processing can only search by “Name” and “Code” (for the programmer the possibilities are wider). If you pay attention to the pictures above, you will see that I have a code in the “Unit” column. And in processing you need to indicate that the search should be carried out by code. Double-click on the “Communication Condition” column opposite “Units” and change it to “Code”.

Now let’s see what they tell us about “Service” in the list of errors. One more important note. The columns in the file must be located strictly in the same order as the lines of the directory fields. And for us “” is located at the very bottom, and in the file after the “Comment” column.

In order to move the “Item type” line up, there are blue arrows at the top of the form. Using the “Up” arrow, raise the desired line and place it under “Comment”.

Click “Load data”, and this time everything goes without errors:

Video tutorial on loading products into 1C from Excel files: