Import text or CSV file 3.0

System.String

-

-

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.

System.Int32

System.String

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".

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.

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.

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)

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
• You need to confirm changes by clicking the button
• Columns that are not supposed to be imported can be deleted with the button
• You can change the order of columns (how they will be displayed in the result table) with the buttons und

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

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

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

• State in the import operation that illegal values should be replaced by 23:59:59:
  
• Then import, with replacement of illegal values.
• Convert the table to a date+time/date+time format with the Operation Convert "From-Date/From-Time/To-Time"
• Then, apply the Formula operator (row-by-row) (4.0) = Formeloperator and change the corrected date (attention: don't forget to choose Date as the data type) with the following formula: 

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)

Possible solutions please see Example 3 above

Another trick: Use data type "Text" for time

Resulting in: