Calc is shortening fully qualified paths to external files

[taylorkh]

Let me preface this by stating that I am using a rather old version of LibreOffice 5.3.6.1 as that is what is current With RHEL/CentOS 7. I have a newer version on a LinuxMint virtual machine and will try that. However, due to the location of files and other programs on my workstation that presents some other challenges. Here is the issue:

I perform some financial analysis which dumps a row of summary data to an Excel format file. One row of data from A1 to H1. I have linked my Calc spreadsheet to that data with links such as:

=‘file:///data/data/FP2/Schwab/allocate2.xls’#$allocate2.E1

This works great and brings in the data I need. (Yes, there are two “data” directories in the path.) However, after saving the file and reloading it I get the dialog to update links. I press yes and get an error message to the effect:

“The following external file could not be loaded. Data linked from this file did not get updated.
file://///FP2/Schwab/allocate2.xls”

When I examine the cells with my linked data I see that the path has been shortened.

One final tidbit… I created a new calc spreadsheet, linked ONE call, saved the file and reloaded. The link was intact. I then copied the cell with the link across the row so as to read all of the data from the source Excel file. It worked great. When I saved and reloaded the file with multiple cells linked I received the error described above.

Yes, I am saving my Calc spreadsheet as .ods (I have read issues related to saving the linkING spreadsheet in Excel format.)

Any suggestions?

TIA,

Ken

[taylorkh]

Oh my head hurts. I tried this in 6.4.7.2 on Linux Mint. No go. I tried Sheet - Link External Data. That provided me an option to link to a csv file. This looked promising as I can export the results from my analysis program to a csv file. I did so and was able to link to the resulting csv file. This pulled in the entire row of data - which is what I wish to do. I can save, reload and update the Calc spreadsheet and it pulls in the latest data. I still wish I could cherry pick cells from a remote spreadsheet.

Note: The Sheet - Link External Data does not work in LO 4. I can select the csv file, go through the dialog to identify the data in the file but when I return to the External Data dialog there are no “Available Tables/Ranges” shown and OK is grayed out.

This is déjà vu all over again. About 35 years ago I created a series of SuperCalc spreadsheets which read in a dBase format data file, performed some calculations and then changed the background color of the spreadsheet when a contract was 80% expended (yellow) and 95% expended (red). The raw data was pulled from the mainframe and analyzed with FoxBase which then created the dBase format file to feed the spreadsheet. All that on DOS 3 with a pre-Pentium PC!

[erAck]

Sheet - Link External Data does not work in LO 4

That’s years old and way outdated. Ability to use CSV as linked external data was added later.