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.
Column | DataType | Position |
---|---|---|
ID | Integer | 0 |
Name | String | 4 |
Price | Decimal | 19 |
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.
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.
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.
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.