The 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 can i apply
For a specialist, the exchange of data between two systems is complicated in the sense that you need to understand your work and in an unfamiliar environment. If you need data from 1C, but studying 1C is not a promising direction for you, as an option I suggest a method of obtaining 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 any format;
- switch from 1C to another accounting system (data migration);
- direct data flow to the database for analysis (OLAP);
- have an archived copy of the data in clear text.
I want to say right away about the disadvantage of this method. In 1C, a lot of data upload processing has been written for different purposes. The use of 1C processing can be called the most official way of obtaining data. Typical processing not only unloads data from the database, but also conveniently converts and compiles them through connections in queries and by performing calculations in an embedded language. When uploading data as described in the article, you get only the primary tables stored in the database, and further processing may be required.
More about the result
What do we get?
Through an external connection, you can upload data to a file of any format or transfer the received records to another database without saving the intermediate file at all. Here, uploading to an xml file is done as a demonstration of the possibility of uploading itself.
Data is extracted from the 1C database and placed in one xml-file. Here is an example of the resulting file.
What data is uploaded
Numeric and string types are stored in the uploaded 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 “Counterparty” attribute with the value “0cc56776-0daa-11e3-bf95-f46d04eec7f5” is indicated in the invoice. In the counterparties directory, under this link we can find “Trading House”.
Unloading is performed without relying on any specific configuration and data structure. By enumerating metadata, all directories and all documents are uploaded. For each directory item and for each document, all attributes are unloaded. If the document has tabular parts, all rows are unloaded for each tabular part.
In the example, only the main types of tables are unloaded: directories, documents and tabular parts of documents. Unloading of constants, information registers and tabular parts of directories is not implemented. Unloading of accumulation registers and accounting registers is not performed, because this data can be obtained from primary documents. But for analysis purposes, it might be more convenient to unload register entries.
How data is received
The launch of the program should be performed with installed 1C, because it is connected through a COM connection. The connection string in the example is created for the file version, but if necessary, it can be redirected to connect to the 1C server. The user with the specified password must be registered in the database. The user must have permissions to read all data and the ability to establish an external connection.
Exclusive mode is not required. Thus, you can “unload” the database without interrupting the work of users.
Alternative methods
This method of data extraction is not the only one. You can even say that access through a COM connection is the slowest and the methods listed below are more productive. But there are fundamental disadvantages to these methods.
External or integrated processing
The same result as an xml file can be obtained from external or built-in 1C processing. The advantage of performing 1C processing in its speed. Still, this is an internal kitchen, and calls from a COM connection in terms of time costs are worth something.
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.
Procedure in a module available for external connection
The upload procedure can be placed in a common module that is available for external connection. The procedure itself should be declared as export.
Процедура ExportDatabase(Путь) Экспорт
Файл = Новый ЗаписьXML;
Файл.ОткрытьФайл(Путь);
Файл.ЗаписатьОбъявлениеXML();
Файл.ЗаписатьНачалоЭлемента("database");
...
Файл.ЗаписатьКонецЭлемента();
Файл.Закрыть();
КонецПроцедуры
Now it’s enough to refer to the unloading procedure from C #.
Connection.ExportDatabase(path);
The advantage of this method is quick execution. Implementing the upload procedure can efficiently use server calls and data exchange between the server and the client.
The main disadvantage is the need to make configuration changes to edit the common module.
Typical Tools
Many configurations have built-in upload handling. Many processes do not unload all the data, but limited by the recipient. From the trading base, only primary documents are downloaded to the accounting department for posting, and the items of the nomenclature directory, as well as prices and characteristics of 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 typical 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 1C database is located on the SQL server, in fact, the tables are open for reading. You can make requests and get all the data. The only problem is that the data structure is complex and requires very careful processing.
It is even more difficult to read from the file version of the 1C database. The structure of the 1CD file is not documented and, in principle, is subject to change. It should be noted here 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 class for establishing a connection to the 1C database and uploading data, as well as an example of the use of 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 types of directories and types of documents, metadata is accessed.
The data for each table is retrieved in separate queries. The documents also unload the tabular parts. For this, subqueries to tabular parts are formed with selection by reference to the uploaded document.
For specific tasks, you may have to make changes to the code. The generated xml file tag names (catalog, document, attribute, name, value) are chosen arbitrarily. If you make minor changes to the code, the structure of the resulting xml-file can be changed. You can also limit the data being uploaded to only certain tables and attributes using conditions in the metadata fetch cycles.
