Page tree
Skip to end of metadata
Go to start of metadata

(The following is an include of the English version of this page, Import text or CSV file 3.0)


Summary

This operator allows to import data from a text file where values are separated by comma (comma separated values, CSV) or another symbol.

It can only be applied to an empty data node.

The text file has to be available in the Data Management on the TIS Server. Therefore, a file from a local storage has to be uploaded to the TIS Server. See Upload or download a data file to the TIS Server for how to do this.


Import via Parameters

As of version 5.8 import files can be defined via Parameters.

Error Node

As of version 5.8 a specific error node can be defined. This way, error nodes can be displayed in TIS Board. The error node contains data rows that could not be properly interpreted.
Create an empty data node and choose the node as error node in the operation settings.

Example: Import text file of the type "Date/Time/Time"

Situation

The text file Import_1.txt is to be imported into a data node.

Step 1
Step 2

Create an empty data node. To this node, add the operation "Import Text or CSV file 3.0".

Click on the folder symbol next to "Name" and select the import file.

Alternatively, files can be defined via parameters under "Path (manual):"

 

Then choose the following settings:

Then click "Execute" to let TIS suggest the columns based on your suggestions. You will need to correct the suggested column "Bis" to Semantics: "Time (end)".

  • You get into the editing mode by clicking the icon Bearbeiten
  • You need to confirm changes by clicking the icon Übernehmen
  • Columns that are not supposed to be imported can be deleted with the icon Löschen
  • You can change the order of columns (how they will be displayed in the result table) with the icons Nach unten und Nach oben

As of version 5.8 a specific error node can optionally be defined. That gives the possibility that error nodes can be displayed in TIS Board.

Create an empty data node and choose the node as data node in the import operation.

Result

 

Further stepsThe resulting table should be converted to date+time/date+time to allow operations such as Scaling 7.0 
TIS ProjectConfluence Op Import Text or CSV file.gzip

Want to learn more?

 Settings

Imports data from a text file.

Parameter


ParameterValueOpt.DescriptionExample
@LOADCSVFILE

System.String

---
NameSystem.String-Name of the file, including path. Entered by browsing the folders in data management.Confluence\confluence-upload-CSV.txt
Path (manual)System.String-

Name of the file, including path. Entered manually and can be a parameter.


First lines / Last lines

-

-Information box showing the first and last lines of the import file defined above.

First row is a header rowSystem.Boolean-When checked, the data of the first row in the file is used as headers.

Headers will be "Datum", "Von", and "Bis"

Remove white space charactersSystem.Boolean-When checked, white space characters around the column separator will be removed.-
Column separatorSystem.String-Character that separates columns in the input file.-
Text delimiterSystem.String-Symbol enclosing text in the input file.-
Decimal symbolSystem.String-Symbol separating the integral part from the fractional part in decimal numbers (this is "," in German, but "." in English).-
Type of result tableSystem.String-Type of result table, see Types of Tables.-
Behavior in the case of invalid cell values – Apply default values, e.g. 0.0 with floating point values, if NULL should transferredSystem.Boolean

-

When checked, then default values are written instead of NULL, e.g. "0.0" in Double columns, "0" in Integer columns.-
Mapping--

Suggested mapping from the input file to the table.

Created after clicking "Execute" on the upper right.

Can be modified manually by using the symbols to the left.

Error output in nodeSystem.Stringopt.An empty data node to save rows that could not be imported.-
@MODE

System.Int32

---
@IMPORTFILE

System.String

---

 Examples

Example 2: Import file of type date+time/date+time

 

Situation

Read in the text file Import_2a.txt

First, save the file on your PC and then import it into TIS (see TIS:Datenmanagement (link to old wiki)).

Create an empty node in the TIS project and add operation "Import text or csv-file".

Operation setting

Click on the folder symbol next to "Name" and select the import file.

Alternatively, files can be defined via parameters under "Path (manual):"

Then, chose the following settings:

Click "Execute" to let TIS suggest the columns based on your suggestions. You will need to correct the suggested column "Bis" to Semantics: "Date+Time (end)".

Please note: Click on the button "Edit" to change settings. Click on the button "Apply" to confirm changes.

As of version 5.8 a specific error node can optionally be defined. That gives the possibility that error nodes can be displayed in [TIS]Board.

Define an empty data node and choose the node as data node in the import operation.

Result
TIS ProjectConfluence Op Import Text or CSV file.gzip

Example 3: Import file of type date+time/date+time with overlap to the next day

 

Situation

Read in the text file

Datum;Von;Bis
01.01.2007;06:00;14:30
02.01.2007;06:00;13:00
03.01.2007;14:00;23:00
04.01.2007;14:00;22:00
05.01.2007;22:00;06:00
06.01.2007;22:00;06:00
09.01.2007;06:00;14:00
10.01.2007;06:00;15:00
11.01.2007;06:00;25:00
12.01.2007;06:00;05:00+
13.01.2007;06:00;06:00+
14.01.2007;22:00;06*00

Please note: Times overlapping to the next day are indicated by different formats here: 25:00, 5:00+, and 6*00. This example will demonstrate how to read in each of these formats.

Operation setting

One solution for this is to apply the Formula operator (row-by-row) (4.0) = Formeloperator to create new columns which calculate the correct date and time:

Formulas are:

1. NextDay [Ja/Nein]: #Bis#.Substring(#Bis#.Length-1 > 0 ? #Bis#.Length - 1 : 0) == "+" || #Bis#.Contains("*")
2. Bis2 [Text]: #Bis#.Replace("+","").Replace("*",":")
3. h [ganze Zahl]: System.Convert.ToInt32(#Bis2#.Split(':')[0])
4. m [ganze Zahl]: System.Convert.ToInt32(#Bis2#.Split(':')[1])
5. Bis3 [DateTime]: #Datum#.AddDays(#Nextday# ? 1 : 0).AddHours(#h#).AddMinutes(#m#)
6. Von2 [DateTime]: #Datum#.Add(#Von#.TimeOfDay)
Result
TIS ProjectDateTimeImport.gzip
 Troubleshooting
ProblemFrequent CauseSolutions

Everything is suggested as Identifier although I wanted to read in Text.

 
  • This can be changed manually, but it is usually not necessary.
  • All operations which work with Text, also work with Identifier. (Identifier = special case of text)
  • If columns remain after conversion, and this is not wanted, simply delete those columns.

How can I make changes to the "Mapping" table?

 
  • You get into the editing mode by clicking the button Bearbeiten
  • You need to confirm changes by clicking the button Übernehmen
  • Columns that are not supposed to be imported can be deleted with the button Löschen
  • You can change the order of columns (how they will be displayed in the result table) with the buttons Nach unten und Nach oben

"Execute" is suggesting only 1 column with all column titles in "Mapping"

Commong error: Wrong column separator has been chosen (e.g., semicolon instead of tabulator)Check column separator, change it and click "execute"

The operation does not read in the decimal points (or not in every column)

  • Decimal symbol has not been selected correctly.
  • After clicking "Execute" the suggested columns have been applied without further checking. However, TIS interprets the values in the import table by reading the values in the first row. If the first row contains numbers without decimal numbers, TIS suggests Integer as data type. 
  • Check if the decimal symbol has been chosen correctly.
  • Check the data type for each column. For decimal numbers you need to select "Double" as data type.

 

24:00 is not imported correctly

24:00 is no allowed format, because in most software systems it means that it is already the next day.

1. Trick: Replacement of illegal values in combination with formula operator:

If the column that needs to be changed is named "Datum", then you can copy this formula:

#Datum#.AddSeconds(#Datum#.Second == 59 && #Datum#.Minute ==59 && #Datum#.Hour==23 ? 1 : 0)

A time overlapping to the next day is included (e.g., 25:00)

25:00 is no allowed format, because in most software systems it means that it is already the next day.

Possible solutions please see Example 3 above

Another trick: Use data type "Text" for time

Resulting in: