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 as new sheet
- Click [Insert] -> [Sheet from file...]
- Select the file you want to import and click [Insert].
- The dialog box about importing CSV files will open. Make the settings to define the file format and click [OK]
- The Insert Sheet dialog box should now be visible.
- If you want the data to update when the CSV file changes, tick the box in front of "Link"
- Click OK.
- The data does not update itself, but it is fairly easy to do so, see below.
Updating the data
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.
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.
Adding a nice update button
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 (from the OpenOffice forum) (Note that the "Sub Main" part is probably already there).
Sub refreshAllSheetLinks() oEnum = thisComponent.SheetLinks.createEnumeration while oEnum.hasMoreElements oLink = oEnum.NextElement oLink.refresh wend End Sub Sub Main end sub
Now we can add the button.
- Save the macro and exit the macro editor
- Click [View] -> [Toolbars] -> [Form Controls]
- Make sure the [Design Mode] button (top right) is active
- Use this toolbar to add a "Push Button"
- Right click on the button and select [Control...]
- Change [Label] in the [General] tab to "Update"
- Add the macro you just created to the [Mouse Button Pressed] event in the [Events] tab. (click [...] and browse for your macro)
- Turn the [Design Mode] off.
- Close the toolbar. Done!
NOTE 1: The [Insert] -> [Link to External Data...] option may also be useful, but does not seem to work with CSV files.
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.