Page 1 of 1

[Solved] Cell Format when linking to external data

PostPosted: Wed Jun 12, 2019 9:27 pm
by gurkand
Hello All,

I am getting data from web using Insert > Link to external data.

It brings the data, but the resulting cells do not have the correct format. Therefore, I cannot work with them.

I attach the sample spreadsheet. As a summary;

https://www.finanzen.ch/kursziele/abb

HTML_8

Column E has data like '18.00CHF (text instead of currency)
Column G has data like '31.05.19 (text instead of date in 31.12.99 format)

I tried different combos while inserting the link, ie. different Languages for input (Automatic, Swiss, etc.), and select/unselect detect special numbers option.

v 4.1.5

Any help, please? Thanks in advance.

Best regards,

Re: Cell Format when linking to external data

PostPosted: Wed Jun 12, 2019 9:44 pm
by Villeroy
Set import language to "Deutsch(Schweiz)" [German(Switzerland)].
Check option "Detect special numbers"

Re: Cell Format when linking to external data

PostPosted: Thu Jun 13, 2019 6:57 am
by gurkand
Villeroy wrote:Set import language to "Deutsch(Schweiz)" [German(Switzerland)].
Check option "Detect special numbers"

This is exactly what I already wrote. It did not work and that is why I posted this question.

Re: Cell Format when linking to external data

PostPosted: Thu Jun 13, 2019 11:53 am
by gurkand
Upgraded to 4.1.6 and retried.

Still no success.

Any help will be very much appreciated. Thanks in advance.

Re: Cell Format when linking to external data

PostPosted: Thu Jun 13, 2019 6:43 pm
by gurkand
Hello,

I have tried all combos I can think of even in the Tools > Options > Languages > Locale setting and still I cannot fix this issue.

Anyone who can get this data from the page I mentioned? Maybe if she/he shares her/his input specifications, it can help me...

Regards,

Re: Cell Format when linking to external data

PostPosted: Thu Jun 13, 2019 7:11 pm
by Villeroy
I see. menu:Edit>Find&Replace the CHF with nothing. It does not recognize what CHF means. The dates are imported correctly though.

Re: Cell Format when linking to external data

PostPosted: Thu Jun 13, 2019 7:18 pm
by gurkand
Thanks for the CHF thing very much. It helps that part.

But, the date colum is still not working for me.

Any chance for you to confirm these:
1. You import data with selecting German(Switzerland) & Detect special characters like Date
2. Your OO Calc Tools Options is as attached
3. Your windows setting is something like I attached.

I am pretty sure something in my environment is different then yours, so if you can tell me the differences I will fix them in my side.

Thanks for your time...

Re: Cell Format when linking to external data

PostPosted: Thu Jun 13, 2019 9:12 pm
by Villeroy
This office suite follows the system's locale setting in so far that it applies the same language (de-CH) but not the details that are set for this language on the operating system level. It applies its own set of rules based on the locale setting found in the operating system. This way the behaviour is the same documents are shared across continents and operating systems.

P.S.: When I select and copy the table below the charts and paste it into a spreadsheet, I get all values right but with a stupid currency format (DM). But this can be reset with Ctrl+M.

Re: Cell Format when linking to external data

PostPosted: Fri Jun 14, 2019 9:20 am
by gurkand
Thanks for the Explanation but it does not help me.

Instead of copying data from the attached doc, have you tried to get the data into a new doc yourself? Currency column is easy to fix thanks to your tip but the date column is still a Problem. Does it imported as Date when you try with a new doc?

If so, it is interesting right? Same Thing working for you and not for me...

I added a new column, btw, and using the DATEVALUE function I managed to convert that column to a number, at least.

Re: Cell Format when linking to external data

PostPosted: Fri Jun 14, 2019 10:28 am
by RPG
It seemes not to work in ApacheOffice 4.2.0 but it works in LibreOffice. I do get Dutch fl as currency.

Re: Cell Format when linking to external data

PostPosted: Fri Jun 14, 2019 10:30 am
by Villeroy
Sorry, I forgot to mention that I use LibreOffice. I'll remove OpenOffice from my signature since I stopped using it.

[Solved] Cell Format when linking to external data

PostPosted: Fri Jun 14, 2019 5:24 pm
by gurkand
Switched to LibreOffice 6.2.

It works perfectly fine now.

Thanks