[Solved] Cell Format when linking to external data

Discuss the spreadsheet application
Post Reply
gurkand
Posts: 29
Joined: Wed Feb 28, 2018 10:27 am

[Solved] Cell Format when linking to external data

Post 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,
Attachments
LinktoExternal.ods
(15.52 KiB) Downloaded 106 times
Last edited by RoryOF on Fri Jun 14, 2019 11:07 pm, edited 2 times in total.
Reason: Added green tick [RoryOF, Moderator]
LibreOffice 6.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Cell Format when linking to external data

Post by Villeroy »

Set import language to "Deutsch(Schweiz)" [German(Switzerland)].
Check option "Detect special numbers"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gurkand
Posts: 29
Joined: Wed Feb 28, 2018 10:27 am

Re: Cell Format when linking to external data

Post 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.
LibreOffice 6.3
gurkand
Posts: 29
Joined: Wed Feb 28, 2018 10:27 am

Re: Cell Format when linking to external data

Post by gurkand »

Upgraded to 4.1.6 and retried.

Still no success.

Any help will be very much appreciated. Thanks in advance.
LibreOffice 6.3
gurkand
Posts: 29
Joined: Wed Feb 28, 2018 10:27 am

Re: Cell Format when linking to external data

Post 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,
LibreOffice 6.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Cell Format when linking to external data

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gurkand
Posts: 29
Joined: Wed Feb 28, 2018 10:27 am

Re: Cell Format when linking to external data

Post 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...
Attachments
WindowsRegion.jpg
ToolsOptions.jpg
LibreOffice 6.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Cell Format when linking to external data

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gurkand
Posts: 29
Joined: Wed Feb 28, 2018 10:27 am

Re: Cell Format when linking to external data

Post 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.
LibreOffice 6.3
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Cell Format when linking to external data

Post by RPG »

It seemes not to work in ApacheOffice 4.2.0 but it works in LibreOffice. I do get Dutch fl as currency.
Last edited by RPG on Fri Jun 14, 2019 10:31 am, edited 1 time in total.
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Cell Format when linking to external data

Post by Villeroy »

Sorry, I forgot to mention that I use LibreOffice. I'll remove OpenOffice from my signature since I stopped using it.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
gurkand
Posts: 29
Joined: Wed Feb 28, 2018 10:27 am

[Solved] Cell Format when linking to external data

Post by gurkand »

Switched to LibreOffice 6.2.

It works perfectly fine now.

Thanks
LibreOffice 6.3
Post Reply