TM1 Forum

Hi,

I'm trying to create a TI Process that imports data from a text file that is fixed width delimited. When I tried to set the columns, I noticed the interface is rather buggy. E.g. the data will be duplicated across multiple columns as I dragged the lines across to try to define the starting point of the columns.

I even punch in a series of "1234567890" at the header row so that i know exactly where to start/end the columns when I create them at the interface. After this is done, the preview seems to show that it is working correctly. However, when I ran the process, errors appear, such as:

"00100-000-000-00-000-000000-10","010-0000-01-000,,,,Share Capital,,,,"99,999,999.","22",","",Data Source line (17) Error: Data procedure line (0): Cannot convert field number 3, value "22"," to a real number.

The actual raw data looks like this:
00100-000-000-00-000-000000-10 Share Capital 99,999,999.22

Does it have anything to do with the number delimiters that i defined? the decimal separator is "." and thousand separator is ",".

Hi
I found it quite hard to get the 'Fixed Width' working as well and what I have done in the past to get around this is to either

- set the fixed width to very very wide to capture the whole row
- set the resulting (one) variable to text
- use SUBST function to extract the needed parts, as the fields are always in a fixed part of that 'large string'
- transform into numbers if needed

or

- use the 'Delimited' option and use a dellimiter which definitely is not in the source like '*' (asterix)
- rest same as above

Basically part of the code is then

Variable1=SUBST(V1, 1, 10);
Variable2=SUBST(V1, 11, 10);

V1 being the source variable.
etc.


I know this is a bit clunky but at least it does not fail and is sometimes easier than to convince the provider of the file to create a delimited file.

Cheers