Skip to main content

Text Files

The Text file provider in Data Sync is a read-only provider and this provider returns a single column __line that represents each line in the Text document.

You can then use Calculated or Dynamic Columns to extract the parts of the line string you need into new columns.

Calculated Columns includes the function SUBSTR which can be used to extract from the string based on positional index.

For example assuming a fixed-width input file like this below we can see we have 3 columns ID, Name and Price.

1   Product1       1234.45
2 Product2 7890.12
3 Product3 45.00

Extracting the Positional Indexes for each row we can see that the file can be described as follows.

ColumnDataTypePosition
IDInteger0
NameString4
PriceDecimal19

Connect

To connect in Data Sync you would start by opening teh connection window and expanding the Text Files folder. Then select the Text File provider.

You will want to browse for your text file by clicking onto the ellipsis (...) in the FileName field.

Once you have completed the fields click Connect to load the file into the datasource window.

Connect to TEXT File

Define Columns

You can then use either Calculated or Dynamic Columns by extracting part of the input string of __Line your interested in.

In our example below we are using the SUBSTR function with Calculated Columns to extract the ID, Name and Price into three separate columns.

ID Column Name Column Price Column

Preview the Results

The columns created can then be added to the schema map window and the results previewed by selecting Preview A from the schema map window.

TEXT File results

This allows you to check that the data is being returned as expected.

Properties

Authentication

Specifies network credentials when using Web URL to load the TEXT file via HTTP.

FileName

The FileName is the path and filename of the text file. If you only include the name, then Data Sync will use the current directory (the folder the Data Sync project is saved to).

This field also supports wildcards, so using *.txt would force Data Sync to load all text files in the directory.

This field also supports a web uri to load the file from a HTTP source e.g.http://yourserver/reportdata?format=txt. You would need to supply authentication settings if using a web uri.

TextEncoding

The Text Encoding scheme to use, UTF8 includes BOM

SearchInFolders

When using Wildcard filenames this specifies whether the search will include subfolders.

Line

This is each line from your file that is injected into your schema.

_SourceFileName

This is a special column that is injected into your schema and will return the filename of the TEXT document. This is especially useful when using wildcard filenames.