Loading barcodes in 1s 8.3 from excel. Accounting info
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 measurement, 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 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.
For loading, we use the external processing LoadDataFromSpreadsheetDocument.epf for managed forms (8.2 and 8.3). It is generic 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 in which 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 upload:
Let's give an example on the reference book "Nomenclature". We create a file with data in xls format.
Processing can also load files of the following format:
- any other format where data can be copied from
This is how the Excel file with the initial data looks like, which we will upload to the "Nomenclature" reference book:
We did not begin to prescribe all the fields of the directory, since this will 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 the 1C information base, and we will now upload them there.
Click on the “Open” icon (marked as “Source Selection” in the figure) and select the data file. You can use a simple copy of the information. The data will be loaded into processing automatically. Now let's check if we filled in our data correctly. To do this, click the "Fill control" button.
As you can see, we have errors! We will eliminate. Go to the "Settings" tab:
Before correcting mistakes, let's pay attention to one important detail. The program initially does not know which field to search for the directory element in case it is already there. So she needs to point it out. For example, we search by code, since it is usually unique. In the column "Search field" in the line "Code" put a tick. Now, if an entry 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 it 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 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 there is a code in the “Unit” column. And in processing, you need to specify that the search should be carried out by code. We double-click on the "Condition condition" column opposite "Unit" and change it to "Code".
Now we look at what they say to us about the “Service” in the list of errors. One more important note. The columns in the file must be placed strictly in the same order as the rows of the lookup fields. And we have the “Nomenclature type” at the very bottom, and in the file after the “Comment” column.
In order to raise the line "Nomenclature type" up, there are blue arrows at the top of the form. Using the "Up" arrow, we raise the desired line and put it under the "Comment".
Click "Load data" and this time everything goes without errors:
Source: programmer1s.ru
It is no secret that a successful 1C application was developed to help accountants, allowing you to perform multiple operations quickly and as efficiently as possible. However, novice accountants sometimes have to "sweat" a little to figure out all the possibilities that this software is fraught with. Of course, having delved into the mechanism for the implementation of individual operations, having understood everything to the smallest subtleties, accountants not only remain satisfied with the software, but also notice an increase in the effectiveness of their work.
One of the pressing problems faced by novice accountants who do not have any experience with 1C is loading into 1C from the previously created Excel. Indeed, it’s difficult to figure out on your own how to save a document in Excel in 1C, and it’s also problematic to figure out how to save from 1C to Excel in order to subsequently work directly with a spreadsheet editor.
So, in order to eliminate all problems, you should focus and study the instructions, delving into each step of the proposed algorithm. Of course, beginners should not show their own creativity when carrying out such manipulations. Only experienced users can afford to perform more complex operations. Novice accountants first need to just “get their hands on it”, learn how to perform actions in which they manage to save data in Excel. Only after that you can expand your knowledge on how to import Excel into the 1C program.
Transaction mechanisms
I would also like to note that the mechanism for performing the desired actions may differ depending on which version of the 1C: Accounting program is installed on the computer. In the most recent versions, this process is no longer accompanied by particular problems. That is why experienced users recommend tracking the appearance of updates, and if they are found, be sure to update the program. If the latest version of the software is installed on the computer, then it will be easier to figure out how to upload it to Excel in 1C or save it in the program.
Download algorithm
First, decide what exactly you would like to save in 1C. Open the table you created earlier in Excel, check whether it is important for you to load all the data into the program. If you later do not need some columns in the spreadsheet document, since they are of no value, simply delete them in advance so as not to overload the 1C: Accounting program. After that, do not forget to re-save the table you edited.
Now you can proceed to the immediate steps that allow you to upload to 1C from Excel. First open the program itself, find the line "References" on the left side, click on it. Additional subsections will open, among which stop your choice on the line "Nomenclature". Now a menu will be located on the horizontal panel, in which find "Nomenclature and prices", by clicking on it, a sublist will open, in which you will find the desired word "Download".
In order for the import to succeed, it is important to ensure that the downloaded document is saved in the correct format. It is allowed to import into 1C: Accounting documents that you managed to save in Excel format - 2003 or later versions. Also, the document can be saved in mxl and ods format. If you have done everything in advance in this way, then you can import.
It is easy to understand how to save in 1C in Excel by clicking on the "Download" button. The usual download window will appear on the screen. It remains only to specify the path where the document you want to download is located. After the import into 1C from Excel is completed, please note that in the program it will be exactly the same as it was in the spreadsheet editor.
This completes the import, but your actions continue, especially if you have not previously edited the table, so there are extra rows and columns in it. Above the columns you will find a sentence such as "Specify the props", by clicking on it, you will need to select one of the options offered in the list. After you have successfully worked with each column, review the rows. If you find something superfluous, feel free to delete it.
To perform such manipulations, select the line, and then click on the "Delete line" button.
Studying the instructions on how to upload to 1C from existing Excel, you can easily find tips on the fact that after editing the table, you need to fill in the missing data. If you do such manipulations with scrupulousness, it will be easier to work with the program later. Similarly, it will be easier to search.
This completes the import of the spreadsheet editor into the 1C: Accounting program. If you strictly follow the instructions on how to unload from Excel in 1C, but something goes wrong, make sure that the program itself is accompanied by the latest version.
Unloading algorithm
So, having thoroughly figured out how to load documents created earlier in Excel in 1C, you can now delve into how to save a document from 1C in Excel. This need is also often faced by some accountants. Fortunately, in new versions of the program, this feature is not only available, but the performance of such tasks is already accompanied by noticeable relief.
First of all, the uploaded document can be used as a price list that can be sent to partners and suppliers. To "force" the file to be unloaded, you should initially go along the already familiar route, opening first "Nomenclature", then "Nomenclature and prices", and at the end this time the line "Unload". After that, a window will appear in which you will need to specify the columns that you would like to see later in Excel.
It is also possible to add additional columns, on the upper horizontal panel for solving such intermediate tasks there are buttons "Add" and "Delete". After you have made sure that all the necessary data is involved, marked, you should click the "Upload" button, specify the path where you want to save such a file and just wait for the process carried out by the program itself to complete.
You won't have to wait long, because the operation is carried out quite quickly.
When the program announces the completion of the process, it remains just to open the uploaded file and make sure that it contains everything that will be needed for further work. Experienced accountants can form a load of not one position, but several at once, counted not in tens, but in whole hundreds. Thanks to such actions, time is saved significantly, which is welcomed by those who previously had to spend a huge time resource to complete such tasks.
So, the process of unloading and loading Excel documents into the 1C: Accounting program was previously accompanied by increased problems. At the moment, having updated the program to the most current version and having studied useful recommendations, any accountant can cope with such tasks.
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 counterparties, 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" has been in force, 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 the exchange of documents by e-mail will help to simplify the process of registering the primary accounting document in the information system.
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. You can upload documents 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 the 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:
- 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.
- 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.
- 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 the 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 measure 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 get acquainted in more detail with the function that reads individual cells of 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 work here is absolutely almost the same, except that we programmatically create a new Excel file, and programmatically write it down. Well, in order to write a value into 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:
