TM1 Forum

Hi, All

I have a TI process where Data Source is an excel CSV file. This is an existing TI process and working normally under following structure.

In, this TI we upload headcount information to the cube. In current TI, if new employee is not part of employee dimension then enter the new employee in the employee dimension through dimensionelementinsertdirect function. Once this activity is done then it enters the employee related data in to cube.

In new structure, i wanted to assign new employee operation in to meta data with dimensionelementinsert function.

Data Source Column 2: is ignored in variable tab having data "Lance, Christopher".
Data Source Column 14: is having string data type which represents the employee type: Full Time, Part Time etc
Data Source Column 15: is having numeric data type which represents the standard hours for an employee

i got following error:

"126",""Lance"," Christopher"","Christopher","Lance","137","systemanalyst","","abc","10410","4077","72","A","R","Full time","100","1","PG_Semi-Monthly","9/5/2018","9/5/2018","","LOC_ZA","Remote - ABC"
Data Source line (2) Error: MetaData procedure line (0): Cannot convert field number 15, value "Full time" to a real number.

According to me, due to comma deliminator ""lance"."christopher"" => count as 2 words.

is there a way to avoid this ?

Thank You

Dharav

Go back to the person that is creating the file and ask them to use something else as the delimiter.My personal preference is the pipe character, "|", as that is rarely in data. They could also use tabs, or any other character that is unlikely to be embedded in the data.

If you want to stick with csv, then I would suggest getting the source file tidied up, as I believe the csv file is not correctly created.

In the scenario you've shown, if correctly formatted in csv, it should be
"126","Lance, Christopher","Christopher","Lance","137","systemanalyst","","abc","10410","4077","72","A","R","Full time","100","1","PG_Semi-Monthly","9/5/2018","9/5/2018","","LOC_ZA","Remote - ABC"

There should be only be one set of quotes around Lance, Christopher. And if you specify the quote character as ", it would remove the quotes and separate your data row into relevant fields.

But you can still run into problems when commas or quotes are used in the data, so they aren't the best delimiter characters. As tomok suggested, "|" is rarely used in data. I personally, prefer the use of tabs in files (char(9)), with no quotes around fields.