Specify data types for Fixed Width files

etluser

I have a need in a DataFlow to import a fixed-width TXT file. Can I enter the data type details using into the “FixedWidthColumns” attribute (and if so, what would the format be)?

andyvidan

There is a Fixed Width Format input module you can use, as follows:

You can drag/drop these input modules from the module library on the left, or right click on the green dot of the file streamer input to find it.

You can have as many of these as needed, order does matter. The inputs have the following structure:

Format – (Can be blank, or specified)

Name – Column name

Width - # of chars

You can also generate the column width inputs based on a table. This can be an easier approach if you have a ton of columns and don’t want to use individual input modules as shown above.

Assuming you have a table with the required information (e.g., column name, width, format), read that into a Code module with the following code:

(Note, this will need to be modified based on the input table)

using System;
using System.Linq;
using System.Collections.Generic;
using CompAnalytics.Contracts.Tables;
using CompAnalytics.Extension.SpreadSheet.Contracts;
using CompAnalytics.Contracts.Isolation;


namespace Example
{
    public class ExampleClass
    {
        public List<FixedWidthColumn> ExampleMethod(Table table)
        {
			List<FixedWidthColumn> columns = new List<FixedWidthColumn>();
            
            ITableOperations ops = ExecutionOperations.Current.FindOperations<ITableOperations>();
            using(ITableReader reader = ops.CreateTableReader(table))
            {
                foreach(TableRow row in reader) 
                {
                    if (row.Count == 8)
                    {
                    columns.Add(new FixedWidthColumn { Format = Convert.ToString(row["col7"]), Name = Convert.ToString(row["col6"]), Width = Convert.ToInt32(row["col3"])});
                    }
                    else columns.Add(new FixedWidthColumn { Format = Convert.ToString(""), Name = Convert.ToString(row["col6"]), Width = Convert.ToInt32(row["col3"])});
                }
            }
            return columns;
        }
    }
}