Best way to link to Excel data

by **catbill** » Wed Sep 04, 2019 6:05 am

I want to create a chart based on Excel data and that will update when the Excel data is updated.

I can do this by pasting the data into Calc with a DDE link and then creating the chart. However, I would like to know if there are other ways to do this. If so, what are they and how do they compare?

I am familiar with the documentation but am still unclear about the options.

by **keme** » Wed Sep 04, 2019 8:11 am

The DDE path should work. You need a DDE server application. Not sure whether Calc is sufficient when you are working with Excel data, or whether you must have Excel for this to work.
Another possibility is to register the spreadsheet as a database and extract data from that.

Mixing Excel and Calc data formats carries a risk of random error/incompatibility. Is it practical to save that file as an ODS?
(Excel in later incarnations - after 2007 - can save as ODS. If the data source is a third party application - whether Excel macros or standalone app - saving may be hardcoded to use an Excel-specific file type and you are out of luck.)
Not a great peril, perhaps, so "if it works, don't fix it". Just thought it was worth mentioning.

edit this topic's initial post · by **Villeroy** » Wed Sep 04, 2019 9:39 am

The best way is a linked database range because it will adjust as the amount of rows changes.
menu:File>New>Database...
Connect to existing database
Type: Spreadsheet
Specify the path to your spreadsheet
[X] Register the database
Save the database
Every table in that pseudo-database represents the used area of a sheet. Note the right table.
Nothing has been copied nor converted. It is just another view on your spreadsheet.
----------------------
Get Calc's data source window (menu:View>Data Sources)
In the left pane open your registered database, the [Tables] container and drag the right table icon into your target sheet.
Under menu:Data>Define Range you find a new database range "Import1". CLick that, expand the [Options] and check two additional options:
1. Insert and remove cells
2. Keep formatting (the formatting of the target sheet)
[Modify]
[OK]
--------------------
Create your chart from the imported range
The import range updates when you click any cell in it and call menu:Data>Refresh and the chart will update accordingly.

by **catbill** » Wed Sep 04, 2019 3:16 pm

Villeroy, Thank you for the helpful reply.
I could get as far as Data > Define Range, where I did not see a new database range Import1.
Any thoughts about what went wrong and how I can make this work?
Also, a few more questions:
Is there a name for this type of link, other than linked database range?
When do the Excel and LibreOffice files need to be open for the updating to work?
More generally, are there other ways to create a chart using external data? If so, what are they?
I am trying to understand all of the options including advantages and disadvantages.

by **catbill** » Wed Sep 04, 2019 3:22 pm

Keme, thank you for the suggestions. I think I understand them. Villeroy recommends registering a database. Presumably that overcomes the incompatibility problem. Correct?

keme wrote:The DDE path should work. You need a DDE server application. Not sure whether Calc is sufficient when you are working with Excel data, or whether you must have Excel for this to work.
Another possibility is to register the spreadsheet as a database and extract data from that.

Mixing Excel and Calc data formats carries a risk of random error/incompatibility. Is it practical to save that file as an ODS?
(Excel in later incarnations - after 2007 - can save as ODS. If the data source is a third party application - whether Excel macros or standalone app - saving may be hardcoded to use an Excel-specific file type and you are out of luck.)
Not a great peril, perhaps, so "if it works, don't fix it". Just thought it was worth mentioning.

edit this topic's initial post · by **Villeroy** » Wed Sep 04, 2019 4:34 pm

Did you try DDE? =DDE("soffice";"C:\path\file.xls";"Sheet1.A1:X99") entered as an array formula may dump the data from the xls file's Sheet1.A1:X99. But what if you insert some data or remove some data? The link will still refer to Sheet1.A1:X99 disregarding any new rows. Same when you remove rows or columns.

Similar problem with =DDE("soffice";"C:\path\file.xls";"Named_Range"). Now the reference to the named range should update when you insert new rows or columns but the target range is still an array formula that needs to be adjusted accordingly.

The one and only link type that overcomes this typical spreadsheet problem is the linked database range which connects a cell range dynamically to a database row set. In this case the database would be just another spreadsheet document.

by **Math** » Wed Sep 04, 2019 5:44 pm

greetings friends ,

I have a problem similar to this one of the topic .

but I need to do the reverse process, I need to Link Excel with libreoffice, ie I need to retrieve data in real time from a libreoffice spreadsheet located in another directory .

In short, I need to feed an Excel spreadsheet with data from a libreoffice spreadsheet located in another directory .

thank you in advance for all the help .

edit this topic's initial post · by **Villeroy** » Wed Sep 04, 2019 6:09 pm

https://support.office.com/en-us/articl ... cca2a9b95f

by **catbill** » Wed Sep 04, 2019 7:11 pm

Villeroy, I appreciate your help so far. Any advice for how to get the database range to work?
I will make a new post to ask my other questions.

catbill wrote:Villeroy, Thank you for the helpful reply.
I could get as far as Data > Define Range, where I did not see a new database range Import1.
Any thoughts about what went wrong and how I can make this work?
Also, a few more questions:
Is there a name for this type of link, other than linked database range?
When do the Excel and LibreOffice files need to be open for the updating to work?
More generally, are there other ways to create a chart using external data? If so, what are they?
I am trying to understand all of the options including advantages and disadvantages.

edit this topic's initial post · by **Villeroy** » Wed Sep 04, 2019 7:37 pm

I listed every single step in viewtopic.php?f=9&t=99225&p=477075#p477033

1) create a registered database document linked to your source spreadsheet
2) drag the table icon from Calc's data source window (NOT the database window) into the destination sheet. Try Ctrl+Shift+F4 in Calc and browse the left pane.
3) edit the database range properties so they behave properly

by **catbill** » Wed Sep 04, 2019 7:41 pm

As I said, I could get as far as Data > Define Range, but then I did not see a new database range Import1.

edit this topic's initial post · by **Villeroy** » Wed Sep 04, 2019 7:48 pm

menu:View>Data Sources or F4 or Ctrl+Shift+F4 in Calc/Writer shows the data source window. Do you see your database in that window? Expand the database name, then the "Tables", grab your table and drag it into the spreadsheet.
If you created the database document but do not see it in the data source window: menu:Tools>Options>Base>Databases and add it to the collection of registered databases.

by **catbill** » Wed Sep 04, 2019 9:56 pm

Thank you. I tried again and this time got farther. The problem now is that when I try to create a chart, it doesn't show any data. What might that mean? Any advice?
I really appreciate your guidance and patience.

edit this topic's initial post · by **Villeroy** » Wed Sep 04, 2019 11:09 pm

Wrong data types. Only numbers can be plotted in charts.

edit this topic's initial post · by **Villeroy** » Thu Sep 05, 2019 9:02 am

Attached is an archive with 3 Files Source.xlsx, ExcelDB.odb and ExcelDB_Report.ods
Extract them to a common directory.
Start LibreOffice, call menu:Tools>Options>LibreOffice Base>Databases and register the document ExcelDB.odb as ExcelDB
Open the report spreadsheet.
Click the list on the first sheet, call menu>Data>Refresh, enter a year between 2005 and 2010 and a month 1-12. The chart has as many colmns as the import range has rows.
The second sheet is a pivot chart. The chart is made from the registered data source. This is an extremely useful feature in LibreOffice but not in OpenOffice.
Notice that Sheet1 in Source.xlsx is a most simple, flat list with clear, separated data types. Nevertheless, I had to format the pivot table's date column before I could group it by quarters and years. This is not a problem if there is no text in the source column. As soon as there is a text in some column, any database program will treat the whole field as text.

by **catbill** » Thu Sep 05, 2019 9:03 pm

Thank you so much for taking the time to post all of this information. I still have some questions:

When I try to do what you suggested earlier, there are two issues:
1. When I create a chart using the data in the Calc file (which is linked to the Excel data) only a default chart appears. It contains no data. The data are definitely formatted as numbers and when I add a title, I can see it.
2. When I change data in the Excel file, the data in the Calc file does not change.
It may not be possible to determine what has gone wrong but any further suggestions would be appreciated.

Now, as for your latest post. Presumably, you are showing how the results of this procedure should look when properly done.
In the last paragraph, you say that you needed to format the date column. Do you mean format the column as dates or are you referring to something else?
What is not a problem if there is no text in the source column? Is the next sentence saying that any entry in a column will be treated as text unless specifically formatted as, say, date?
How is it determined that when Data > Refresh is clicked in ExcelDB_Report.ods, dates need to be specified, not Person, Item, or Value?

I have been learning a lot from you and hope that you can continue to bear with me as I try to understand.

edit this topic's initial post · by **Villeroy** » Thu Sep 05, 2019 9:33 pm

The question is not about format. The question is if the values are text or numbers. If they are text, no number format applies.
=TYPE(A1)
=ISNUMBER(A1)
=ISTEXT(A1)
=COUNTA(A1:A999)-COUNT(A1:A999)

or simply menu:View>HighlightValues [Ctrl+F8] highlights constant numbers in blue font color.

by **catbill** » Fri Sep 06, 2019 4:19 am

Aha! I finally figured out why I wasn't able to get the suggestion in your first post to work. I will explain my misunderstanding so that others can learn.

Here is where I went astray:
"Get Calc's data source window (menu:View>Data Sources)
In the left pane open your registered database, the [Tables] container and drag the right table icon into your target sheet."

I now realize that by "right table icon" you mean the table on the upper right that opens when I click Sheet1 under Tables. However, I was looking for an icon and assumed that you meant the icon that goes with Sheet1 so that is what I dragged onto the spreadsheet. (It didn't occur to me that the table on the right could be considered an icon.) I thought that there could be several objects/icons under "Table" and by specifying the "right" table, you meant the correct table.

Just to be clear, is this procedure that you are suggesting based on an OLE link?

Now that that problem is solved, I hope that you are still willing to clarify my questions about your later post with the attached files:
Presumably, you are showing how the results of this procedure should look when properly done. Is that your purpose?
In the last paragraph, you say that you needed to format the date column. Do you mean format the column as dates or are you referring to something else?
You say that something is not a problem if there is no text in the source column. What problem are you referring to?
Is the next sentence saying that any entry in a column will be treated as text unless specifically formatted as, say, date?
How is it determined that when Data > Refresh is clicked in ExcelDB_Report.ods, dates need to be specified, not Person, Item, or Value?

Thanks again for all of your help.

edit this topic's initial post · by **Villeroy** » Fri Sep 06, 2019 11:28 am

I mean the correct table icon which represents your data set. Spreadsheets may have hundreds of sheets.
When you click on a table icon in the left pane, table data appear in the right pane. What you drag into your document is the table icon from the left pane. Many users are tempted to drag rows or columns from the right pane.

German data source window.
Current data source is "Chargen"
The icons "BHR", "CH", "DESINF", "FLT" represent tables of a databse.
The displayed data set on the right side is from table "CH" (icon name in bold font)
"Abfragen" = "Queries", "Tabellen" = "Tables"

In order to import and link a table to a spreadsheet, you drag any of the table icons or a query icon into a spreadsheet document.

by **catbill** » Fri Sep 06, 2019 5:46 pm

Thank you for further explanation.

I am puzzled. I tried the method you suggest at least 10 times. Every time, the chart was blank. Then, after reading your post I tried again. This time the chart is filled in with the data. I can’t figure out what has changed to make this now work but am happy that it does.

However, there is now another problem: When I change data in the source document and select Data > Refresh, the data is not updated. Any ideas on what I am doing wrong?

I would be grateful if you would answer the questions in the latter part of my last post. With a better grasp of how this all works, I would not ask you so much for help—which I deeply appreciate.

edit this topic's initial post · by **Villeroy** » Fri Sep 06, 2019 9:15 pm

Even though the vast majority of users try to use spreadsheets as databases, spreadsheets are just lousy databases, no matter which program you misuse.
Pulling linked data from a database means that you pull data that are stored to disk. In case of a spreadsheet this means that the whole source document needs to be saved. In case of a true database, only the last edited row needs to be written. Databases are written row by row. With OpenOffice you even need to restart the whole office suite in order to get the latest data from a spreadsheet database.
Testing my own example database with LO 6.2 on Windows, all I can tell is that everything is updated when I insert new rows into the source list, enter data into new rows, save to disk and refresh the import range or the pivot table respectively.
----
Oh, I think I found a bug regarding the link between database range and the chart. When there was only one record in the db-range the chart's data range does not expand when new rows are added to the database range. The size of the database range is correct but the chart does not follow from one record to many records. You have to adjust the chart's source range manually. This has nothing to do with the database connection. It happens also with manual insertions and deletions of rows.

by **catbill** » Fri Sep 06, 2019 9:53 pm

So, does the source document need to be manually saved for the link to work? How does the database get updated or does it automatically update when the data in the source file changes or is saved?

edit this topic's initial post · by **Villeroy** » Sat Sep 07, 2019 4:56 pm

The database document (*.odb) does not contain any data. It is a mere configuration file. It does not change when you change the connected spreadsheet.To this configuration file you can add queries, forms and reports. A query is much like a "formula" returning a subset from a table (see my example file). Input forms are not particularly useful when the database is read-only. You have to edit spreadsheet data with a spreadsheet application. Reports are a nice way to wrap database data into a professional print layout.
The database document fetches its data from an external source (spreadsheet in this case). It changes and needs to be saved when you edit query definitions, forms and reports. Otherwise you can keep it closed and forget about it as long as you don't delete. It provides the info where a certain set of data can be found.

by **catbill** » Sat Sep 07, 2019 6:34 pm

Thank you. Earlier you called it something like a pseudo-database. Now I see what you mean.

So how is it determined that when Data > Refresh is clicked in ExcelDB_Report.ods, dates need to be specified, not Person, Item, or Value?

I am still trying to figure out why I can't get the data to update. Any further thoughts?

edit this topic's initial post · by **Villeroy** » Sat Sep 07, 2019 9:19 pm

No help possible without any data.

by **catbill** » Tue Sep 10, 2019 4:02 am

I still don’t understand why I can’t get this (linking to Excel data) to work but I don't see a way to attach the files so you can have a look. Should I assume that even if I could attach the files, the links would be broken and it would therefore be hard to figure out what the problem is?

Wait, I just realized that you attached files earlier so there must be a way to attach them.

One other question:
For this to work, after Excel data changes does the file need to be closed or just saved? It sounds like the Base file can remain closed.

I asked some questions about the files you sent earlier. I could copy them here but maybe it would be easier for you if I keep this post short.

Thank you.

by **robleyd** » Tue Sep 10, 2019 4:35 am

there must be a way to attach them.

[Forum] How to attach a document here Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire and post the link here. The link also contains information on how to anonymise your document if it contains confidential information.

by **catbill** » Tue Sep 10, 2019 3:06 pm

Thank you. I have attached the 3 files: Excel file with data, Base file, and Calc file that links to the Excel data.
Are the links still active if the files are moved? If not, maybe someone can still find out something helpful about why this is not working.

edit this topic's initial post · by **Villeroy** » Tue Sep 10, 2019 4:07 pm

Your set of files works fine.

One more thing that should be set when working with Calc: Tools>Options>Calc>General:"Expand references when new rows or columns are inserted". This global option applies to all spreadsheet documents.
With this option set and the database being registered, I can insert a new row directly below the last row for June, fill in some data, store to disk and refresh the db range on the other spreadsheet.

Indeed, when this option is not set, the database expands but the chart does not follow. Calc has too many options that need to be set or unset (and LibreOffice uses bad defaults).

by **catbill** » Tue Sep 10, 2019 4:31 pm

Thank you for looking at the files.

Are you saying that when you change data in the Excel file, the data changes in the Calc file? Any thoughts about why that doesn't work for me?

Also, does the Excel file need to be closed or is just saving the data enough for the Calc file to change?

Are the files still linked even though their locations have changed?

Best way to link to Excel data

Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Re: Best way to link to Excel data

Who is online