[Solved] Cell Format when linking to external data

Discuss the spreadsheet application

[Solved] Cell Format when linking to external data

Postby gurkand » Wed Jun 12, 2019 9:27 pm

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

Re: Cell Format when linking to external data

Postby Villeroy » Wed Jun 12, 2019 9:44 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27220
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Cell Format when linking to external data

Postby gurkand » Thu Jun 13, 2019 6:57 am

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

Postby gurkand » Thu Jun 13, 2019 11:53 am

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

Postby gurkand » Thu Jun 13, 2019 6:43 pm

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

Re: Cell Format when linking to external data

Postby Villeroy » Thu Jun 13, 2019 7:11 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27220
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Cell Format when linking to external data

Postby gurkand » Thu Jun 13, 2019 7:18 pm

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

Re: Cell Format when linking to external data

Postby Villeroy » Thu Jun 13, 2019 9:12 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27220
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Cell Format when linking to external data

Postby gurkand » Fri Jun 14, 2019 9:20 am

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

Re: Cell Format when linking to external data

Postby RPG » Fri Jun 14, 2019 10:28 am

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 6.2.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2171
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Cell Format when linking to external data

Postby Villeroy » Fri Jun 14, 2019 10:30 am

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27220
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Solved] Cell Format when linking to external data

Postby gurkand » Fri Jun 14, 2019 5:24 pm

Switched to LibreOffice 6.2.

It works perfectly fine now.

Thanks
LibreOffice 6.3
gurkand
 
Posts: 29
Joined: Wed Feb 28, 2018 10:27 am


Return to Calc

Who is online

Users browsing this forum: jon oneill, Zizi64 and 20 guests