Accounting info. Processing “Loading data from a spreadsheet document.” Example settings and use of 1s 8 loading data from excel

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

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

For loading, we use external processing LoadDataFromTabularDocument.epf for managed forms (8.2 and 8.3). It is universal and should be suitable for any configuration written for a managed application.

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

Processing Loading data from a 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 download:

Let's give an example in the reference book "Nomenclature". We create a file with data 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:

We did not write down all the fields in 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 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. 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, let's pay 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. Therefore, she needs to indicate it. For example, we 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 there is 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, “Type of item” 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:

Based on materials from: programmist1s.ru

30.06.2010

How to download a product reference book from an Excel file in 1C: Trade Management rev. 10.3

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

Quite often in practice, situations arise when it is necessary to transfer data to the 1C program from an Excel table, for example, download a product reference book from a price list or a Receipt Invoice from a supplier’s Invoice electronically sent by e-mail.

The need for automatic exchange is especially relevant when the number of elements that need to be entered is tens, hundreds or even thousands, and manual entry of such a volume of data is quite labor-intensive work. Some need to load data once only when starting to work with the program, others need to enter large amounts of data constantly 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 order the corresponding processing from their own in-house programmers, franchisees or third-party developers, but this is an additional expense and time.

What if for various reasons this is not possible?

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

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

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".

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

The processing capabilities are very large and it is not possible to cover all possible use cases within the framework of one article. Let's consider only the most common and popular examples:

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

Price list in Excel “PriceOnlineKhimSnab.xls” - Microsoft Office Excel 97-2003 (.xls)
Typical configuration Trade management Rev.10.3, release 10.3.10.4, platform 8.2 (8.2.10.77)
It is necessary to load into 1C from Excel not only the names of goods and prices as in Example 1, but also article numbers and barcodes, and it is necessary that the item is loaded not into one catalog (group), but distributed by manufacturer in the 1C item directory.

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

We use standard processing “Data Loading From Tabular Document.epf” which goes to the ITS.

1. Launch the 1C program

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

3. In the window that opens, 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 the data download will be carried out in three 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). - loading barcodes

Set “Download Mode” - Download to the directory

“Type of directory” - Nomenclature

5. In the “Tabular document” tab, click the “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.

8. Configure title loading parameters

For convenience, let’s set “Manual column numbering”

At this stage, we upload only those details that will allow us to start working with the program, so we uncheck the boxes that do not interest us yet

“First line of data in a spreadsheet document” - look at our price list, discard the header, the data for loading starts from the ninth line - put “9” (see Fig. 2 “Price list”)

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

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

Line “Parent” - in the “Loading mode” column, select “Search”, in the column “No. Columns of the spreadsheet document” - in our price list of manufacturers in the fourth column - put “4”.

If there is no need for distribution by manufacturer and all new items need to be loaded into one folder, then in the “Load mode” column, select “Install”, in the “Default value” column, select the item group into which we will load data from the price list.

Line “Article” - put a checkmark in the “Search field” column (second column) - the program will check for the presence of an element with such an article in the 1C database, and if one already exists, then a new element with this name will not be created. We set the Loading mode to “Search”, “Column No. of the spreadsheet document” - in our price list the article number in the second column is set to “2”

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

The “Weight” line - in our case, the product is NOT weighted, so “Loading mode” select “Install”, in the “Default value” column select “No”

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

Line “Item type” - in the “Load mode” column, select “Install”, in the “Default value” column, select “Product”.

We have set the minimum values for new items in the nomenclature directory, which will allow you to start working.

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

Click “Download” in the lower right corner, click “Yes” when asked

8. We check for the presence of new elements in the corresponding groups of the nomenclature directory.

At the moment, we have loaded product names from the price list in Excel into the 1C nomenclature directory.

As you can see, the details “Unit for reporting” and “Unit for storing balances” were left blank.

The next step is to set these details, because... you will need them when loading prices, barcodes and further work.

There are two options:

Manual.

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

Disadvantage: the manual procedure of opening and recording can be quite labor-intensive and time-consuming if there are a large number of directory elements.

Auto.

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

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

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

Option 1. Identification by Article

Place a checkmark in the “Do not create new elements” field and uncheck all lines.

Line “Article” - check the box in the “Search field” column (second column). We set the Loading mode to “Search”, “Column No. of the spreadsheet document” - in our price list the article number in the second column is set to “2”

In the lines “Unit for reporting” and “Unit for storing balances” in the “Load mode” field, select “Calculate”. In the “Expression” field we write the algorithm in the built-in 1C programming language. For example, our specialist from the implementation department suggested the following code in a couple of minutes:

Nomenclature Link = Directories.Nomenclature.Find By Details("Article", CurrentData["Article"]); Basic Unit of Measurement = Directories.Classifier of Units of Measurement.Find By Code(796); TechSpr = Directories.Units of Measurement.Find By Attributes("Unit By Classifier", Basic Unit of Measurement, Nomenclature Reference); If CurrentRef.Empty() Then FoundUnitObject = Directories.Units.CreateElement(); FoundUnitObject.Name = AbbrLP(BasicUnit); FoundUnitObject.UnitByClassifier = BasicUnit; FoundUnitObject.Coefficient = 1; FoundItemObject.Owner = ItemRef; Attempt FoundUnitObject.Write(); Result = FoundUnitObject.Ref; Exception Report("Failed to write"); EndAttempt; Otherwise Result = CurrentRef.Link; endIf;
// Company "Online" 2010, www.site

If you plan to repeat this procedure many times, then you must additionally add a verification procedure to the algorithm to prevent repeated entry of units of measurement with the same name.

Option 2. In case of identification by Name

Put a tick in the “Do not create new elements” field

Uncheck all lines

Line “Name” - check the box in the “Search field” column (second column). We set the Loading Mode to “Search”, “Column No. of a spreadsheet document” - in our price list the names in the third column are set to “3”

Nomenclature Link = Directories.Nomenclature.FindByName(CurrentData["Name"]); Basic Unit of Measurement = Directories.Classifier of Units of Measurement.Find By Code(796); TechSpr = Directories.Units of Measurement.Find By Attributes("Unit By Classifier", Basic Unit of Measurement, Nomenclature Reference); If CurrentRef.Empty() Then FoundUnitObject = Directories.Units.CreateElement(); FoundUnitObject.Name = AbbrLP(BasicUnit); FoundUnitObject.UnitByClassifier = BasicUnit; FoundUnitObject.Coefficient = 1; FoundItemObject.Owner = ItemRef; Attempt FoundUnitObject.Write(); Result = FoundUnitObject.Ref; Exception Report("Failed to write"); EndAttempt; Otherwise Result = CurrentRef.Link; endIf;
//© Online Company 2010, www.site

9. Open the product card, 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 “Save Settings” button and clearly name the current setting, for example, “Loading product names from the Excel price list.”

In “Trade Management” Rev. 10.3, prices are set using the document “Setting Item Prices”.

We use the same processing “Loading Data from Tabular Document.epf” from the ITS disk.

11. Set “Loading mode” - “Loading into the table section”.

The document journal “Setting item prices” opens.

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”.

13. Please note that now you need to go to the “Tabular document” tab and again select our price list in Excel from which we loaded the names, and now we will load the prices (see point 5).

14. Configure boot options

Again, set “First row of spreadsheet document data” - as when loading names, set “9” and “Manual column numbering”.

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

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

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

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

In the “Price type” line, set the Loading mode to “Install”, select the price that we will load “Retail”.

If in your price list the prices of goods are given in different currencies, 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”.

In the “Price calculation method” line, set the Loading mode to “Install”, select “By percentage markup on the base type”

Click “Download” and answer “Yes”

15. Open the document “Setting item prices” into which the data was loaded (by clicking the button with 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 product reference book, check the “Prices” tab in the loaded items card, and click the “Reread current prices” button.

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

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

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 “Save Settings” button and clearly name the current setting, for example, “Download Retail Prices from 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.

We use the same processing “Loading Data from Tabular Document.epf” from the ITS disk.

19. Set “Load mode” - “Load to information register”.
"Register type" select "Barcodes".

20. Please note that now you need to go to the “Tabular document” tab and again select our price list in Excel from which we downloaded the names and prices, and now we will download barcodes (see point 5).

21. Configure boot options

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

Setting up boot options

We remove “daws” from all lines from which it is possible.

In the remaining lines we set the parameters

In the “Barcode” line we set the Loading mode to “Search”, in the column “No. Columns of the spreadsheet document” we write “9” as in our price list.

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

If the price list does not have a column with articles, then in the “Search by” column you must select “Name”, and in the cell “No. Columns of the spreadsheet document” indicate the number of the price list column with names.

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

Nomenclature Link = Directories.Nomenclature.FindByName(CurrentData["Owner"]); Result=Nomenclature Link.Remaining Storage Unit;

In the “Quality” line in the “Boot mode” column, select “Install”, in the “Default value” column, select “New”.

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

Click “Download”

Open the product card and check for the presence of a barcode.

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 “Save settings” button and clearly name the current setting, for example, “Download product barcodes 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.

We wish you success!

Price list in Excel “PriceOnlineKhimSnab.xls” - Microsoft Office Excel 97-2003 (.xls)
Typical configuration Trade management Rev.10.3, release 10.3.10.4, platform 8.2 (8.2.10.77)
It is necessary to load into 1C from Excel not only the names of goods and prices as in Example 1, but also article numbers and barcodes, and it is necessary that the item is loaded not into one catalog (group), but distributed by manufacturer in the 1C item directory.

We use standard processing “Data Loading From Tabular Document.epf” which goes to the ITS.

1. Launch the 1C program

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

3. In the window that opens, 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 the data download will be carried out in three 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). - loading barcodes

Set “Download Mode” - Download to the directory

“Directory view” - Nomenclature

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.

8. Configure title loading parameters

For convenience, let’s set “Manual column numbering”

At this stage, we upload only those details that will allow us to start working with the program, so we uncheck the boxes that do not interest us yet

“First line of data in a spreadsheet document” - look at our price list, discard the header, the data for loading starts from the ninth line - put “9” (see Fig. 2 “Price list”)

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

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

If there is no need for distribution by manufacturer and all new items need to be loaded into one folder, then in the “Load mode” column, select “Install”, in the “Default value” column, select the item group into which we will load data from the price list.

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

The “Weight” line - in our case, the product is NOT weighted, so “Loading mode” select “Install”, in the “Default value” column select “No”

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

Line “Item type” - in the “Load mode” column, select “Install”, in the “Default value” column, select “Product”.

We have set the minimum values for new items in the nomenclature directory, which will allow you to start working.

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

Click “Download” in the lower right corner, click “Yes” when asked

8. We check for the presence of new elements in the corresponding groups of the nomenclature directory.

At the moment, we have loaded product names from the price list in Excel into the 1C nomenclature directory.

As you can see, the details “Unit for reporting” and “Unit for storing balances” were left blank.

The next step is to set these details, because... you will need them when loading prices, barcodes and further work.

There are two options:

Manual.

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

Disadvantage: the manual procedure of opening and recording can be quite labor-intensive and time-consuming if there are a large number of directory elements.

Auto.

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

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

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

Option 1. Identification by Article

Place a checkmark in the “Do not create new elements” field and uncheck all lines.

Nomenclature Link = Directories.Nomenclature.Find By Details("Article", CurrentData["Article"]); Basic Unit of Measurement = Directories.Classifier of Units of Measurement.Find By Code(796); TechSpr = Directories.Units of Measurement.Find By Details("Unit By Classifier, Basic Unit of Measurement, Nomenclature Link); If TechRef.Empty() Then FoundUnitObject = Directories.Units of Measurement.CreateElement(); FoundUnitObject.Name = Abbreviated LP(BasicUnitofMeasurement); FoundUnit Object.UnitByClassifier = BaseUnit;FoundUnitObject. Coefficient = 1; FoundUnitObject.Owner = Nomenclature Link; Attempt FoundUnitObject.Write(); Result = FoundUnitObject.Reference; Exception Report("Failed to write"); EndAttempt; Otherwise Result = CurrentRef.Link; EndIf;

If you plan to repeat this procedure many times, then you must additionally add a verification procedure to the algorithm to prevent repeated entry of units of measurement with the same name.

Option 2. In case of identification by Name

Put a tick in the “Do not create new elements” field

Uncheck all lines

Line “Name” - check the box in the “Search field” column (second column). We set the Loading mode to “Search”, “Column No. of the spreadsheet document” - in our price list the names in the third column are set to “3”

Nomenclature Link = Directories.Nomenclature.FindByName(CurrentData["Name"]);
Basic Unit of Measurement = Directories.Classifier of Units of Measurement.Find By Code(796);
TechSpr=Directories.Units of Measurement.Find By Requisites("
Unit By Classifier", Basic Unit of Measurement, Nomenclature Reference);
If CurrentRef.Empty() Then FoundUnitObject = Directories.Units.CreateElement();
FoundUnitObject.Name = AbbrLP(BasicUnit);
FoundUnitObject.UnitByClassifier = BasicUnit;
FoundUnitObject.Coefficient = 1;
FoundItemObject.Owner = ItemRef;
Attempt FoundUnitObject.Write();
Result = FoundUnitObject.Ref;
Exception Report("Failed to write");
EndAttempt;
Otherwise Result = CurrentRef.Link;
endIf;

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

9. Open the product card, 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 “Save Settings” button and clearly name the current setting, for example, “Loading product names from the Excel price list.”

In “Trade Management” Rev. 10.3, prices are set using the document “Setting Item Prices”.

We use the same processing “Loading Data from Tabular Document.epf” from the ITS disk.

11. Set “Loading mode” - “Loading into the table section”.

The document journal “Setting item prices” opens.

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”.

13. Please note that now you need to go to the “Tabular document” tab and again select our price list in Excel from which we loaded the names, and now we will load the prices (see point 5).

14. Configure boot options

Again, set “First row of spreadsheet document data” - as when loading names, set “9” and “Manual column numbering”.

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

In the “Price type” line, set the Loading mode to “Install”, select the price that we will load “Retail”.

If in your price list the prices of goods are given in different currencies, 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”.

In the “Price calculation method” line, set the Loading mode to “Install”, select “By percentage markup on the base type”

Click “Download” and answer “Yes”

15. Open the document “Setting item prices” into which the data was loaded (by clicking the button with 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 product reference book, check the “Prices” tab in the loaded items card, and click the “Reread current prices” button.

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

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 “Save Settings” button and clearly name the current setting, for example, “Download Retail Prices from 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.

We use the same processing “Loading Data from Tabular Document.epf” from the ITS disk.

19. Set “Load mode” - “Load to information register”.
"Register type" select "Barcodes".

21. Configure boot options

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

Setting up boot options

We remove “daws” from all lines from which it is possible.

In the remaining lines we set the parameters

In the “Barcode” line we set the Loading mode to “Search”, in the column “No. Columns of the spreadsheet document” we write “9” as in our price list.

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

Nomenclature Link = Directories.Nomenclature.FindByName(CurrentData["Owner"]); Result=Nomenclature Link.Remaining Storage Unit;

In the “Quality” line in the “Boot mode” column, select “Install”, in the “Default value” column, select “New”.

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

Click “Download”

Open the product card and check for the presence of a barcode.

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 “Save settings” button and clearly name the current setting, for example, “Download product barcodes 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.

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:

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”.

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