or CSV - Wrong number format?

Hi,

I really like your product but i have a big problem with. In my company in 80% of case we export data to LibreOffice or Excel for add some SUM or other formula and we cannot use data just like that. I mean, we cannot make a sum easly because the format of number in the cell is not standard in some case and the software (LibreOffice / Excel) do not interpret it like a number but like text and this cause problem.

Example:

$27,361.20
6,590
17,599.67

I think will be better if exported data only can be without formating. Because like that and in my case is not usable. Or we need to past a lot of time to convert each column or remove some $, ...

Is a way to do that already ?

Thanks in advance, Michael

asked 01 Nov '11, 09:19

MichaelB
1●1●1●1
accept rate: 0%

7 Answers:

oldest newest most voted

can you look at our current development version demo at : http://dev.analytical-labs.com ? is that how you like it?

i use the raw number with format #,###.0# as actual number, not text ... so you can do SUM(B3:B10) out of the box without converting anything

let me know if that works for you

link

answered 02 Nov '11, 12:28

pstoellberger ♦♦
897●2●7
accept rate: 5%

Hi,

Your dev version look better with formating but still cannot do a SUM(), the cells are in "text mode" in my case.

Ex: '2.2460822511398373 (start with ' Apostrophe). The only column that work is the "All Products" (i can do a SUM without problem and without converting)

I tested with Excel 2003 and LibreOffice 3.4.3.

Below the MDX used for my test:

SELECT NON EMPTY {Hierarchize({{[Product].[All Products]}, {[Product].[Product Family].Members}})} ON COLUMNS, NON EMPTY Hierarchize(Union(CrossJoin([Time].[Month].Members, {[Measures].[Average Warehouse Sale]}), CrossJoin([Time].[Month].Members, {[Measures].[Store Invoice]}))) ON ROWS FROM [Warehouse]

With LibreOffice i have to do "Text to Columns" for each column and is ok after.. In excel 2003, i have to convert each cells to number..

You don't have the same result?

link

answered 07 Nov '11, 03:32

MichaelB
1●1●1●1
accept rate: 0%

0	you are talking abou the CSV export, right? XLS should be fine from what i can tell link answered 07 Nov '11, 08:50 pstoellberger ♦♦ 897●2●7 accept rate: 5%

0	no i talk about XLS export.. can you test it with excel and libreoffice if you have the same problem than me? link answered 08 Nov '11, 13:23 MichaelB 1●1●1●1 accept rate: 0%

0	are you sure you tried it against http://dev.analytical-labs.com ? at least excel works for me. will test libreoffice when i get the chance link answered 09 Nov '11, 14:24 pstoellberger ♦♦ 897●2●7 accept rate: 5%

I see that #,###.0# format is used for all number fields when exporting to .xls This results in that even Year field is displayed as 1,997.0 (check on http://dev.analytical-labs.com).

Can formatting options be set up-ed in any way in Saiku or completely turned off to get General format cell type in XLS?

I have no problems with CSV exporting, no formatting is applied there.

link

answered 02 Feb, 10:04

oli4
1●1
accept rate: 0%

edited 02 Feb, 10:12

0	yeh i'd suggest using csv export for now our xls export should get better over time :) link answered 07 Feb, 15:32 pstoellberger ♦♦ 897●2●7 accept rate: 5%

Your answer

toggle preview

community wiki

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Tags:

excel ×4
export ×3
formatting ×3
csv ×1
libreoffice ×1

Asked: 01 Nov '11, 09:19

Seen: 735 times

Last updated: 07 Feb, 15:32

Export Excel / LibreOffice / or CSV - Wrong number format?

Follow this question

Related questions