TM1 Forum

hi,

I'm using TI in 8.4.2 with Excel 2003. I'm reading a csv file into a cube. I'm getting an error message
Error:Metadata procedure line (0):Cannot convert field number 25, value "-73,743.00" to a real number

I have tried using text to columns to format the column to ensure it is a value - but the process is reading in some of the data whilst some is read in as i would expect. Can't fathom why it is doing this so help please.

Also one of the columns has some blanks which i want to put against an element "no supplier". I tried using an If statement but think i got the syntax wrong (afraid i can't bring the info home from the MOD). Are their any examples i can look at to see where i went wrong?

thanks for any pointers

regards

David

No commas allowed in the number because TM1 is going to think that is a field separator. Reformat your Excel spreadsheet to be General format and re-save as CSV.

seems too simple...but then the best solutions usually are

this is despite the process having the deimal point and thousand separators defined?

I'll try it tomorrow

thanks

daitaylor wrote:this is despite the process having the deimal point and thousand separators defined?

It doesn't matter what you do with data after the fact. If it doesn't get in the right bucket your process isn't going to work correctly. This isn't just true for TM1, it's true for ANY system that is set to process in comma-delimited ASCII files (and that's what a CSV is). Think about it for a minute. If you include commas in the data, how in the world is TM1 going to know when a comma represents the end of a field and when it represents a thousand separator?????? It can't read your mind.

tomok wrote:If you include commas in the data, how in the world is TM1 going to know when a comma represents the end of a field and when it represents a thousand separator?

Some systems include quotes as well. E.g.
"1,200","1,300","1,000"

It's a bit more convoluted, but it does make things clearer. I thought TM1 did this actually, might try it out later.

Martin Ryan wrote:Some systems include quotes as well. E.g.
"1,200","1,300","1,000"

It's a bit more convoluted, but it does make things clearer. I thought TM1 did this actually, might try it out later.

Yes, that will work OK in TM1. The problem is going to be in creating a CSV file formatted like that from Excel. The easy answer, and one that will always work, is to always format your columns in the Excel sheet as General and it won't be an issue. I've found that when dealing with people that don't have a complete understanding of programming or are not experienced in dealing with data movement between systems and the ramifications of different data types, it is better not to confuse them with all the "possibilities" and just give them a solution that is easy to understand and will almost always work.