{"id":622,"date":"2013-01-09T17:23:09","date_gmt":"2013-01-09T15:23:09","guid":{"rendered":"http:\/\/blog.louic.nl\/?p=622"},"modified":"2016-03-10T12:13:12","modified_gmt":"2016-03-10T10:13:12","slug":"automatically-importing-data-from-an-external-csv-file-in-libreoffice-calc","status":"publish","type":"post","link":"https:\/\/blog.louic.nl\/?p=622","title":{"rendered":"Automatically (almost) importing and updating data from an external CSV file in LibreOffice Calc"},"content":{"rendered":"<p>How import a CSV file into a LibreOffice (or OpenOffice) spreadsheet?<br \/>\nI wanted an easy way to update the data when the CSV file changes, and describe the method that was most suitable for me below.<\/p>\n<h2>Importing a CSV file as new sheet<\/h2>\n<ul>\n<li>Click [Insert] -> [Sheet from file...]<\/li>\n<li>Select the file you want to import and click [Insert].<\/li>\n<li>The dialog box about importing CSV files will open. Make the settings to define the file format and click [OK]<\/li>\n<li>The Insert Sheet dialog box should now be visible.<\/li>\n<li>If you want the data to update when the CSV file changes, tick the box in front of \"Link\"<\/li>\n<li>Click OK.<\/li>\n<li>The data does not update itself, but it is fairly easy to do so, see below.<\/li>\n<\/ul>\n<h2>Updating the data<\/h2>\n<p>Unfortunately, the data is not reloaded when the CSV file changes, but it is easy to do this manually by going to [Edit] -> [Links...] and then clicking [Update]. Also, when you save your spreadsheet and click File -> Reload, it will ask you if the \"links to other files should be updated\". Click \"yes\" to re-read the CSV data. The same will happen if you close and re-open the spreadsheet.<\/p>\n<p>It is important to note that the the tab (sheet) that contains the data from the file can ONLY contain these data. Other columns may be deleted when the data is updated. Also, I think it is good practice to keep the original data in unmodified form in their own sheet and the analysis and graphs in a new one.<\/p>\n<h2>Adding a nice update button<\/h2>\n<p>To improve this a little, let's put an [Update] button on the spreadsheet that re-reads the CSV file when clicked. All the file-format settings you made earlier when importing the file will automatically be applied. To do this, we first need a macro that updates the link. Go to [Tools] -> [Macros] -> [Organise Macros] -> [Openoffice.org Basic] -> [Edit]. You need to make a new Sub procedure that does the updating. The code is given below (<a href=\"http:\/\/forum.openoffice.org\/en\/forum\/viewtopic.php?f=20&t=6004\">from the OpenOffice forum<\/a>) (Note that the \"Sub Main\" part is probably already there).<\/p>\n<pre lang=\"vb\">\r\nSub refreshAllSheetLinks()\r\n   oEnum = thisComponent.SheetLinks.createEnumeration\r\n   while oEnum.hasMoreElements\r\n      oLink = oEnum.NextElement\r\n      oLink.refresh\r\n   wend\r\nEnd Sub \r\n\r\nSub Main\r\nend sub\r\n<\/pre>\n<p>Now we can add the button.<\/p>\n<ul>\n<li>Save the macro and exit the macro editor<\/li>\n<li>Click [View] -> [Toolbars] -> [Form Controls]<\/li>\n<li>Make sure the [Design Mode] button (top right) is active<\/li>\n<li>Use this toolbar to add a \"Push Button\"<\/li>\n<li>Right click on the button and select [Control...]<\/li>\n<li>Change [Label] in the [General] tab to \"Update\"<\/li>\n<li>Add the macro you just created to the [Mouse Button Pressed] event in the [Events] tab. (click [...] and browse for your macro)<\/li>\n<li>Turn the [Design Mode] off.<\/li>\n<li>Close the toolbar. Done!<\/li>\n<\/ul>\n<h2>Final notes<\/h2>\n<p>NOTE 1: The [Insert] -> [Link to External Data...] option may also be useful, but does not seem to work with CSV files.<\/p>\n<p>NOTE 2: It may be possible to set up an external data source via [Tools] -> [Options] -> [Databases] but I find that hard to use and have not yet looked into using that option.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>How import a CSV file into a LibreOffice (or OpenOffice) spreadsheet? I wanted an easy way to update the data when the CSV file changes, and describe the method that was most suitable for me below. Importing a CSV file &hellip; <a href=\"https:\/\/blog.louic.nl\/?p=622\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[44,41],"tags":[],"_links":{"self":[{"href":"https:\/\/blog.louic.nl\/index.php?rest_route=\/wp\/v2\/posts\/622"}],"collection":[{"href":"https:\/\/blog.louic.nl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.louic.nl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.louic.nl\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.louic.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=622"}],"version-history":[{"count":24,"href":"https:\/\/blog.louic.nl\/index.php?rest_route=\/wp\/v2\/posts\/622\/revisions"}],"predecessor-version":[{"id":754,"href":"https:\/\/blog.louic.nl\/index.php?rest_route=\/wp\/v2\/posts\/622\/revisions\/754"}],"wp:attachment":[{"href":"https:\/\/blog.louic.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=622"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.louic.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=622"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.louic.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=622"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}