[Solved] Cell Format when linking to external data

by **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,

edit this topic's initial post · by **Villeroy** » Wed Jun 12, 2019 9:44 pm

Set import language to "Deutsch(Schweiz)" [German(Switzerland)].
Check option "Detect special numbers"

by **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.

by **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.

by **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,

edit this topic's initial post · by **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.

by **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...

edit this topic's initial post · by **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.

by **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.

by **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.

edit this topic's initial post · by **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.

by **gurkand** » Fri Jun 14, 2019 5:24 pm

Switched to LibreOffice 6.2.

It works perfectly fine now.

Thanks