Configuration: 1c accounting

Configuration version: 3.0.44.177

Publication date: 20.01.2017

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

Loading 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 purchase).

Come in.

Press at the top Nomenclature and Prices - Download. This is where loading and unloading from spreadsheet documents is hidden.

The download supports xls(excel 97-2003 old 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 loaded in the form as we saw it in Excel, now we will assign the columns and remove the excess.

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

Select the line that has unnecessary entries, namely the table header and press Delete line.

Now we remove the column with the purchase price, note that processing is able to fill in only 1 type of price at a time!!! Then you can do this operation again and load the 2nd type of price, for the 1st load 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 compare the nomenclature. Fill out and click Download after which the data will be loaded into 1C: Accounting.

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

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

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

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

It's all there in the guide. Nomenclature - Nomenclature and Prices - Upload.

In step speakers, select those columns that need to be built in the Excel document. Selection serves for selection of nomenclature by Groups, properties, names... etc.

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

• When performing the exchange of information between counterparties (price lists, reconciliation acts, etc.);
• To facilitate the work of operators in cases where the main accounting is kept in 1C, and some of it is performed in Excel tables;
• During the initial filling of the database.

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

Universal movement

On the ITS disks, as well as on the 1C portal, by going to the menu "Technological support" -> "Universal reports and processing", in the folder "Loading data from a spreadsheet document" there is a corresponding processing.

When it is launched, a 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. The tabular part of the document or reference book;
3. Register of information.

Depending on the position of the switch, the object selection field changes.

The processing tabular form is filled in when you open the uploaded file.

Type processing supports uploads from:

• Developed by specialists of the company 1C, files of the formatmxl;
• Lisztxls saved in the formatExcel 97-2003;
• text filetxt;
• tablesdbf.

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

It is important to understand that if line groupings, subheadings, and notes exist 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 spreadsheets 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), in order to exclude their processing by the program on the form, in the "First line of the spreadsheet document" attribute, you must specify the first line with the transferred information

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

Settings columns

Flag - by checking or unchecking the box in the line, it is determined whether the corresponding attribute will be filled in.

Representation of the attribute - a synonym (name) of the metadata attribute is written here, as it is specified in the configurator.

Search field - if you check this box, the processing will search for elements by the corresponding attribute and, if successful, change the existing data, additional function this field - protection against the appearance of duplicates.

Description of types - displays the data type that has one or another metadata attribute.

Download mode - offers three options to choose from (Fig. 3):

Fig.3

• Search - the corresponding element will be searched, in case of its absence, a new one can be created;
• Set - a certain value is set in a subordinate way;
• Calculate – in this case, the result of the calculation of the expression specified in the "Link condition/Expression for value" column will be set in the field of the element being created.

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

Fig.4

Column number - a field used to indicate from which column of the Excel spreadsheet you need to take data for filling.

Default value - quite often a situation arises when the uploaded 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 for filling.

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

Here, in principle, is all the information that is available on the "Settings" tab.

In order not to kill a lot of time each time for loading, prescribing correspondences and expressions, the developers have provided the ability to save the settings option to a file with the mxlz extension.

You can check the correctness of the transferred data by clicking on the "Fill control" button (Fig. 1). After that, you can start the download process. You will be notified separately about the successful completion of the procedure or non-standard situations.

To upload data from Excel to information bases"Management and trade" there is another mechanism. It is less universal than the above method, however, it 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. The field for selecting the counterparty who sent his price list;
3. A button that allows you to select the type of prices to be set;
4. A tabular section that can be filled with uploaded data.

This form can be seen in Fig.6

Fig.6

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

After selecting a counterparty (depending on whether it acts as a supplier, commission agent or seller), additional columns for each price type 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 that, 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 press 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 there are none, will offer options to resolve the error. Appearance tabular part can be controlled with a switch. In addition, the user can independently compare the elements of the uploaded file with those 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:

• A corresponding element will be created in the "Supplier nomenclature" directory (if it was not there);
• It will be matched with an element of the reference book "Nomenclature";
• The document "Setting item prices" will be created and posted, indicating: supplier, price type and data fixing date.

Processing "Loading goods from external files" 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 to both file and client-server database storage options;
• Through Microsoft use Office - a method that sometimes fails when working with SQL databases, as a rule, it works somewhat slower than the first method, and Office is also required;
• Through Libre Office - unlike the previous method, it is free, in addition to xls and xlsx formats, it also supports its own tables, but requires an installed LibreOffice package and some preparation of the uploaded file (the first row of the table should contain the names of the columns).

Let's take a closer look various ways and options.

Via ADODB.Connection

In general, ADO stands for ActiveX Data Object and serves to program 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.

There are three options for Excel files:

Connection string options:

• Provider - the driver used is determined 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 - from the second), whether the file is opened read-only (ReadOnly) and some other additional options.

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) start fetching information from the downloaded file.

AT this case the “Sheet” parameter determines which sheet from the excel workbook we have to work with.

The set of records stored on a sheet can be read using the Recordset object. In this case, the first record of the sheet can be obtained by the BOF (beginning of the 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 the database drivers, Excel must be installed on the computer where the connection is made. Only in this case we can initialize the application for reading data from the table (Figure 16).

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

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

After that, you can iterate inside the cycles of 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 in use by another application. It's fine if the taskbar shows that Excel is running on the computer, but if you or another user opened it from external processing, visually this can only be determined through the "Task Manager", so do not forget to close the connection before the end of 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. twenty.

The connection and the procedure for completing work with data are best organized inside the Attempt-Exception-End Attempt construct, causing an error description in an exceptional situation. Although this sometimes slows down the work, it greatly simplifies the determination of the cause of the error and, ultimately, ways to eliminate it.

The specialist answers:

It should be immediately indicated that initially in 1s loading data from excel is not provided, for this there is an external loader 1s 8.3, which means that your question can already be answered - yes, it is possible.

In order for the download from excel to 1s 8.3 to occur without distorting the data, you must first prepare the table directly in excel. The list that is loaded in v1c must be properly structured, i.e. it is not allowed to have several data types in one column or one cell (quantity, number, last name, etc.).

If the table is extremely accessible, then in 1s the download from excel will pass without any problems. Next, you should perform processing 1s to load data from excel. To complete the processing, go to the File menu, specify Loading Data From Spreadsheet Document.epf In the pop-up window in the Load Mode line, it says Loading to Directory. The line below indicates the type of directory - Nomenclature. Then we open the file and look for the Excel Sheet (*.xls) in the list, select the file that contains the information we need to download. Next, let's go to the settings, in the line The first line of the data of the spreadsheet document indicates the number 2, since the header of our table is in the top line.

Then we number the columns, indicating the Manual numbering of the columns. We disable all the checkboxes, for this there is a certain button on the toolbars. The Name and Full name fields marked with flags, do not touch the mode, let it remain Search, set the numbering of the columns corresponding to the Excel table.

It remains only to specify the unit of measurement and the VAT rate, if you do not put it right away, then later you will have to put down manually one item at a time. Checkboxed VAT rate Base unit of measure, mode – Set, set Default value and directly 18% in the Rate line. The preparation has been completed. Specify Spreadsheet Document and click Fill Control. A sign indicating that there are no errors should appear.

Now you can safely download. The loading of the nomenclature from excel into the 1s program is completed

How can I download documents from a supplier by e-mail in 1C: Accounting 8, edition 3.0?

The video was made in the program "1C: Accounting 8" (release 3.0.42.84).

Significantly simplify the process of exchanging documents between contractors, as well as reduce the cost of delivering documents, purchasing consumables and storing archives, the transition to electronic document management (EDM) will help. Since April 8, 2011, the Federal Law of April 6, 2011 No. 63-FZ “On electronic signature”, which determined the procedure for obtaining and using an electronic signature and the obligations of participants in the exchange of electronic documents. For more information about the exchange of electronic documents, see the reference book "Exchange of electronic documents" in the section "Accounting and tax accounting".

If the counterparties are not participants in the EDF, then simplify the process of registering the primary accounting document in information system E-mail exchange of documents will help.

Accounting system documents Buyer invoice, Implementation (act, waybill), Invoice issued can be sent to the counterparty by e-mail (by clicking the button Send by email with an envelope). The document is sent as an attached file in the format selected by the user. Also attached to the letter is a file intended for download by the recipient in "1C: Accounting 8" (rev. 3.0).

You can upload the documents received from the supplier in the forms of lists of documents.

Invoices for payment are uploaded in the form of a list of documents Money orders. Invoices and acts - in the list Receipt (acts, invoices).

The recipient can upload documents of the form or UPD from files with the extension xls, mxl or in the format approved by the Federal Tax Service of Russia. Documents can be loaded from a file or from Email. You can choose the download method in the form of a list of receipt documents by clicking the button Download. When loading, the program checks the format for compliance and reports the results of the check.

When uploading documents TORG-12, Act of acceptance and delivery of works (services) or UPD an accounting system document is created Receipt (act, invoice), in which the necessary details are automatically filled in. In addition to receipt documents, a document can be additionally created Invoice received.

If the goods or service is delivered to the recipient from the supplier for the first time, and the items of the buyer and supplier are not synchronized, then in the opened form Comparison of data by nomenclature field must be filled Nomenclature by selecting the appropriate item from the directory Nomenclature, and click on the button Write and close .

E-mail accounts are configured in the section Administration - Organizer. The e-mail address of the counterparty must be specified in the form of a directory element Counterparties.

Please note that uploaded documents from e-mail (from a file) are not legally significant documents, therefore, both the buyer and the seller must have properly executed documents in paper form.

In this article, we will consider two very important topics with you, the first is, and the second is of course uploading data from 1s to excel. It should be noted that these topics are of interest to two main audiences of users, they are:

• Users of the 1C Enterprise system
• 1C programmers

1C users usually want to order, for example data transfer from excel to 1s in accordance with their specific porting requirements, while 1C programmers are more likely to look for some clarification or code examples. Based on these considerations, we decided to divide the information given in this article into two parts for users and programmers of 1C.

1C loading from Excel and unloading from 1C to Excel through the eyes of an ordinary user 1C

Very often it turns out that an enterprise or some firm, for example, has documentation that is stored on a computer in Excel files. And often users have a question, but how upload or download data from Excel to 1C. Of course, one cannot do without knowledge of programming, so if you are a simple user, then you need to order 1c services from us, because absolutely any of our 1c programmers will always listen to you, select a specially designed solution just for you, and will definitely help you.

Why is there no universal processing for loading data from Excel to 1C?

Let's answer this question and look at its main points:

1. Each user has different Excel files, the data from which he wants to transfer to 1C, of ​​course, these files have a different number of rows and columns, and also carry a different semantic load.
2. Each user works with a separate configuration, i.e. configurations can be different, what works in one configuration will not work in another configuration without upgrading.
3. Data from Excel must be transferred to different objects, and therefore different code is needed, because each user has his own requirements.

It is for these reasons that you cannot create universal processing for loading data from Excel to 1C.

How data is loaded and unloaded from Excel to 1C on the example of real development

Now let's take a look at the general how data is loaded and unloaded from Excel in 1C, and how will it all look in the finished solution?. An example of a real individual development, as well as the original Excel file are presented below.

In this processing, you first need to select excel file, then click on the "Run" button, after which the necessary data from Excel will be loaded into the tabular part. Now all that is left for the user to do is to click the "Transfer to book" button, after which the data will be transferred from Excel to 1C.

Uploading data from 1C to Excel happens about the same, only here the complexity of writing processing depends on what kind of data should be unloaded from the object. For example, we have the “Nomenclature” reference book and we want to upload the name of the item itself and the unit of measurement of this item, although the item itself can have many more details, such as item type, item type, full name, etc.

I need processing for loading and/or unloading data from Excel, what should I do?

All you have to do is pick up the phone and call us. After that, our specialists will perform all the necessary actions so that you get exactly what you need. If you need to perform an urgent 1c update, then call us, we are happy to help each of our clients!

Loading and unloading data from Excel to 1C through the eyes of an ordinary 1C programmer

A little higher, we gave a lot of useful information for 1C users, but now it's time to talk about the technical aspects of writing code, as well as provide information for 1C programmers that will be very useful to them when writing code. Consider the main frequently used functions for working with Excel.

We will start with how to connect Excel to 1C, this is done through a COM object. In order for this code to work properly, Excel must be installed on the computer, otherwise nothing will work:

After we have connected Excel, we need to open the necessary document from which the transfer will be carried out:

Now we need to select a sheet of an excel book, in the figure above we see how a sheet with a certain number is selected. The sheet number is stored in the "Sheet number" variable:

We can find out the number of sheets in an excel workbook using the "Count" field:

To get the name of a sheet under a certain number, you need to write a similar line:

After we have finished all the steps to work with Excel itself, the process must be closed. Close it, otherwise it will hang:

An example of a function that loads individual cells from Excel to 1C

We have reviewed the basic procedures and functions that every 1C programmer encounters when writing processing for loading data from Excel to 1C. Now let's take a closer look at the function that reads individual cells in an excel file.

We think that there is no need to provide any additional explanations for this code, since all these functions were discussed in detail above.

An example of a function that loads all data from Excel into a 1C tabular part

We have considered a function that displays individual Excel cells. But what if we need to unload the entire excel sheet at once and bypass it sequentially? This is where the code below comes in handy:

An example of a function that uploads data from 1C to Excel

Above, we have analyzed a lot of interesting things, but we did not touch on such a topic as uploading data from 1C to Excel. In fact, the principle of operation here is almost exactly the same, except that we programmatically create new file excel, and write it programmatically. Well, in order to write a value to a cell, as you probably already guessed, you need to put the expression Sheet.Cells (x, y) on the left side. An example of uploading data from 1C to Excel is presented below: