Loading data from a spreadsheet document 8.3. Accounting info

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 uploading Loading DataFromSpreadsheetDocument.epf for managed forms (8.2 and 8.3), which can be downloaded . It is generic and should be suitable for any configuration written for a managed application.

If you are a programmer and want to develop "your" 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 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:

I want to give an example on the reference book "". I have created a data file in xls format.

Processing can also load files of the following format:

any other format from which data can be copied.

This is how the Excel file with the initial data looks like, which we will upload to the "Nomenclature" reference book:

Get 267 1C video lessons for free:

I did not begin to prescribe all the fields of 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 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. My example file can be downloaded. 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, I want to 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. I prefer to search by code as it is usually unique. In the column "Search field" in the line "Code" put a tick. 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 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 in the "Unit" column I have a code. 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 let's see 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 "" at the very bottom, and in the file after the "Comment" column.

In order to raise the line "Type of item" 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:

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

11
ATTENTION! When using the codes described below, remove the symbol _ Let's look at examples of how to do it! =) I know 2 ways: 1. By replacing the template text in the Word layout. 2. By pasting text with... 9
Convert tables from Excel to MXL - download Converter Excel v MXL: For 8.x - For 7.7 - // Select Microsoft Excel file Procedure FileNameStartChoice(Element, StdProcessing) StdProcessing = False; DialogFile = New FileDialog(FileDialogMode. 3
I think everyone has come across a single-sheet excel file from 1C, which at first glance does not have the ability to add sheets. Everything is not so scary - they were simply hidden or, most often, they were not even displayed (due to the creation ... 2
Procedure SaveTableToExcelFile(Table, FullFileName=" " , OpenFileSelectDialog=True, UploadedColumns=" ") Export If EmptyString(FullFileName) OR OpenFileSelectDialog Then Dialog =...

16.06.2010

As in 1C: Accounting 8 rev. 2.0, download the stock list directory from an Excel file

Get access to the 1C: Fresh cloud for free for 30 days!

Quite often, situations arise in practice when it is necessary to load data into the 1C program from an Excel spreadsheet, for example, download the stock list reference book from the price list or the Purchase Invoice from the Supplier's Invoice in electronic form sent by e-mail.

The need for automatic exchange is especially relevant when the number of elements to be entered is tens, hundreds or even thousands, and manual entry of such a volume of data is a rather laborious work. Some need to download data once only when starting to work with the program, others need to enter large amounts of data all the time due to the technology of their work.

Naturally, we would like to avoid these wasted efforts and precious time in our age of electronic document management.

In such cases, many people order the appropriate processing from their own full-time programmers, franchisees or third-party developers, but these are additional costs and time.

What if for various reasons this is not possible?

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

Typical processing "Loading DataFrom Spreadsheet Document.epf", located in the section: "Technological support" > "Methodological support 1C: Enterprise 8" > "Universal reports and processing" >
D:1CIts\EXE\EXTREPS\UNIREPS82\UploadFromTableDocument

Please note that starting from February 2010, on the ITS disk, this processing for configurations on the 8.1 platform 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

Processing from the 1C company itself is universal and allows you to upload data to any configuration on the 1C: Enterprise 8 platform, to any directories, documents and information registers from *.xls (Excel), *.mxl, *.txt, *. dbf.

The processing capabilities are very large, and within the framework of one article, it is not possible to cover all possible use cases. Consider only the most frequent and popular examples.

Example 1. Loading the nomenclature reference book into 1C: Accounting 8 ed. 2.0 from the price list toExcel.
Example 2. Loading the nomenclature reference book in 1C: Trade Management 8 from the price list in Excel.
Example 3. Loading a directory of individuals in 1C: Salary and personnel management 8 from Excel. (soon)
Example 4. Loading an invoice in 1C: Trade Management 8 from an Excel document.

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 in 1C from Excel the names of goods and prices.

From the Price List, you need to download the nomenclature reference book in 1C: Accounting 8.

Buy the minimum version of ITS for 1 month for 4818 rubles.

1. We launch the 1C program

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

3. In the window that opens, find the processing file, which is 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:

Set the "Download Mode" - Download to the directory

"Directory view" - Nomenclature

5. In the "Spreadsheet document" tab, click the "Open file" button, in the selection window that appears, we find the directory in which we have the price list in Excel from which we plan to upload data to 1C.

The file may not be displayed initially. Set the file type to "Excel Sheet (*.xls)"

Choose our file

The details of the nomenclature card are displayed.

8. Set up the parameters for loading titles

For convenience, set "Manual column numbering"

At this stage, only the “Name” is of interest, so we uncheck the boxes that are not of interest to us yet

“The first line of data of a spreadsheet document” - we look at our price list, discard the header, the data for loading starts from the ninth line - we put “9”

“Do not create new elements” - we DO NOT check the box, the option is needed if we plan to change existing elements

The lines “Name” and “Full name” - set the Loading mode to “Search”, “No. Columns of the spreadsheet document” - in our price list, the name in the third column - put “3”

In the "Name" line, check the box in the "Search field" column (second column) - the program will check for the presence of an element with this name, and if it already exists, then a new element with this name will not be created.

Please note that in the 1C: Accounting 8 program, the elements of directories are identified by name. If loading into a database that is no longer empty, visual control of the entered positions is necessary in order to exclude re-entry and duplication of elements.

This inconvenience is exacerbated if it is planned not just a one-time initial download of directories, but the use of such automatic data loading in the future. Since the different spelling of names, the use of abbreviations, punctuation marks, etc. will cause duplicate elements to appear.

With frequent use and large amounts of downloaded data, we recommend using other 1C software products from the 1C: Enterprise 8 line: “1C: Trade Management 8”, “1C: Small Business Management 8”, “1C: Integrated Automation 8”, which use props "Article" allows you to repeatedly increase the reliability of identification of the downloaded nomenclature and eliminate the appearance of duplicate elements of the directory.

The line "Parent" - in the column "Load mode" select "Install", in the column "Default value" select the folder (nomenclature group) into which we will load data from the price list (in our case, "Household chemicals").

The line "Basic unit of measure" - in the column "Load mode" select "Set", in the column "Default value" select the unit of measurement (in our case, units)

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

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

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

We have set the minimum values for the new elements of the stock list reference book, which will allow you to start working.

To close the month, allocate costs, generate accounting and tax reporting, you will need to set the values "Nomenclature 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 specify.

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

8. We check the presence of new elements in the corresponding group of the reference book of the nomenclature.

9. Open the product card, check the correctness of the details loading

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 for downloading data with the same settings for download options, we recommend that you save the current settings. To do this, click the "Save settings" button and clearly name the current setting, for example, "Load product names from the Excel price list".

In "Accounting of the enterprise" Rev. 2.0, setting prices is done using the document "Setting item prices".

11. Set "Loading mode" - "Loading to the tabular part".

The document journal "Setting item prices" opens.

Here you can select an existing document in which we want to add new elements, or create a New one (recommended), in the document we indicate which “Price Type” we will upload (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 part" field, 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 downloaded the names, and now we will download prices (see paragraph 5).

14. Set up load parameters

Again, set the "First line of spreadsheet document data" - as with loading names, set "9"

In the line "Nomenclature" set the loading mode "Search", "No. Columns of the spreadsheet document" - in our example we set "3"

In the “Price” line, set the loading mode to “Search”, “Spreadsheet Document Column No.” - put the number of the column with the prices we want to load - in our case, “4”.

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

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 "Download mode" column, set "Search" and the number of the corresponding price list column in "Spreadsheet Document Column No.".

Click "Download" and answer "Yes"

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

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

Prices must be fixed.

17. Open the stock list directory, check the "Prices" tab in the loaded items card.

If everything is in order, then you can start loading the remaining prices (wholesale, credit, etc.). The procedure is similar.

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

We wish you success!

To be continued...

Shavaleev R.F.

Most organizations in their daily practice to keep records of various accounting transactions in the old fashioned way use the Excel software product from the Microsoft office suite. 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 pivot table in the .xls format. It is not surprising that often there is 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.

There are many ways to solve this problem: some organizations prefer to entrust this work to their IT department, others hire developers working in a franchisee 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 integrating 1C documents and Excel spreadsheets.

In this section, we will help you figure out how to set up data loading from an .xls file into one of the software configurations from 1C based on 1C:Enterprise 8

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

Typical processing "Loading DataFrom Spreadsheet 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 the 8.1 platform 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

There are initial data:

Price list in Excel "PriceOnlineChemSnab.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 in 1C from Excel the names of goods and prices

From the Price List, you need to download the nomenclature reference book in 1C: Accounting 8.

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

In the window that opens, find the processing file

It is 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 the names and setting the values of the details of the new elements of the directories necessary to start working (unit of measurement, VAT rate, product / service sign). Stage 2 - loading prices.

Loading titles

Set the "Download mode" - Loading into the directory "Directory type" - Nomenclature

In the "Spreadsheet" tab

We press the button ex1_b01.jpg "Open file", in the selection window that appears, we find the directory in which we have the price list in Excel from which we plan to upload data to 1C.

The file may not be displayed initially. Set the file type to "Excel Sheet (*.xls)"

Choose our file

The details of the nomenclature card are displayed.

Setting the parameters for downloading titles

We want to focus your attention on the aspect of recognizing the structural units of item directories in the 1C: Accounting version 8 program, because one of the key points in identifying these elements is the name. If initially the positions of the infobase were not filled, then you will need to keep track of duplicate positions and, if any, delete them.

Duplicate names are difficult to avoid, especially for employees who will use this type of upload not for a one-time entry of data into the list, but for the systematic upload of information in automatic mode.

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 array of downloaded information, we recommend using other software from the 1C: Enterprise 8 series, for example, 1C: Trade Management 8, 1C: Small Business Management 8, or 1C: Integrated Automation 8, which have the "Article" parameter, which ensures the reliability of recognition of the data loaded into the nomenclature reference books and excludes the possibility of duplicating names.

The line "Basic unit of measure" - in the column "Load mode" select "Set", in the column "Default value" select the unit of measurement (in our case, units)

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

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

We have set the minimum values for the new elements of the stock list reference book, which will allow you to start working. To close the month, allocate costs, generate accounting and tax reporting, you will need to set the values "Nomenclature 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 specify. Click "Download" in the lower right corner, click "Yes" to the question

We check the presence of new elements in the corresponding group of the stock list directory

Open the product card, check the correctness of the details loading

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 for downloading data with the same settings for download options, we recommend that you save the current settings. To do this, press the button ex1_b03.jpg "Save settings" and clearly name the current setting, for example, "Load product names from the Excel price list".

The next time you can load the saved settings, click on the button ex1_b03.jpg "Restore settings" and select the one you need from the list.

Loading prices

In "Accounting of the enterprise" Rev. 2.0, setting prices is done using the document "Setting item prices".

Set "Loading mode" - "Loading to the tabular part".

In the "Link" field, select "Set item prices".

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 part" field, select "Products"

Now you need to go to the "Spreadsheet" tab

Again, select our price list in Excel from which we loaded the names, and now we will load the prices (see point 5).

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