TM1 Forum

Hi All,

I have a raw file (A.CSV) and want to pull out with 12 values in a single record as output in a file (B.CSV)

A.csv file Output file format (B.CSV) looking to process this using TI Process.
Please share your ideas.

Regards,
Deepak Jain

deepakjain2020 wrote: I have a raw file (A.CSV) and want to pull out with 12 values in a single record as output in a file (B.CSV)

A.csv file

Output file format (B.CSV)

looking to process this using TI Process.
Please share your ideas.

So basically you want to take something that doesn't so much qualify for the term "data file" as it does a "stream of consciousness narrative", and convert it into something structured and delimited.

Good luck with that. My first suggestion would be to return to whoever is supplying the data and ask them to read the introductory section of any given web page on "How to output text data".

Failing that, IF you can discern any patterns at all in the source data, it would be possible for you to, for example:
(a) Declare a variable as an empty string in the Prolog;
(b) In the Data tab, keep reading each input record until you get a complete output record. For example if you know that the headings will have no more than n fields in them, and that each field will terminate with a semi-colon with subsequent commas being ignored, you could use that knowledge to read A;B; from the first row and ignore the rest since there are no further semi-colons, read C; from the second one (and so on) until you get to L (which would need to be coded as a special case since it has no semi-colons at all). With each row you append the relevant part of the content to the string variable and then, once you have it all, use an Ascii output function to write it.

You could use a combination of the Scan() and SubSt() functions to do this. Naturally the code in question will be wrapped inside an If() block so that it only executes until you finish working out the header row.

If you know that after you get through creating the heading row, everything will be data, you could use the same functions to split out all of the values between semi-colons, again ignoring the commas that appear after the last semi-colon, and pad out the extra fields with as many semi-colons as you need. Again this block of code would be inside an If() block, and only executed on records which appear after the header row has been written.

That's of course only IF you can make those kinds of predictions.

Hi Alan,

Thanks for your idea. I tried out my best with the knowledge.
Can you please help me in knowing in what way can proceed with append the same variable till the count is 12 in single row.
Do we need to use any kind of ASCII function which will use the ASCII value of backspace, to append same variable.
I am not aware of which function or way will help me to append the same variable from different rows.

Thanks in advance

Regards,
Deepak Jain

The Data tab of a process runs through all of the records: make use of that knowledge to concatenate (the | operator in TM1) to form the lines.
Then do an AsciiOutput to a text file. Please re-read what Alan wrote under (b) in his post above.

Back in the days when a colleague and I did a lot of TI (mostly for unnatural purposes) we use a fixed width import to get the entire line and then do whatever transformation on it we required using the rules text functions.

To get a wide enough column (we reckoned 1000 was enough for us) create the process on top of a dummy file containing a single row with 1000 "q"s (see attachment), specify "Fixed Width" for the "Delimiter Type" option and set the "Contents" column in the "Variables" tab to "Other".

Then you can set the actual data source file in the prolog and do what you like with the contents of V1 on your data tab.

Be aware that TI will not give you blank lines. If you need to know about blank lines you will need further DOS trickery with the command which outputs the line numbers of the blank lines which you can pipe into another file which you would also read.

Using a combination of these two methods it is possible to recreate the exact contents of a text file as a single string in a process variable. Not that you need to do that now - but you might sometime.