Hint: Importing tables is a frequent task. It should be as easy as possible. This first approach was too cumbersome and is now deprecated.
The most common way to organize data is columns and rows. In Topincs the main challenge is to get the data out of the source file, as the object-oriented model-driven Tobject API offers everything for persisting the data in the database. Topincs currently supports CSV and Excel as source formats. We use the term table for the data held in a csv file or Excel file. If you export an Excel sheet into a CSV file, both hold the same table.
A data import either happens once or multiple times. This makes no difference to the work you need to do. You need to tell Topincs which columns are relevant for the import. For each of these you need to choose a datatype and a formal name you use for coding. Then you need to define a service which simply iterates over all rows and uses the Topincs API to create or modify the data.
Santa Claus was busy and expanded his operation over the years to almost every country in the world. For this reason it is necessary to have all countries in the database, since this is mandatory information when a child records a wish. When the elves heard about this task, they happilly volunteered – all of them. You could feel the excitement in their quarters. But vivid debates about favorite countries quickly turned into conflicts about who may record it in the database. After all there is roughly 200 countries and legions of eager elves. Santa could no longer stand the resulting unrest and decided that IT should import a list of countries into the system.
The table in the source file needs to have a format. When the table is created by an export from an external computer system, this is usually constant and regular. When the table is managed by human employees, for example through Excel, this tends to vary over time, since it is simple to change and adjust the format for better support of the task the employee needs to perform. This includes adding, renaming, and abusing columns or putting relevant information into remarks. Topincs is aware of the resulting difficulties and tries to support the administrator as much as possible.
Hint: In many cases the source file ist uploaded by the user and the import is performed through the page menu.
Hint: For regular imports, the source file is fetched from a remote server, and after archiving it in the database, the import is scheduled.
The file holding the table, the source file, needs to be present in the database. Thus we create a new topic type, a subtype of Topincs file. Once this is done, we can defined the table format. The table format consists of one or more columns. For each column you need to specify:
Optionally you can specify the following.
string
. This is used for the conversion of the source information into the native PHP value. Invalid values become null
.null
.Now that you have a place for the source files in the database and defined the table format, you need to program a service to perform the actual import. This service takes a source file as an argument, iterates over the rows and creates new topics or modifies existing ones. It is a good idea to make an import repeatable. This means that no matter how often you run it, it will avoid to create duplicates, thus it is essential that you use stable subject identifiers.
Every valid row in the source file will be returned as a plain PHP object (stdClass
). The properties will hold the information of the source row according to your specification of the columns. The formal name is the name of the property and it will have a scalar PHP value (string
, int
, float
, bool
or a DateTime
). If the value of the column may be absent, you may need to test for null
. This is not necessary when you just pass it through to the Topincs API, since setting to null
does nothing or deletes any existing statements from the database.
Check it out
API reference
API reference
<?php
$file = $p->get("file");
foreach ($file->rows() as $row) {
Tobject::fetch_Country("si:country/" . $row->code)
->set_name($row->name)
->set_country_code($row->code);
}
Frequently subject identification for important topic types is required throughout the database in multiple source code locations. It is recommended to move these into functions in a dedicated file si.php
.
<?php
function si_country(string $code) : string {
return "si:country/" . $code;
}
function si_invoice(
string $vendor_id,
string $invoice_nr
) : string {
return sprintf(
"si:invoice/%s/%s",
$vendor_id,
$invoice_nr
);
}
This page cannot be displayed in your browser. Use Firefox, Opera, Safari, or Chrome instead.
Saving …