Skip to main content

SELECT.CSV

Zoltán Mikolás
  • Updated

 

Selects content from a source of text data in the form of CSV, TXT, TAB or any other separated text.

Properties

From Name of the Input Data Source.The input-source can contain either a file-path, a PBinaryStream object or a .Net Stream object. A Stream object can only be specified programmatically.
The format of the source must be a text-file with data and possibly header separated by defined characters etc.
To Name of the Output Data Source.
The output-source will always be a neutral Table-format.

Parameters

@CONTEXT

Specifies in which context to find data in the document.
The context is specified as follows: 'Header/Separator/Qualifier/NumberFormat/DateFormat'

  • Header
    1 with header (default)
    0 no header
  • Separator
    Any character that separates columns (comma, semicolon or tab)
  • Qualifier
    Any character used to encapsulate text, e.g. such that the text can contain characters that would otherwise separate it (typically single quote ' or double quote ")
  • NumberFormat
    , comma will be used as decimal separator (no 1000 separators are used in CSV)
    . period will be used as decimal separator (no 1000 separators are used in CSV)
  • DateFormat
    d-M-yyyy

Shortcuts
In addition to the above complete specification, the following shortcuts are available instead of specifying Number and Date formats.
us set all above to what they typically are in the USA
da set all above to what they typically are in Denmark (like most of Europe)
Example: da or 0/da
@ColumnsType The data type of the column is detected automatically, but from Perfion 2022-R2-SR1 it is possible to specify the data type by adding S, N, D or A (String, Number, Date, Automatically) via the parameter @ColumnsType separated by / like S/D/N/A which will enforce the first column as a string, the secund as Date and so on.
@Encoding Set the file encoding. If it is not set, it will use the Byte Order Mark (BOM) and without BOM it will use the default encoding for the Windows implementation. It is possible to set encoding to: UTF-8, UTF-16 (Unicode), utf-16BE (Unicode Big-Endian), windows-1252 and other Code Page Names from https://learn.microsoft.com/en-us/windows/win32/intl/code-page-identifiers (From Perfion 2023R1)
@FILTER Filter the result of the selected data. See Command Maps & Parameters for more information.
@ORDER Order the result of the selected data. See Command Maps & Parameters for more information.

Map

From Name of Header, as it is named in the file.
If * is specified this will take all columns, that are not specified by any of the other map entries. If no headers are in the file, they are named Col1, Col2, Col3 ….
If you leave "From" empty (No value) you can assign a default value to the feature in "To" by entering e.g. Value="NEW"; in the "Script" field.
To Name of the Header as it should be called in the Output table
Script Any Script code to execute for this header

Example

Above example will read below file with no headers, semi-colon separated, qualified with ", numbers with comma as decimal separator and dates formatted as date-month-year.

Related articles

Comments

0 comments

Please sign in to leave a comment.