loading fields from csv without knowing the type of value

[yyround]

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

[gtonkin]

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]

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.

[lotsaram]

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 ) )

[Wim Gielis]

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

[yyround]

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]

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).