Universal import. Accounting for import operations in "1C: Enterprise" Cost of imported goods
Feedback (0)
Discussions (1)
Discussions (1)
We are engaged in store automation and use this solution.
I can say the following:
1. The module is equipped with a very wide functionality due to which you can perform almost any import.
2. Very prompt technical support, respond in just a few minutes.
3. Quickly modify the module functionality according to adequate offers.
Many thanks to the developers of the solution for their work and attitude towards customers, which you rarely see recently.
I recommend to use!
Dmitry Gerasimov, Thank you Dmitry, reviews are also rare, we really appreciate them when everything works and you won’t be questioned, but as soon as something is wrong, poems are ready to be written right away, so your opinion is very valuable to us, this is a sign that No wonder we are doing our job.
What's new
| 1.7.2 (08.04.2019) | A warning has been added about the need to update the faceted index during manual import. |
| 1.7.0 (07.04.2019) | Improved stability of regular background import run. |
| 1.6.2 (28.03.2019) | A notification function about the availability of module updates has been added. |
| 1.6.1 (22.03.2019) | Improvements to work with trade offers. |
| 1.6.0 (22.03.2019) | The flexibility to configure loading from XML files has been significantly improved: a binding has been added to the values \u200b\u200bof the selected node attributes, in addition to binding to the order of the nodes. |
| 1.5.10 (13.03.2019) | Added option "Load if empty" for the data of a separate field. Added option "Skip existing items" to load only products that are not in the catalog. |
| 1.5.8 (05.03.2019) | Reworked page with background import schedule and manual launch. |
| 1.5.6 (26.02.2019) | Fixed problems with launching import module agents via cron. The function of downloading images from third-party servers has been improved - now there are cases with a redirect when issuing images by the server. |
| 1.5.5 (19.02.2019) | The "Separator for multiple values" option can now be configured individually for individual fields in all formats. |
| 1.5.4 (15.02.2019) | Improvements in the logging mechanism. Minor fixes in the function of creating a hierarchy of partitions. |
| 1.5.3 (15.02.2019) | Added the ability to create a hierarchy of nested sections from multiple fields. |
| 1.5.2 (13.02.2019) | For XLSX format, multiple field separator option has been added. For XLSX and CSV, the option to ignore the first line is added. |
| 1.5.1 (12.02.2019) | Fixed a small error when creating new partitions. |
| 1.5.0 (11.02.2019) | The ability for each product to create an arbitrary number of new sections through multiple fields. |
| 1.4.1 (11.02.2019) | Error correction for the XLSX format. |
| 1.4.0 (05.02.2019) | Added the ability to import files from a third-party server via a direct link. To import from CSV, you can now specify your own plural separator. |
| 1.3.3 (18.01.2019) | Feedback form added. |
| 1.3.2 (10.12.2018) | XLSX format: Added the ability to get data from formulas. |
| 1.3.1 (06.12.2018) | - Added the ability to distribute access rights for user groups. - Supplemented with useful tech page options. support and settings. - Fixed bugs when importing from .xlsx files. |
| 1.3.0 (12.08.2018) | - Added import type - catalog in Yandex.Market format (YML). The list of parameters and handlers of data source fields has been expanded: options "HTML entities to characters", "Cut HTML tags" and "Cut special characters", various options for rounding values, the ability to change the field value using a given formula, image processing options at boot time, the ability to set the condition for loading positions (option "Exclusive values" and option "Required values"). - Added import of ads from avito.ru, irr.ru, realty.yandex.ru, cian.ru, sob.ru, youla.io, n1.ru, egent.ru, mirkvartir.ru, moyareklama.ru via the http API: //ads-api.ru/api |
| 1.2.1 (26.07.2018) | Added option "Default section for new positions". Added option "Apply information block settings for image processing". |
| 1.2.0 (24.07.2018) | Added import type - Excel spreadsheets (XLSX). Added the ability to create custom import types (for example, for import via the REST API). |
| 1.1.0 (11.07.2018) | A new type of import has been added - XML \u200b\u200bwith infoblock data. Found bugs fixed. |
Installation
Installation Instructions
For the module to work, an XMLReader library must be available on the server.
After installing the module, you need to configure import profiles for each variant of the files you import.
If, for example, you fill content sections, such as news or articles, you will have your own profile for each section. If you need to fill in the catalog, then there can be either one or three profiles: a profile for loading basic goods, a profile for loading trading offers and, possibly, a separate profile for loading the structure of product categories (although the structure can also be formed when loading goods).
Profile setup
The profile is configured in the ACRIT / Universal Import / Profile List section.
A profile consists of several tabs. Tabs are configured sequentially, one after another. After each transition between the tabs, profile information is saved. Thus, the new profile is saved after the first tab is configured, although it will only be possible to launch it after setting all the tabs.
On the "Basic Data" tab, the profile name, data source and destination of data loading are set. Without filling out these key fields of this tab, you will not be able to go to the next tabs. The source can be either a file on the current server of the site, or on the remote one. A specific information block is indicated as a destination.
On the "Field Binding" tab, you can configure the correspondence between the fields of the data source and the fields of the information block, as well as additional parameters regarding the data link between the source and destination. In the "Data compliance" block on the left, all available fields taken from the import source are shown. To the right of them, you need to select the parameter of the infoblock element into which the data from the source field will fall. For those source fields whose data you do not need to import, the option "Do not import" remains on the right. Images in the import source are indicated as file names. These names will be searched for at the address specified in the Image Path box.
The "Data Processing" tab contains additional options that allow you to configure the upload logic. For example, if you want the catalog to contain only those products that are in the price list, you need to select the "Delete" option for the "Items that are not in the file" field.
In the "Schedule and launch" tab, the parameters for regular launch of this profile are configured. For example, you can configure the import to start automatically once a day (it is understood that during this time the import source can update its data and then the latest information will be uploaded to the site). Technically, a regular launch is organized through the mechanism of the so-called agents, which by default are launched when a visitor enters the site. To download large files, it is more productive to configure the launch of agents through the cron server utility. This will make the launch of the import more stable and accurate in terms of time, as well as save visitors from the inconvenience associated with the launch of the import when the site page is opened.
Also on the same tab, you can immediately start the configured profile and check its operation. After the launch, the download progress will begin to be displayed (either in percentage or in a quantitative version, if the total amount of downloaded data is obviously impossible to obtain).
If you encounter any difficulties during installation or encounter an error, use the "Technical Support" page located next to the "Profile List" item in the administrative menu. There you can find additional reference information, as well as send a request to the tech service. module support.
This material will help to understand the order of posting of imported goods in “1C: Accounting 8.3”.
What is the TBG number in 1C?
The purchase of imported goods is regulated by the following legislative acts:
- Customs Code of the Eurasian Economic Union (until 01.01.2018 - Customs Code of the Customs Union);
- Federal Law No. 173-ФЗ dated 10.12.2003 “On Currency Regulation and Currency Control”;
- Tax code;
- Also, the accountant must understand the terminology of Incoterms 2010 / Incoterms 2010 - a set of rules and terms used in international trade.
The GDT form was approved by the decision of the Commission of the Customs Union of 05.20.2010 N 257. We will consider the values \u200b\u200bof some lines, to which the accountant first draws attention.
The declaration consists of the main and additional sheets. The main sheet contains information about one product and general data for the entire declaration. If there are more than one goods, fill out additional sheets. On one additional sheet, you can specify information about three products.
Declaration number - consists of three groups of digits separated by a slash. The first value is the customs code, the second is the date of submission of the declaration, the third is the serial number of the declaration.
- In column 1, the IM mark is placed upon import.
- Box 12 - the total customs value in rubles. It is equal to the value of column 45 of the main and additional sheets.
- Column 22 - the contract currency and the total value in this currency are indicated. Equals the value of column 42 of the main and additional sheets.
- Column 23 - indicate the exchange rate on the date of filing the declaration, if it is necessary to recalculate the customs value.
- Box 31 - the name of the imported goods and their characteristics.
- Box 42 - the price of the goods in currency.
- Column 45 - the customs value of one item of goods.
- Column 47 - calculation of payments (customs duty, customs duty, VAT on the import of goods).
How to properly distribute a gas turbine engine in 1C 8.3?
Example 1. We import goods from Poland. Euro exchange rate on the date of filing the declaration 68.2562. The following items on the TBG:
In our example, the customs value of the declaration is 341,281.00 rubles.
The amount of customs duty is equal to 2000 rubles.
The size of the customs duty is 10%, which means the amount of the duty is 34,128.10 rubles. It is subject to distribution among all nomenclature items.
The amount of VAT is calculated by the formula (cost of goods + customs duty + excise tax) x VAT rate (10% or 18%). If the goods are not subject to excise duty, they shall be considered equal to zero. In this case, VAT is equal to:
(341,281.00 + 34,128.10) * 18% \u003d 375,409.10 * 18% \u003d 67,573.64 rubles.
Filling GTE for import into 1C will require making certain tinctures in terms of program functionality and in reference books (more on this later).
To work with foreign suppliers, accounts 60.21 and 60.22 are used, on which the amounts are indicated in currency.
When capitalizing receipts on the account. 10 (41, 15) there is a translation of the cost in rubles.
In addition to the total accounting in accounting accounts, an off-balance sheet GTD account is used to account for goods in the context of different numbers of declarations.
Settlements with customs are displayed on account 76.5.
For the correct conversion of the currency into rubles, you must load their rates.
In the button reference Download exchange rates ... a form will open where you need to select the date range.
How to conduct a gas turbine engine in 1C 8.3?
Let's check the settings of the program and reference books for posting goods by GTE in 1C.
- Main -\u003e Settings -\u003e Functionality;
- Administration -\u003e Program Settings -\u003e Functionality.
On the bookmark Stocks must be set Imported goods.
Let's move on to the directories.
In the directory of counterparties we will establish a foreign supplier. The country of registration should be selected from the list of countries.
In chapter Contract For this provider, you must set the settlement in the contract currency. Most likely, settlements with suppliers are carried out in foreign currency, and by the time the goods were capitalized, the organization had already managed to open a foreign currency account. In the case of settlements with the counterparty in rubles, it is necessary to establish the sign “Payment” in rubles and use the current account in rubles for payment.
We will receive materials and goods, create them in the directory with the corresponding type of item. You can specify the GDT number and country in the directory, then when filling out the receipt documents, these data will be filled in automatically. If it is planned to constantly receive any type of cargo through GTE for different numbers of customs declarations, you can leave this field free, and fill in the number upon receipt of material values.
Among the counterparties, customs should be established to which the goods were delivered. The type of contract must be Other (not the Supplier), as settlements go through account 76.5
The reflection of imports in 1C begins with the capitalization of the goods nomenclature. It is very similar to the standard receipt of material assets, taking into account small features.
Menu Shopping -\u003e Receipts (acts, invoices).
Prices are in the currency of the contract, in this case in euros. We do not indicate VAT. Button Edit allows you to edit any attribute at once for all positions of the document, for example, it may be the customs declaration number or country of origin.
If we look at the transactions, then the prices in euros are automatically converted into the currency of regulated accounting, in our case, rubles.
By nomenclature Goods GTE off-balance sheet data appeared. Please note by item group Materials movements in the section of the gas turbine engine are not recorded.
Based on the receipt of goods, you can create several documents related to the receipt of imported goods. We will create GTE on import. If deliveries from the countries of the Eurasian Economic Union, then you should fill out Application for the import of goods. Moving goods may be useful, for example, when transferring goods from the customs warehouse to the organization’s warehouse. The document on reflection of additional expenses does not need special presentation.
When creating a GTE on a tab the main thing indicate the amount Customs fee and Customs fine (if available), you can also specify the setting for VAT - For settlements with customs, indicate account 76.05. You can also see the euro, which is used in the calculations.
On the GTD Sections tab, the amount of customs value in currency is filled in, and data on the commodity nomenclature is also transferred. After indicating the percentage of the duty, the amount of the duty and VAT will be calculated in rubles automatically, and will also be distributed across the product section.
GTD entries are generated for the amount of customs duties, fees, fines (if any) and VAT.
Different algorithms can be used to calculate the duty, it is not always a percentage of the cost. In 1C, you can specify the duty in one amount, and it will be distributed among the commodity items. If you want to adjust the distribution of the amounts among the goods, this can be done manually in the column Duty.
If you suddenly need to increase the cost of goods by other amounts, for example, transportation costs or broker services, then use the document Receipt of additional expenses (Menu Shopping -\u003e Receipt of additional expenses) Its filling does not differ from the usual (non-import) posting of goods.
We will reflect in 1C another import supply. The GDT number is different.
We can create a shopping book, it will reflect the amount of VAT.
With the further sale of goods to its customers, the organization must indicate the correct GTE number. We show the sale of goods with different numbers of diesel engine. We have a parish of 10 pcs. on first delivery and 5 pcs. - on the second. We ship to the buyer 12 pcs. When filling out the sales document, we must respectively show the product in two lines. Do not forget to specify the VAT rate.
To facilitate the entry, you can use the Fill -\u003e Add from receipt button, by which you can select the posting document, the product names and the GDT data will be filled in automatically.
We look at the postings of the posted document.
For the purposes of analysis, the detailed movement of imported goods can be seen through the turnover balance sheet on the GTD account.
In the settings, you must specify the appropriate parameters.
We will additionally consider some of the possibilities when designing a gas turbine engine.
A customs declaration can be formed on the basis of several documents for the receipt of material assets. In the tabular section Goods you can add another receipt document by selecting it from existing ones by clicking Fill. In addition, the tabular section allows you to add new sections. For educational purposes, we have added a new section, which is filled with data from the second receipt of imported goods.
The procedure for recording operations on foreign trade contracts * is of great complexity for an accountant. In the process of their accounting, it is necessary to observe many different norms and requirements of Russian legislation. In addition to the paperwork, the accountant has the task of correctly reflecting them in a computer program. In this article, E.V. Baryshnikova (consultant) is considering the procedure for reflecting import operations in the economic programs of 1C.
Fig. 1
Fig. 2
Fig. 3
- customs duty;
- customs duty;
When conducting the document generates postings:
- 44 "Cost of sales";
- 91 "Other income and expenses".
Reflection of import operations in "1C: Accounting 8"
In “1C: Accounting 8”, to carry out operations under an import contract and to correctly account for mutual settlements with the supplier, it is necessary to determine the terms of the contract in the “Contracts” directory (Fig. 1).
Fig. 1
In the field "Type of contract" must indicate "with the supplier"; choose the currency in which the contract is drawn up. The order of settlements with the counterparty depends on the configuration settings and is possible in two versions:
- according to the contract as a whole (upon closing the contract, the program itself will find the necessary settlement documents);
- according to settlement documents (when closing the contract, the user must independently indicate the settlement document).
To transfer funds under an import contract to the advance payment to the supplier, the document “Payment order outgoing” is used. On the toolbar of this document, by pressing the "Operation" button, the option "Payment to the supplier" is selected. Account 52 select, indicate "Bank account" (currency), which is the movement. Accounting accounts for calculations and advances to choose - 60.21 and 60.22 (see. Fig. 2).
Fig. 2
For the correct calculation of the ruble amount, it is necessary to timely fill in the information on exchange rates in the "Currencies" directory. If necessary, the user can edit the "Course" field, which reflects the current exchange rate on the date of the document.
In the "Currencies" directory, it is possible to automatically download exchange rates from the RBC server. To do this, use the "Download Courses" button on the document panel. In the processing dialog box that opens, specify the period for which you want to download courses. Using the "Selection" or "Fill" button, create a list of currencies for which you need to download rates. Downloading courses is done by clicking on the "Download" button. After downloading, information on exchange rates is automatically recorded in the information register for each currency.
When conducting the document "Payment order outgoing" generates a posting:
Debit 60.22 Credit 52 - the amount of the contract value of the delivery.
The formation of the value of acquired material assets may be reflected:
- using account 15 "Procurement and acquisition of material assets";
- without the use of account 15 "Procurement and acquisition of material assets", directly on accounts 10 "Materials" and 41 "Goods".
The procedure for the formation of the actual value of material assets should be fixed in the accounting policy of the enterprise.
The formation of the actual value of tangible assets using account 15 "Procurement and acquisition of tangible assets" is available to the user using manual operations.
In this article, we consider the scheme of accounting for actual value directly on the accounts of material assets.
As an example, consider the accounting of imported goods.
The receipt of goods from a foreign supplier is documented by the "Receipt of goods and services" (main menu Main activity - Purchase). On the document toolbar, on the "Operation" button, select the option - "Purchase, commission."
Use the "Price and Currency" button to clear the "Include VAT" flag (the price of the goods does not include the amount of tax, the tax is paid to the customs authorities).
On the tab "Products" fill in the tabular part of the document with information about the goods. In the tabular part of the document, it is also necessary to indicate the country of origin of the imported goods and the number of the cargo customs declaration (Fig. 3). To do this, you may need to adjust the visibility of the columns of the tabular part of the document. The visibility of certain columns of the tabular section is configured in a special window called “List Settings”, called up from the context menu of the tabular section of the document (opened by pressing the right mouse button, provided the cursor is over the tabular section - for more information on setting the visibility of columns, see the “Accounting Guide” ").
Fig. 3
When conducting the document generates postings:
Debit 41.01 Credit 60.21 - for the amount of the contract value; Debit 60.21 Credit 60.22 - for the amount of the credited advance payment; GTE debit (without correspondence) - by the amount of goods received (without amount).
In accordance with RAS 5/01, the initial value of tangible assets is formed taking into account the costs associated with their acquisition. When conducting foreign trade operations, the costs included in the price of goods include:
- customs duty;
- customs duty;
- other expenses (customs broker services, transportation services, etc.).
To reflect the information on customs duties and duties recorded in the cargo customs declaration, the document “Customs Declaration on Import” is used (main menu - Main activity - Purchase). This document may be “entered on the basis of” the document “Receipt of goods and services”. On the tab “Basic” the GTE number and the amount of customs duties are indicated, on the tab “GTE sections” information on material values \u200b\u200band amounts of customs payments is entered. When a document is posted, the following transactions are generated:
Debit 41.01 Credit 76.05 - for the amount of the customs duty; Debit 41.01 Credit 76.05 - in the amount of the customs duty; Debit 19.05 Credit 76.05 - in the amount of VAT.
To reflect other expenses that form the actual cost of material assets, it is necessary to use the document “Receipt of additional expenses” (main menu - Main activity - Purchase). This document has the ability to distribute the amount of additional costs in two ways:
- in proportion to the amount of goods ("by amounts");
- in proportion to the quantity of goods ("by quantity").
When conducting the document generates postings:
Debit 41.01 Credit 60.21 - for the amount of expenses; Debit 19.04 Credit 60.21 - for the amount of accrued VAT.
To reflect the costs associated with the acquisition, but not included in the value of tangible assets, the document “Receipt of goods and services” is used, which reflects the receipt of tangible assets. This document fills the tab "Services", which indicates information about the costs and determines the cost account to which these costs should be allocated. Expenses not included in the value of material assets may be recorded in the accounts:
- 44 "Cost of sales";
- 91 "Other income and expenses".
Thus, operations on receipt of material assets and on reflection of services not included in the price can be reflected in one document.
It should be noted that in practice it is often necessary to take into account imported goods during their delivery as material assets in transit. Since the program provides analytics for warehouses on material assets accounts, it is possible to create an additional “virtual warehouse” on material assets accounts (10 “Materials”, 41 “Goods”, etc.). To do this, add the item with an arbitrary name to the Warehouses directory (for example, “MC on the way” or others) and capitalize material assets in this warehouse. With the actual receipt of material values \u200b\u200bby the document "Movement of goods" (main menu Main activity - Warehouse operations), reflect the receipt of values \u200b\u200bat the enterprise’s warehouse.
Reflection of operations under an import contract in tax accounting occurs when documents are posted. In the configuration, the user is given the opportunity to independently determine the need for reflection in the tax accounting of a specific operation. For this, each document has a flag “Reflect in cash. Accounting”.
When the flag is set in the document, “duplicate” entries are generated according to the tax chart of accounts. The tax chart of accounts for the structure of accounts, analytics is similar to the chart of accounts for accounting to facilitate the comparison of accounting and tax data. Codes of accounts in most cases correspond to codes of accounts of accounting for a similar purpose.
To analyze the operations used a set of standard accounting reports.
Reflection of import operations in "1C: Accounting 7.7"
In the "1C: Accounting 7.7" configuration, for the correct settlement of settlements with a foreign supplier under a foreign trade contract, it is also necessary in the "Contracts" directory for the counterparty from whom the material assets are received to correctly determine the terms of the contract (see Fig. 4).
Fig. 4
Prices in the contract are set in the currency (USD, EURO), payment of the contract is also set in the currency.
Transfer of payment to the supplier for imported goods is reflected in the document "Statement" (currency). When posting, the document will generate postings:
Debit 60.22 Credit 52
Posting of imported goods (material) directly to material assets accounts - 41 "Goods" (10 "Materials") - is carried out using the document "Goods receipt" ("Goods receipt"). When conducting this document generates postings:
Debit 41.1 Credit 60.11 - for the amount of the contract value; Debit 60.11 Credit 60.22 - for the amount of the advance payment credited; Debit Н02.02.1 (without correspondence) - reflected the receipt of goods for tax accounting.
When filling out a document, special attention should be paid to the VAT accounting procedure.
To account for settlements with customs authorities, the following accounts are used:
- 76.5 "Settlements with debtors and creditors";
- 19.4 "VAT paid to customs authorities on imported goods."
Since the cost of the goods received from the supplier does not include the amount of tax, and the amount of tax is paid directly to the customs authorities, in the document “Goods receipt” (“Goods receipt”), the flag “Invoice” must be unchecked, and information about the amount of tax paid at customs, enter the document "Invoice received" (see. Fig. 5).
Fig. 5
In this document, on the “Corresponding accounts” tab, select a Debit account - 19.4 “VAT paid by customs.”, Credit account - 76.5 “Settlements with debtors and creditors”; on the tab "Imported goods" indicate the number of the cargo customs declaration and the quantity of goods received through it. When conducting the document generates postings:
Debit 19.4 Credit 76.5 - for the amount of tax paid at customs; GTE debit (without correspondence) - by the amount received on the declaration of material assets.
Accounting for additional costs associated with the acquisition of material assets from a foreign supplier is reflected in the configuration document "Services of third parties." To include the costs associated with the payment of customs duties and customs duties in the initial cost of acquired material assets, in the document "Services of third-party organizations" in the field "Document of receipt" it is necessary to indicate the document of receipt, which reflects the posting of imported goods. In this case, the costs indicated in the tabular part of the document "Services of third parties" will be included in the initial cost of the goods. In the field "Type of contractor" indicate - 76 "Other creditor" (settlements with customs are accounted for in account 76.5 "Settlements with debtors and creditors). When posting, the document generates the following entries:
Debit 41 “Goods” (10 “Materials”) Credit 76.5 - for the amount of additional expenses.
To account for expenses not included in the value of material assets, the document "Services of third parties" is also used. In this case, the field "Receipt document" remains blank. In the tabular part of the document in the field "Correspondent Account" you should indicate the cost account to which these costs should be allocated:
- 44 "Cost of sales";
- 91 "Other income and expenses".
8.1. Import Documents
In the process of importing documents from the BS in the system DVO BS-Client Converts BS text documents of certain formats into DB documents. The format in which documents imported from the BS "1C: Enterprise" should be submitted is described in app. A “Agreement on the exchange of information with BS" 1C: Enterprise "" . Parameters for converting text formats of imported documents are set on the bank side and are described in app. B "Text format for data exchange with the BS" .
Import b2B documents (cm. sec. 4.4 "B2B documents" ) from BS "1C: Enterprise" is implemented in XML format. B2B document import formats are given in the relevant sections of the documentation:
After conversion, soft controls are applied to the RB document (see section 4.1.3 “Document controls” . Based on the results of controls and depending on the import settings, the document is added to the list of outgoing documents with one of the following statuses:
"new" - assigned to the imported document that does not contain serious errors in the details (the document passed the test with hard-tuned controls, but could not pass the test with soft-tuned controls). The document with the status of "new" can be edited, deleted or signed and sent to the bank for processing.
"imported" - is assigned to the document imported from the BS if serious errors in the details were found in it (the document did not pass the check with hard-tuned controls). A document with the status "imported" can be deleted or edited by manually correcting errors. After correcting errors, the document with the status of "imported" is assigned the status of "new".
Imported documents with the statuses “new” and “imported” are displayed in the list of documents in the status “Imported” (see sec. 4.1.5 "Status of documents" ) If necessary, imported documents in the "new" status can be transferred to the "New" state by setting a mark on the successful completion of the import. This mark is set by selecting the required entries in the list of imported documents and pressing the button.
To import documents from the BS:
Select menu item Service → Import documents from BS .
The page will open.
Fig. 8.2. Page Import of documents from accounting systems
In field Import File Format select the format of the imported file.
In field Document type Select the type of document to import.
In field File set the path to the imported file using the standard Windows window that is called when the button is clicked Overview (Browse ).
Press button Import .
The system will start the import process. A window will appear Message to display the progress of the operation.
Fig. 8.3. Message window (operation execution)
If during the execution of the operation it is necessary to perform any other actions in the system:
When the operation is completed, the red arrow on the button will be hidden.
In the window Message A message will be displayed indicating that the import operation was successful.
Fig. 8.4. Message window (completion of operation)
If there is no need to view the import results, click OK and complete the instructions. If you want to view the import results, click View and go to the next section of the instructions.
The report page on the results of importing documents from the BS opens.
Fig. 8.5. Page of the report on the results of importing documents from the BS
The report contains information on the processing results of each of the documents presented in the import file.
To view payment orders created during the last import session, click View Documents .
As a result of these actions, the imported documents will be added to the list of outgoing documents.
If you are just starting your journey in exploring the possibilities of Python, your knowledge is still at an elementary level - this material is for you. In the article we will describe how you can extract information from data presented in Excel files, work with them using the basic functionality of libraries. In the first part of the article we will talk about installing the necessary libraries and setting up the environment. In the second part, we will provide an overview of libraries that can be used to load and write tables to files using Python and describe how to work with such libraries as pandas, openpyxl, xlrd, xlutils, pyexcel.
At some point, you will inevitably encounter the need to work with Excel data, and there is no guarantee that you will enjoy working with such data storage formats. Therefore, Python developers have implemented a convenient way to read, edit and perform other manipulations not only with Excel files, but also with files of other types.
Starting point - data availability
TRANSFER
Original article - www.datacamp.com/community/tutorials/python-excel-tutorial
Posted by Karlijn Willems
When you start a data analysis project, you often come across statistics collected, possibly with the help of counters, perhaps by uploading data from systems like Kaggle, Quandl, etc. But most of the data is still in Google or in the repositories. shared by other users. This data can be in Excel format or in a file with the .csv extension.
There is data, a lot of data. Analyze - I do not want. Where to begin? The first step in data analysis is to verify it. In other words, you need to make sure the quality of the incoming data.
If the data is stored in a table, it is necessary not only to confirm the quality of the data (you need to be sure that the data in the table will answer the question posed for the study), but also to evaluate whether this data can be trusted.
Table Quality Check
To check the quality of the table, usually use a simple checklist. Do the data in the table meet the following conditions:
- data are statistics;
- various types of data: time, calculation, result;
- the data is complete and consistent: the data structure in the table is systematic, and the formulas present are working.
Best Practices Tabular Data
- the first row of the table is reserved for the header, and the first column is used to identify the sampling unit;
- avoid names, values, or fields with spaces. Otherwise, each word will be interpreted as a separate variable, which will lead to errors related to the number of elements in the row in the data set. It is better to use underscores, case (the first letter of each section of the text is capitalized) or connective words;
- give preference to short names;
- try to avoid using names that contain the characters ?, $,%, ^, &, *, (,), -, #,?,<,>, /, |, \\, [,], (, and);
- delete any comments that you made in the file to avoid additional columns or fields with a value of NA;
- make sure that any missing values \u200b\u200bin the dataset are displayed as NA.
If you work with Microsoft Excel, you probably know that there are a large number of options for saving the file in addition to the default extensions: .xls or .xlsx (go to the “file” tab, “save as” and select another extension (the most commonly used extensions to save data for the purpose of analysis - .CSV and .ТХТ)). Depending on the saving option, the data fields will be separated by tabs or commas that make up the “delimiter” field. So, the data is checked and saved. We begin to prepare the workspace.
Workspace preparation
The preparation of the workspace is one of the first things that must be done to be sure of the qualitative result of the analysis.
The first step is to check the working directory.
When you work in the terminal, you can first go to the directory where your file is located, and then run Python. In this case, you need to make sure that the file is in the directory from which you want to work.
To check, give the following commands:
# Import `os` import os # Retrieve current working directory (` cwd`) cwd \u003d os.getcwd () cwd # Change directory os.chdir ("/ path / to / your / folder") # List all files and directories in current directory os.listdir (".")
These commands are important not only for loading data, but also for further analysis. So, you went through all the checks, you saved the data and prepared the workspace. Already start reading data in Python? :) Unfortunately, not yet. One last thing to do.
Installing packages for reading and writing Excel files
Despite the fact that you still do not know which libraries will be needed to import data, you need to make sure that everything is ready for the installation of these libraries. If you have installed Python 2\u003e \u003d 2.7.9 or Python 3\u003e \u003d 3.4, there is no reason for concern - usually, in these versions everything is already prepared. So just make sure you upgrade to the latest version :)
To do this, run the following command on your computer:
# For Linux / OS X pip install -U pip setuptools # For Windows python -m pip install -U pip setuptools
In case you have not installed pip yet, run the python get-pip.py script, which you can find (there are installation instructions and help there).
Install Anaconda
Installing the Anaconda Python distribution is an alternative if you use Python to analyze data. This is a simple and quick way to start working with data analysis - you don’t have to separately install the packages necessary for data science.
This is especially convenient for beginners, but even experienced developers often go this route, because Anakonda is a convenient way to quickly test some things without having to install each package separately.
Anaconda includes the 100 most popular Python, R, and Scala libraries for analyzing data in several open source development environments such as Jupyter and Spyder. If you want to get started with Jupyter Notebook, then here you are.
To install Anaconda - you are here.
Well, we have done everything to set up the environment! Now is the time to start importing files.
One of the ways you'll often use to import files for data analysis is to import using the Pandas library (Pandas is a Python program library for processing and analyzing data). Pandas works with data on top of the NumPy library, which is a lower-level tool. Pandas is a powerful and flexible library and it is very often used to structure data in order to facilitate analysis.
If you already have Pandas in Anaconda, you can simply upload the files to Pandas DataFrames using pd.Excelfile ():
# Import pandas import pandas as pd # Assign spreadsheet filename to `file` file \u003d" example.xlsx "# Load spreadsheet xl \u003d pd.ExcelFile (file) # Print the sheet names print (xl.sheet_names) # Load a sheet into a DataFrame by name: df1 df1 \u003d xl.parse ("Sheet1")
If you did not install Anaconda, just run pip install pandas to install the Pandas package in your environment, and then run the commands above.
To read .csv files there is a similar function for loading data into a DataFrame: read_csv (). Here is an example of how you can use this function:
# Import pandas import pandas as pd # Load csv df \u003d pd.read_csv ("example.csv")
The separator that this function will take into account is the default comma, but you can optionally specify an alternative separator. Go to the documentation if you want to know what other arguments you can specify in order to import.
How to write Pandas DataFrame to Excel file
Suppose, after analyzing the data, you want to write the data to a new file. There is a way to write Pandas DataFrames (using the to_excel function). But, before using this function, make sure that you have XlsxWriter installed if you want to write your data to several sheets in the .xlsx file:
# Install `XlsxWriter` pip install XlsxWriter # Specify a writer writer \u003d pd.ExcelWriter (" example.xlsx ", engine \u003d" xlsxwriter ") # Write your DataFrame to a file yourData.to_excel (writer," Sheet1 ") # Save the result writer.save ()
Notice that the code snippet uses an ExcelWriter to output a DataFrame. In other words, you pass the writer variable to to_excel (), and specify the name of the sheet. Thus, you add a data sheet to an existing workbook. You can also use ExcelWriter to save several different DataFrames in one workbook.
Like the functions that are used to read in .csv files, there is also the to_csv () function to write the results back to a comma-delimited file. It works the same as when we used it to read in a file:
# Write the DataFrame to csv df.to_csv ("example.csv")
If you want to have a separate file with a tab, you can pass a \\ t to the sep argument. Note that there are various other functions that you can use to output files. They can be found.
Using a virtual environment
A general tip for installing libraries is to install in a virtual Python environment without system libraries. You can use virtualenv to create Python sandboxes: it creates a folder containing everything you need to use the libraries that Python will need.
To get started with virtualenv, you first need to install it. Then go to the directory where the project will be located. Create virtualenv in this folder and load, if necessary, into a specific version of Python. After that, activate the virtual environment. Now you can start loading other libraries and start working with them.
Remember to turn off the environment when you are done!
# Install virtualenv $ pip install virtualenv # Go to the folder of your project $ cd my_folder # Create a virtual environment `venv` $ virtualenv venv # Indicate the Python interpreter to use for` venv` $ virtualenv -p / usr / bin / python2 .7 venv # Activate `venv` $ source venv / bin / activate # Deactivate` venv` $ deactivate
Please note that a virtual environment may seem problematic at first if you take the first steps in data analysis using Python. And especially if you have only one project, you may not understand why a virtual environment is needed at all. Education Add tags
Loading...