Specify data types for Fixed Width files

[data-usr]

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 the Code module and C#, this can be implemented as follows. But you can use any of the other Code modules (e.g., Python Code, etc.).

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;
        }
    }
}