loading fields from csv without knowing the type of value

Post Reply
yyround
Posts: 26
Joined: Tue Apr 18, 2017 1:20 am
OLAP Product: TM1
Version: PA2.0.8
Excel Version: MS 2016

loading fields from csv without knowing the type of value

Post by yyround » Thu Aug 22, 2019 10:00 am

Hi TM1 Gurus,

I stumble upon this when try to create a TI that loads the data (a csv file contains header and the data from second row and onwards) into a cube.

Data comes from an external Notes server. The number of columns varies, although it shouldnt happen very often but once a while, there could be new fields added to the notes server. So when load the data, TM1 needs to check whether the header exist in the measure dimension, and if doesnt, creates it, then loads the data.

Without knowing the type of data (numeric or string), how do we create the element? instead of asking Notes to output the metadata of the fileds extracted.

Regards
YY

User avatar
gtonkin
MVP
Posts: 737
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: loading fields from csv without knowing the type of value

Post by gtonkin » Thu Aug 22, 2019 10:07 am

For many imports I define all variables as String and then deal with them in Metadata or Data.
Unfortunately there is no ISNUMBER function so you will need do your own tests.

Sometimes I approach the imports from another angle - add attributes to my measures dimension for Import Column Number.
My Data tab then enumerates a static subset of measures configured with Column Numbers. I get the Column Number and use expand to get the value.
I can then use DTYPE on the measure to see if I need to test if numeric and then convert, update etc.

declanr
MVP
Posts: 1628
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: loading fields from csv without knowing the type of value

Post by declanr » Thu Aug 22, 2019 10:33 am

You could completely automate this but it becomes risky in case the CSV comes in as complete garbage one time and you could end up with it creating the measures etc anyway.

But create a process to handle however many columns you expect plus an extra 10 or so (all set as string - other)
Have a new process for the same source that gets kicked off from the prolog of your main process...
in the new TI have it check your first column for the measure names - if it finds a new one then just store that under a variable (e.g vsCol17Name) then process either all rows or x amount of rows and when processing loop each character and if you find one that isn’t a number of decimal point etc you know the contents will be string so you can set it as a string measure otherwise set it as a numeric.

Then when your main process gets to the data tab you can use a DType and decide whether you need to convert the string variable to numeric before loading the data.


That being said if this is few and far between I would just have the process check the columns and if it finds a new one it would email someone to check and update the process or “fix the CSV file”, might seem like an overhead but probably safer than the risk of auto updating the cube with garbage and no one realising it’s happened.
Declan Rodger

lotsaram
MVP
Posts: 3318
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: loading fields from csv without knowing the type of value

Post by lotsaram » Thu Aug 22, 2019 1:30 pm

gtonkin wrote:
Thu Aug 22, 2019 10:07 am
Unfortunately there is no ISNUMBER function so you will need do your own tests.
No there isn't. But if you don't want to iterate the string character by character testing for a number or decimal then probably the closest you can get is
IF( sValue @= NumberToString( Numbr( sValue ) )
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Wim Gielis
MVP
Posts: 2260
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: loading fields from csv without knowing the type of value

Post by Wim Gielis » Thu Aug 22, 2019 1:58 pm

I wouldn't rely too much on these kind of string comparisons to decide whether an entry is numeric or string.
Only in cases where you can really control the format of the input, you could use it.

For example, this code:

Code: Select all

vValue = '200';
AsciiOutput( 'test.txt', vValue, If( vValue @= NumberToString( Numbr( vValue )), 'number', 'no number' ));

vValue = '200,5';
AsciiOutput( 'test.txt', vValue, If( vValue @= NumberToString( Numbr( vValue )), 'number', 'no number' ));

vValue = '200.4';
AsciiOutput( 'test.txt', vValue, If( vValue @= NumberToString( Numbr( vValue )), 'number', 'no number' ));

vValue = '-200';
AsciiOutput( 'test.txt', vValue, If( vValue @= NumberToString( Numbr( vValue )), 'number', 'no number' ));

vValue = '-200,5';
AsciiOutput( 'test.txt', vValue, If( vValue @= NumberToString( Numbr( vValue )), 'number', 'no number' ));

vValue = '-200.4';
AsciiOutput( 'test.txt', vValue, If( vValue @= NumberToString( Numbr( vValue )), 'number', 'no number' ));

vValue = '5E+3';
AsciiOutput( 'test.txt', vValue, If( vValue @= NumberToString( Numbr( vValue )), 'number', 'no number' ));

vValue = '5E-3';
AsciiOutput( 'test.txt', vValue, If( vValue @= NumberToString( Numbr( vValue )), 'number', 'no number' ));

vValue = '-5E+3';
AsciiOutput( 'test.txt', vValue, If( vValue @= NumberToString( Numbr( vValue )), 'number', 'no number' ));

vValue = '-5E-3';
AsciiOutput( 'test.txt', vValue, If( vValue @= NumberToString( Numbr( vValue )), 'number', 'no number' ));
gives:

"200" "number"
"200,5" "no number"
"200.4" "no number"
"-200" "number"
"-200,5" "no number"
"-200.4" "no number"
"5E+3" "no number"
"5E-3" "no number"
"-5E+3" "no number"
"-5E-3" "no number"


http://www.wimgielis.com/tm1_numbersandstrings_EN.htm
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

yyround
Posts: 26
Joined: Tue Apr 18, 2017 1:20 am
OLAP Product: TM1
Version: PA2.0.8
Excel Version: MS 2016

Re: loading fields from csv without knowing the type of value

Post by yyround » Sun Aug 25, 2019 5:21 am

Thanks guys for all your advices.

I guess we will have to get the notes dB to send a separate metadata file along with the data file.

Regards
YY

David Usherwood
Site Admin
Posts: 1377
Joined: Wed May 28, 2008 9:09 am

Re: loading fields from csv without knowing the type of value

Post by David Usherwood » Sun Aug 25, 2019 10:17 am

Until around 2002 InfoCat did both TM1 and Lotus Notes work. I recall that there was then an ODBC driver for Lotus Notes - but Notes' data model was quite unusual in that fields could be multi valued and the multiple values did not need to be the same data type. In practice the design and usage of the 'database' (.NSF file) determined what was stored so you could deduce what you were likely to find (but then validate).

Post Reply