Module: Vertec CSV Exporter
Module for simple Python CSV export from Vertec, with integrated OCL and SQL
Product line
Standard
|Expert
Operating mode
CLOUD ABO
|ON-PREMISES
Modules
Services & CRM
Budget & Phases
Purchases
Resource Planning
Business Intelligence
Created: 01.12.2020
Machine translated
Updated: 09.04.2024
|
Replaced module (VertecCSVExporter.py). If a file_name is specified, the file is sent anew to the client, not saved directly on the filesystem. This makes it fully cloud-ready.
For easy creation of CSV files in Vertec we offer this module. It is registered like a normal script and can then be referenced in other scripts.
Scripting
Register as script
, designation VertecCSVExporterClasses.
The designation is free to choose, it simply must not contain spaces or special characters. In the examples below, this designation is used; if you choose a different designation, you will need to modify the examples accordingly.
The module implements the following classes:
- VertecCSVExporter: class which applies file handling and which allows to specify lists directly via OCL or SQL expression.
- OCLCSVRecord: Records that are appended to the CSVExporter and represent the rows that are being written.
VertecCSVExporter
The VertecCSVExporter has the following properties:
| Characteristic | description |
|---|---|
| encoding | Default: 'UTF-8' |
| field_delimiter | Default: ';' field separator |
| string_delimiter | Default: '”' string separator |
| only_first_line_for_strings | Default: True Specifies whether to write only the first row of multi-line values (e.g., address text). Excel, for example, cannot handle CRLF in CSV strings. |
| file_name | If a file name is specified, the CSV is sent directly to the client and can be saved there. |
| field_names_in_first_line | Default: True Writes the field names as the first row in the CSV. |
| row_delimiter | Default: '\n’ line separator. |
| add_row() | Rows of type OCLCSVRecord are appended to the exporter. |
| populate_rows(listexpression, sqlwhere, ocl_filter_expression) | Method that creates the rows with OCL or SQL. The list can also be filtered with OCL (useful if the expression is an SQL expression).
|
| export_rows() | Method that creates and returns the CSV file. If a file_name is specified (see above), it is sent directly to the client. |
OCLCSVRecord
The individual records, i.e. the rows that are written, are from the OCLCSVRecord class. The class has the following properties:
| Characteristic | description |
|---|---|
| key | Field name. The field names are written as the first row in the CSV by default, unless the field_names_in_first_line option in the exporter is set to False (see above) |
| default_value | Default value. You can specify a default value, which will be written if the field does not contain a value. If no default value is specified, None must be specified. |
| field_type | Field type. The treated field types are: – string. String handling, which actively takes care of the encoding when writing a file. – float: Number with two decimal places, decimal separator is the period. – floatlocale: Number with four decimal places, decimal separator according to local locale or according to replaceSeparatorBy (see description below). – date: Date, is written in the format Day.Month.Year. All other field types are simply interpreted as String: str(value). For example, int can also be used, but there is no special treatment for int. |
| ocl_expression | OCL: Optional. You can specify an OCL expression to calculate the field. |
The rows are returned as Unicode strings.
Examples
Variant 1: All calculated automatically, save no file
from VertecCSVExporterClasses import VertecCsvExporter
fields = [
('Project', None, 'string', 'code'),
('ProjectId', None, 'int', 'objid'),
('Currency', None, 'string', 'waehrung.asstring'),
('Betreffend', None, 'string', 'betreffend'),
]
exporter = VertecCsvExporter(fields)
exporter.populate_rows("projekt")
csv = exporter.export_rows()Variant 1 with SQL expression
exporter.populate_rows("Projekt", "CreationDateTime>='01.01.2020'", "self->orderby(code)")CSV now contains the CSV as a Unicode string and can be e.g. via vtcapp.sendfile() sent to the client.
Since this string could also be processed further, the encoding is not taken into account here. For example, if you want to have this string as UTF-8, you can simply .encode("UTF-8") shall be appended.
Option 2: All automatic, with sending the file to the client
Structure as above, but with a file name:
exporter.file_name = 'export.csv'
The exporter.export_rows() automatically sends the file to the client via vtcapp.sendfile()
.
The file is sent by default in UTF-8. If you want to have it in a different encoding, you can do so in exporter.encoding(e.g. exporter.encoding='Windows-1252').
Variant 3: Creating rows manually, setting all or part of fields via Python
The fields that contain OCL are still calculated automatically, but can also be overwritten by specifying the field name.
from VertecCSVExporterClasses import VertecCsvExporter
fields = [
('Project', None, 'string', 'code'),
('ProjectId', None, 'int', 'objid'),
('Currency', None, 'string', 'waehrung.asstring'),
('Betreffend', None, 'string', 'betreffend'),
('KomplizierteBerechnung', 0.0, 'float', ''),
]
exporter = VertecCsvExporter(fields)
for projekt in projekte:
if projekt.typ = irgendeine_bedingung:
row = exporter.add_row(projekt)
row.KomplizierteBerechnung = berechneter_wert
csv = exporter.export_rows()