Scribe does not support reading real numbers in Excel - does it?

[Zwergfilz]

Unfortunately, the EXCEL data storage within xml-files is very complex, and as noted by others, it is virtually impossible to handle complex (and even very simple) data without the program EXCEL itself. I just discovered a new CAVEAT relating to the scribe plugin. Reading an EXCEL document with ScribeDocReadValue(), real numbers are always returned as 'integer string' without comma, and it seems impossible to get a meaningful value out of this. '29.84' -> 2984, '2984' -> 2984 (nothing to say about '2.3' -> 22999999999999998, etc.).

 

Although not documented, I tryed to address the field with a name ('Bill') instead of a cellcode 'C14'. Interestingly, this returned no error, if the name was defined in EXCEL, but a null (empty) value! Next try. The Excel calculation =TEXT(C14,"standard") returns the real number in a beautiful text format. However, scribe returns ERROR: Scribe returns ERROR for a any Text calculations, e.g.  for 'C15' = 'Jane'; 'C16'= '=C15', ScribeDocReadValue ('C16') = ERROR. Probably scribe just can NOT access the 'SharedStringTable', nor any other secondary source tables (– this has other bad effects. If you write any values to an Excel 'fill down' range with Scribe (that is, through the backdoor), Excel will never recognize and evaluate these values correctly, etc. etc.)

 

What about the real numbers, then? I only found one solution: Defining an additional cell with a calculation like 'C16'= ROUND(('C14'+1000)*10000000000000,0), or (more generic) defining TWO additional fields calculating the INT and MOD part of the number.

What an abonimably atrocious workaround!

 

However, I really wonder WHERE the 'comma' position is stored in xlm-EXCEL, and, eventually, if you guys at 360works may plan to retrieve this information in a next version. That would be great!

 

Thanks

Markus

@Excel for Mac 2011, v14.3.6, OS X 10.8.4, Java 7 25 (Build 1.7.0_25-b15), FM 12.0v4 Adv, 360Works Scribe v1.262

[shmert]

Hi Markus, 

 

Do you have a sample excel file  you could send over? I'm able to read numbers using the excel formula, e.g.

ScribeDocReadValue( "C16" )

If this returns "ERROR", call the ScribeLastError function to get a detailed message about what went wrong.

 

There may be some unusual formatting going on in your Excel file. Excel stores its numbers as raw values, and specifies a format to use when displaying it to the user. Scribe tries to look at the format definition and return results formatted the same way. Having a file to test against would be very helpful.

 

Thanks!

[Zwergfilz]

Scribe tries to look at the format definition and return results formatted the same way.

 

Hi Sam

 

Thanks for your reply. Testing again the real number issue, I wondered about the influcence of local/region settings in EXCEL, i.e. in the system preferences. No difference. But then I discovered that scribe itself unfortunately fails with GERMAN/SWISS settings, returning the quite meaningless values described earlier. Bad luck.

 

Attached are my test files and results.

 

Of course, it would be great, if this issue could be solved - workarounds, including changing the system preferences of my clients computers - are not a nice perspective .... What kind of (system) language libraries are used by scribe, FM? Java??

 

Finally, I found another unfavorable behavour of scribe, when 'trying to look at the format definition': A Zero (or any other number) formatted as "0000.000" (three digits) will be interpreted as date and returned as 30.12.1899 00:00, but correctly as '0' if formatted as "0,000.000" (including the thousands separator)! This is the one and only case I found, where scribe seems to read the user entered 'Display as ...' format options, and I think it better should be abandoned ...

scribeTestsXls.zip

[abross86]

I'm running the demo of scribe 2.15 and having the same issue. I have some cells formatted as numbers with 2 decimal place and ScribeDocReadValue reads them as dates. If it read them as date/timestamps I could interpret the result and convert it to a decimal, but it only reads it as a date which loses information about the value.

[amallison]

I have the same issue. What was the fix for this.

I'm using Scribe 3.09. The file is saved from LibreOffice on a Mac. If I save it with Excel for Mac Scribe throws a Java error.

All the fields with text come across ok but numbers come across as timestamps.