A way to extract data from 1C database

I want to share a way to upload data from 1C based on a COM connection. This option can be called an alternative to the use of standard and non-standard 1C treatments. For example, the C # language is taken, and the upload is performed into one large XML file.

Where to apply

For a specialist, the exchange of data between the two systems is difficult in that you need to understand both your work and the unfamiliar environment. If you need data from 1C, but studying 1C is not a promising direction for you, as an option I suggest a way to get data through an external connection without any visual opening of 1C.
You can try to apply this method in different tasks:

• upload to another database of arbitrary format;
• switch from 1C to another accounting system (data migration);
• send data flow to the database for analysis (OLAP);
• have an archive copy of the data in the clear.

Just want to say about the lack of this method. In 1C it is written a lot of processing upload data for different purposes. The use of 1C treatments can be called the most official way to obtain data. Typical processing not only unloads data from the database, but also conveniently converts and assembles them through connections in queries and by performing calculations in the embedded language. By downloading the data in the manner described in the article, you only get the primary tables stored in the database, and you may need to process them further.

More about the result

What will we get?

Through an external connection, you can upload data to a file of arbitrary format or transfer the resulting records to another database without saving the intermediate file at all. Here the upload to the xml file is made as a demonstration of the very possibility of uploading.
The data is extracted from the base 1C and placed in one xml-file. Here is an example of the resulting file.
')

<?xml version="1.0" encoding="utf-8"?> <database> <catalog name=""> <item> <attribute name="" value="0cc56775-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name="" value="AAAAAQAAAAA=" /> <attribute name="" value="false" /> <attribute name="" value="false" /> <attribute name="" value="000000001" /> <attribute name="" value=" " /> <attribute name="" value="1111111111" /> <attribute name="" value="222222222" /> </item> <item> <attribute name="" value="0cc56776-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name="" value="AAAAAwAAAAA=" /> <attribute name="" value="false" /> <attribute name="" value="false" /> <attribute name="" value="000000002" /> <attribute name="" value="" /> <attribute name="" value="3333333333" /> <attribute name="" value="444444444" /> </item> </catalog> <catalog name=""> <item> <attribute name="" value="0cc56777-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name="" value="AAAAAgAAAAA=" /> <attribute name="" value="false" /> <attribute name="" value="false" /> <attribute name="" value="000000001" /> <attribute name="" value="" /> <attribute name="" value="15000" /> </item> <item> <attribute name="" value="0cc56778-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name="" value="AAAAAwAAAAA=" /> <attribute name="" value="false" /> <attribute name="" value="false" /> <attribute name="" value="000000002" /> <attribute name="" value="" /> <attribute name="" value="25000" /> </item> </catalog> <document name=""> <attribute name="" value="0cc56779-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name="" value="AAAAAQAAAAA=" /> <attribute name="" value="false" /> <attribute name="" value="000000001" /> <attribute name="" value="2013-08-25T21:19:56" /> <attribute name="" value="true" /> <attribute name="" value="0cc56776-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name=""> <line> <attribute name="" value="0cc56779-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name="" value="1" /> <attribute name="" value="0cc56778-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name="" value="1" /> <attribute name="" value="25000" /> <attribute name="" value="25000" /> </line> </attribute> </document> <document name=""> <attribute name="" value="0cc5677a-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name="" value="AAAAAwAAAAA=" /> <attribute name="" value="false" /> <attribute name="" value="000000002" /> <attribute name="" value="2013-08-25T21:22:49" /> <attribute name="" value="true" /> <attribute name="" value="0cc56775-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name=""> <line> <attribute name="" value="0cc5677a-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name="" value="1" /> <attribute name="" value="0cc56778-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name="" value="1" /> <attribute name="" value="25000" /> <attribute name="" value="25000" /> </line> <line> <attribute name="" value="0cc5677a-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name="" value="2" /> <attribute name="" value="0cc56777-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name="" value="2" /> <attribute name="" value="15000" /> <attribute name="" value="30000" /> </line> </attribute> </document> <document name=""> <attribute name="" value="0cc5677b-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name="" value="AAAAAgAAAAA=" /> <attribute name="" value="false" /> <attribute name="" value="000000001" /> <attribute name="" value="2013-08-25T21:23:10" /> <attribute name="" value="true" /> <attribute name="" value="0cc56776-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name="" value=" .." /> <attribute name=""> <line> <attribute name="" value="0cc5677b-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name="" value="1" /> <attribute name="" value="0cc56777-0daa-11e3-bf95-f46d04eec7f5" /> <attribute name="" value="15000" /> <attribute name="" value="15000" /> <attribute name="" value="15000" /> </line> </attribute> </document> </database> 

What data is uploaded

Numeric and string types are stored in the downloaded file in the usual string form. Link fields that are displayed in forms with a select button with three dots “...” are unloaded as GUID values. For example, the invoice contains the attribute "Counterparty" with the value "0cc56776-0daa-11e3-bf95-f46d04eec7f5". In the directory of contractors for this link we can find "Trading House".
Uploading is performed without any calculation for any particular configuration and data structure. By enumerating the metadata, all directories and all documents are unloaded. Every attribute of the directory and every document unloads all attributes. If the document has tabular parts, for each tabular part all lines are unloaded.
The example implements the unloading of only the main types of tables: directories, documents and tabular parts of documents. Unloading of constants, registers of information and table parts of directories is not implemented. Unloading of accumulation registers and accounting registers is not performed, because these data can be obtained from primary documents. But for the purposes of analysis, it might be more convenient to unload register entries.

How does the data get

Run the program should be performed with the installed 1C, because it is connected via a COM connection. The connection string in the example is created for the file variant, but if necessary, you can forward it to connect to the 1C server. The user with the specified password must be registered in the database. The user must have read access to all data and the ability to establish an external connection.
Single mode is not required. You can "unload" the database in this way without interrupting the work of users.

Alternative ways

This method of data extraction is not the only one. You can even say that access via a COM connection is the slowest and the methods listed below are more productive. But these methods have fundamental flaws.

External or embedded processing

The same result as an xml file can be obtained from external or embedded 1C processing. The advantage of performing 1C processing in its speed. Still, this is an internal kitchen, and calls from a COM connection are worth something in terms of time costs.
The disadvantage of using processing is that automation ends there. The user must manually open the processing form, specify the parameters, click the upload button.

The procedure in the module, available for external connection

The upload procedure can be placed in a common module accessible to an external connection. The procedure itself must be declared as export.

  ExportDatabase()   =  XML; .(); .XML(); .("database"); ... .(); .();  

Now, just refer to the procedure for unloading from C #.

  Connection.ExportDatabase(path); 

The advantage of this method in fast execution. The implementation of the upload procedure can effectively use server calls and data exchange between the server and the client.
The main disadvantage is the need to make configuration changes to edit a common module.

Typical means

In many configurations, there are built-in handling uploads. Many treatments do not upload all data, but are limited by the recipient. From the trade base, only primary documents are downloaded to the accounting department for generating transactions, and items of the nomenclature reference book, as well as prices and characteristics of the goods, are downloaded to the online store. Unloading the entire database is usually done to go to the next edition. If it is possible to use standard tools for a specific task, this will probably be the best solution.

Physical access to the database

There are still ways to access data from the outside, but they rely on knowledge of the database structure. If the base 1C is located on the SQL server, in fact the tables are open for reading. You can make requests and receive all the data. The only problem is that the data structure is complex and requires very careful processing.
Even more difficult to read from the file version of the database 1C. The structure of the 1CD file is not documented, and in principle is subject to change. Here it should be noted that for the purposes of access to information, you can convert from file mode to SQL and vice versa.

Full text of the program

I will give the C # code of the installation class for connecting to the 1C database and uploading data, as well as an example of using this class.

 using System; using System.Text; using System.Xml; namespace ConsoleApplication { class Export1C { dynamic Connection; public void Connect(string path, string user = "", string password = "") { dynamic connector = Activator.CreateInstance(Type.GetTypeFromProgID("V82.COMConnector")); string connectionString = "File=\"" + path + "\""; if (user != "") connectionString += ";Usr=\"" + user + "\""; if (password != "") connectionString += ";Pwd=\"" + password + "\""; Connection = connector.Connect(connectionString); } public void Export(string path) { XmlTextWriter xml = new XmlTextWriter(path, Encoding.UTF8); xml.Formatting = Formatting.Indented; xml.WriteStartDocument(); xml.WriteStartElement("database"); // Catalogs foreach (dynamic catalog in Connection.Metadata.Catalogs) { xml.WriteStartElement("catalog"); xml.WriteAttributeString("name", catalog.Name); dynamic query = Connection.NewObject("Query"); query.Text = "select * from catalog." + catalog.Name; dynamic items = query.Execute().Unload(); for (int i = 0; i < items.Count(); i++) { xml.WriteStartElement("item"); for (int j = 0; j < items.Columns.Count(); j++) { xml.WriteStartElement("attribute"); xml.WriteAttributeString("name", items.Columns.Get(j).Name); xml.WriteAttributeString("value", Connection.XMLString(items.Get(i).Get(j))); xml.WriteEndElement(); } xml.WriteEndElement(); } xml.WriteEndElement(); } // Documents foreach (dynamic document in Connection.Metadata.Documents) { dynamic query = Connection.NewObject("Query"); query.Text = "select * from document." + document.Name; dynamic table = query.Execute().Unload(); for (int i = 0; i < table.Count(); i++) { xml.WriteStartElement("document"); xml.WriteAttributeString("name", document.Name); dynamic docref = null; for (int j = 0; j < table.Columns.Count(); j++) { xml.WriteStartElement("attribute"); string field = table.Columns.Get(j).Name; xml.WriteAttributeString("name", field); dynamic tabular = document.TabularSections.Find(field); if (tabular == null) { xml.WriteAttributeString("value", Connection.XMLString(table.Get(i).Get(j))); if (field == "") docref = table.Get(i).Get(j); } else { dynamic subquery = Connection.NewObject("Query"); subquery.Text = "select * from document." + document.Name + "." + field + " as lines where lines.Ref=&Ref"; subquery.SetParameter("Ref", docref); dynamic lines = subquery.Execute().Unload(); for (int line = 0; line < lines.Count(); line++) { xml.WriteStartElement("line"); for (int col = 0; col < lines.Columns.Count(); col++) { xml.WriteStartElement("attribute"); xml.WriteAttributeString("name", lines.Columns.Get(col).Name); string value = Connection.XMLString(lines.Get(line).Get(col)); xml.WriteAttributeString("value", value); xml.WriteEndElement(); } xml.WriteEndElement(); } } xml.WriteEndElement(); } xml.WriteEndElement(); } } xml.WriteEndElement(); xml.WriteEndDocument(); xml.Close(); } } class Program { static void Main(string[] args) { Export1C export = new Export1C(); export.Connect("D:\\TestBase", "User", "pass"); export.Export("D:\\Export.xml"); } } } 

Export is divided into two blocks: unloading directories and unloading documents. To obtain the types of reference books and types of documents, reference is made to metadata.
The data for each table is retrieved by separate queries. According to the documents, the table parts are also unloaded. For this, subqueries to table parts are formed with selection by reference to the document being uploaded.
For specific tasks, you may have to make changes to the code. The generated tags names of the xml-file (catalog, document, attribute, name, value) are chosen arbitrarily. If you make small changes to the code, the structure of the resulting xml-file can be changed. You can also limit the data to be uploaded only to specific tables and attributes using conditions in metadata sampling cycles.