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!
Final notes
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.
What if reordering of columns is needed after a CSV file import?
Can that be automated too?
I would choose the "easy" solution and make an extra sheet that copies all the data from the first sheet (using formula of course) but in a different order. It may not be the most elegant solution but it gets the job done quickly.
Thanks for your reply, but formula seems rather big (like saying the C language could help)? Could you give me a few quick hints about which commands I could use? I can probably take it from there.
What I meant was the following:
- Imagine your CSV file is in a sheet named Sheet1
- Make a new sheet named Sheet2
- We are going to swap column C and E
- In Sheet2, Cell A1, type: =Sheet1.A1
- In Sheet2, Cell B1, type: =Sheet1.B1
- In Sheet2, Cell C1, type: =Sheet1.E1
- In Sheet2, Cell D1, type: =Sheet1.D1
- In Sheet2, Cell E1, type: =Sheet1.C1
- Copy these all down and make sure there are plenty of rows (also in case there are new rows added to the imported csv file).
- Hide one or both of the sheets if you like.
Of course this can be done with a script but why would you? I think this is much easier and more clear.
I was expecting some formula magic, but this plain simple. Thanks again.
Close but no sigar. Unfortunately references to empty fields are filled with: '0
That can be solved (I want my cigar :)
Replace =Sheet1.A1 by the following and copy down:
=IF(Sheet1.A1="","",Sheet1.A1)
Do the same for the other rows of course.
I need to save and recall data inside one sheet of an 8 sheet file. This page contains weather data. I want to save that data to an external file, then retrieve the data from a external file into the same place.
I do not want to add an additional sheet to my file.
So far, Insert Data From File will not work because it asks for a range name in the external file. I haven't been able to get a range name into the external file.
I do not understand why you want to export and then import the same data. If you can better explain what you want to do it is easier to find the correct solution for your use case.
Why this requirement? If that solves your problem I would add an extra sheet and hide it (just right click on the sheet tab and select hide).
That is "normal". Despite the confusing label on the menu item you can only import certain types of files this way, strangely enough not including simple txt or csv files.
Hank offers louic a well deserved fine quality cigar.
Thanks louic!
Hi here,
Have a question for the OO experts . We build an application for law offices under open source with a previous OpenERP build now called LibrERP. Build in python and using postgresql as DB. I wonder if its hard to do to make some interchange between this and OO to use project data projectname and number and chosen contact thats linked and so create a csv or xml file which could be read by the OO writer. Or can we let Oo view to a dataview. On OO side we want a popup where we can chose this project and create our letter.
I have no experience with this but the openoffice wiki mentions something about postgresql connectivity: https://wiki.openoffice.org/wiki/Base/connectivity/PostgreSQL
Hi louic,
the nice update button (the code above) works very well for me, can you change te code to make the updates automatically after every 60 sec?
Thanks in advance
Hi! Thank you for this article, it was very educative! Could you tell, is it possible to get the date&time of the imported file, and to display it in a cell, as i press the 'update' button?
Hi Lac,
You could add something like the following to the refreshAllSheetLinks() function:
At the very top:
Dim Sheet As Object
Sheet = ThisComponent.Sheets(0)
Below oLink.refresh:
Sheet.getCellRangeByName("A1").String = "Imported CSV File"
Sheet.getCellRangeByName("B1").String = oLink.Url
Sheet.getCellRangeByName("A2").String = "CSV File Date"
Sheet.getCellRangeByName("B2").String = FileDateTime(oLink.Url)
Sheet.getCellRangeByName("A3").String = "Last Updated"
Sheet.getCellRangeByName("B3").String = Now
But note that this is just an example. It has the following problems:
- It always uses Sheets(0)
- It writes the values to fixed cells (A1, B1, A2, B2, A3, B3), possibly overwriting the CSV data
- The dates are not nicely formatted as real dates
Hi louic,
I'am using your method on NeoOffice (3.4.1.) Calc on mac os x 10.7 but no success.
I have to spreadsheets SP1 and SP2.
I added the macro to SP2.
In SP1 in sheet "Transitoria" in cell A1 is text "YES!".
In SP2 in "Sheet1":
- in cell A2 i have the button added as you describe. Macro attached to event [Mouse Button Pressed].
- in cell K35 i have a link: ='file:///Users/erik/uyc-boekhouding/2014/2014-kas-bankboek.ods'#$Transitoria.A1
When i change the text in SP1 (+save) and hit the button in SP2 nothing happens.
When i run the macro step by step in the macro editor. The while part is skipped.
So it looks like the enumeration called eNum is empty.
So it looks like no links are found in SP2….
But there are definitely links in SP2 …
And updating through Edit > links > update does work.
Any ideas?
Thanks for your solution.
This is beautiful and easy. So, we can update data as csvfile is modified.
But I also want to write data to csvfile on pressing ctrl+s or making a separate button.
Can you guide me how... and which option would be easier.
You should be able to simply use the Openoffice/Libreoffice menu to save the data as csv. If you want to automate this: start recording a marcro, save the file, stop recording the macro. That's all.