Unlike Microsoft Excel, OpenOffice.org 2.0 Calc doesn’t have a built-in “text to columns” feature, which is hard to live without once you’re used to it. OpenOffice.org has an extensible add-on architecture, and someone has written a “text to columns” add-on, but installation may be confusing. In this article I’ll explain how to install the add-on.
I hope this feature will be added into the office suite at some point. Oddly, it seems to already be implemented, but not in the way it’s needed. Writer has a text-to-columns feature already, and Calc’s Open process has what looks to me like the needed functionality too — when opening a delimited text file, it brings up a dialog that does exactly what I’d do with the text-to-columns feature in Excel.
Update
Here is something I’ve noticed under GNU/Linux since writing this article: if I’ve copied text to the primary selection, for example by highlighting it in a terminal window, I can get OO.org to “text to columns” the text just by middle-click-pasting it into the spreadsheet. Instead of actually pasting it, this opens up the “Text Import (Pasted Data)” dialog, which lets me choose delimiters, etc — exactly what I need. (Of course, it’d be nice if it were even smarter and auto-detected that for me). So far I have not found any other way to cause this dialog to appear, which is puzzling.
Getting the add-on
The add-on is available through SourceForge and the OOoMacros site, which is both a source of add-ons and help and examples for those wishing to make their own add-ons. You can download text to columns for Calc here. There are actually two add-ons, but apparently the “Text2Columns fixed width” one is redundant, since the “Text to Columns” one does that too.
How to install an add-on in OpenOffice.org
This is the part I found confusing. The difference between macros and add-ons in OpenOffice.org isn’t very clear to me, and the tools to manage them aren’t either. I went down the wrong path with macros until I realized this isn’t a macro, it’s an add-on. Then I tried to learn how to install an add-on. I found lots of references to something called unopkg, but nothing about where it’s installed. It wasn’t in my PATH. I searched my filesystem and found it in /usr/lib/openoffice/program/unopkg. Then I ran it:
xaprb $ /usr/lib/openoffice/program/unopkg gui
Lo and behold, it brought up the same dialog I can access through the Tools > Package Manager menu entry. All that searching for nothing. I recommend not running it from the command-line; just run it through Tools > Package Manager!
Installing and using the package
Once I found it, installing the package was as easy as selecting the My Packages entry and pressing Add.. to browse for the file. It installs itself and shows up under the Tools > Add-Ons menu. This is really easy to do, but it took me a while to abandon my misdirected efforts to install it as a macro.
Here’s a screenshot of what it looks like (click the screenshot for a full-size look).
So far it has worked fine for myself and my coworker. I hope it’s useful to you too.
Update For those of you using GNU/Linux, Gnumeric has a built-in text-to-columns converter that’s very nice. Gnumeric also loads much faster and runs with much less memory than OpenOffice.org.
Technorati Tags:No Tags
These directions were right on the money and worked perfectly! Thanks!
I am hoping to use OpenOffice as a complete substitute for MS Office, but I ran into a critical problem. I often create spreadsheets in other programs such as TimeStamp (for my web design business) and then have to export as a text file (.txt) and then import into Excel and convert into an .xls file, so I can edit the file and supply it to the client. Unfortunately this conversion feature isn’t built into OO Calc as you describe. I downloaded TextToColumns as described, and added three different files to the Package Manager
“AddonConfiguration.xcs”, “AddonConfiguration.xcs”, and “Addon.xcu”. All three are described as being enabled in the Package Manager. I have no idea what these files do. They are the only files in the download of the correct media type. They are the only ones that can be added to the package. My question is about the next step(s), how to convert a .txt file to an .xls file in OpenOffice. I have to do this conversion all the time and I really don’t want to install Excel again. Any tips on how to proceed would be gratefully received.
This is a great feature. I’d just like to add that after I installed the add-on, it was not available under windows until I re-opened the file I had. Apart from that, all’s well and thank you for taking the time to discover and work out how to use this neat feature. Yes, let’s hope OOo make it standard.
I am new to OO.org. I followed the directions above and all appears to have worked as you described in Windows XP. Now, for the dumb question. How do I use this add-on? After I select my column with the text, where is the add-on in the menu structure so I can execute the conversion of text to columns? Thanks for your help!
Hi Reagan, you should see it at “Tools > Add-Ons > Text to columns.”
Thanks for the answer. It was my fault for not restarting Calc. Once I restarted, it was there. Thanks!
Thanks for this. The package works great and should definitely be in future version of Calc, but I had also found it confusing to add.
In addition to these instructions, it might be useful to note that the file you select after pressing “Add” is the zipped file, which in my version is “TextToColumns.uno2005-04-19.zip”. I had started by unzipping this and then looking for the right file in the unzipped folder, which doesn’t work.
Many thanks. The Add-On was easy to find, install and use. Remind the users that all they have to do is download the zip file, and the Package Manager will handle the rest, no need to “unzip”. This Ad-On works even on the Spanish versÃon of OO Calc, but the user will have to get use to see the option in English.
I have just been unsuccessful in attempting to install TextToColumns on Portable OpenOffice, which I am running from a USB hard drive. pOOo crashes immediately (bug report filed). It appears that the addon completed the unzipping but the location looks odd to me:
M:\Program_Files\PortableOpenOffice\settings\user\uno_packages\cache\uno_packages\3E3.tmp_\TextToColumns.uno2005-04-19.zip\
Can someone advise me on where the package would unzip on a standard OOo?
TIA,
Will
Please disregard earlier post. I have since updated from OOo v2.02 to OOo v2.03 (both portable) and the TextToColumns package installs and works correctly under the newer version.
This is super it proved to be a great help. Otherwise I had to use some others sys to convert text to columns. Looking forward to more tips from you.
I tried it and got a NoSuchElementException error when it tried to BasicLibraries.LoadLibrary(”Tools”)
I commented that out and it got another error two lines down so I gave up.
I tried it first in 2.0 and then I downloaded 2.04 RC1 and had the same problem.
I need to deploy this in a network environment, so I can’t make small changes in everybody’s computer. Is there an easy way to fix this?
Well I downloaded the file and read the install…(useless - in a word. Would it kill anyone to actually include instructions on how to install. As in what file should be added?)… There are three files that are “installable” in the download. I have tried “adding” each of them….After restarting I get the add-ons option for text to columns, only it does not do anything….No dialog, nothing…I select a column of text choose the text to columns add-on and then …nothing….
So did I miss the secret handshake or something?….
Hey Big Al,
To get the windows package to function, I downloaded the .zip file and selected the .zip file as the packet to add, instead of the individual files. Working like a CHARM!!
I can’t live without “text to column” so this has been a serious life saver.
Thank you XAPRB for this excellent informaiton! Much appreciated!
ZERD
Downloaded TextToColumns-20061118.uno.pkg and installed on OOo-2.0.2. Works perfectly. Thank you.
The 20061003 version I downloaded from Sourceforge.net did not install properly after repeated downloads and installations. The 20061118 version available from the author’s [of the add-on package] website (that can be found in the documentation provided with the 20061003 version) worked fine.
Thanks a lot!
It seems that version of 20061003 does not work with OOo 2.0.4 on ubuntu 6.10 linux (for me at least :)), but version of 20061118 works perfect.
Thanks a lot.
I downloaded the latest Version 20061003 , but it does not work on OOo 2.0.4 on FC6. It installs, with a error message, and when I try to run it, it dumps me in the debugger.
It would be great if they could add this functionality to the core program.
Anyone got any suggestions for a command-line option to turn a word file into a csv file? I have tables formated with spaces (!) to align them. So all I want to do is to take the text, and replace anywhere there is more than one space with a comma and hey presto I’ll have a .csv. Must be possible with antiword, sed, awk etc?
cheers
M.
Thanks Andy, August 17th, 2006!!!
That makes the package easy to install. It should also be noted that the clicks are now Tools>Extension Manager.
This page helped me a lot. I do a lot of conversion of text to columns and this was a lifesaver!
I got gnumeric text to column to work once, but forgot what I did! Can someone explain the procedure? Besides the smaller footprint, gnumeric has a nice text import druid. I have been messing around with saving tables as html from gnumeric and calc, and generally like gnumeric better for that.
For working with the html output from gnumeric after it has been further edited/marked up,
I discovered oo writer would apply formatting like grid lines to table cells without losing the edits applied after gnumeric save to html.
It’s under Data:Text To Columns in 1.7.0. I really like Gnumeric. It is better at text import than any other spreadsheet I’ve seen by a large margin. It auto-detects most things and just does the right thing. For example, if I save the tabular text from a MySQL query, which is surrounded by pipes, dashes and plus signs, and open it with Gnumeric, it just magically figures out where the columns are and even omits the column separators. It blows every other spreadsheet away in this regard.
New solution! No add-on required!
There’s a built-in function (in OO 2.1 Calc at least), but it is hard to find.
Go to the Insert menu and pick Sheet From File, then select your text file.
You will then see the options to choose delimiters and column types, etc.
Thanks for the tips and the discussion. For what it’s worth, TextToColumns-20061003.uno.pkg.zip works fine in OO 2.0.2. I was initially concerned by not finding [Tools][Add-on] as an option, but after adding the downloaded zipped file to My Packages with the packet manager, and restarting OO, the Add-on option appeared and allowed me to parse the column just like Excel.
I’m not the world’s greatest techie. Your advice was exactly what I needed to make this change. And, it’s working for me now (OO ver 2.1). I really need text-to-columns to work for me if I’m going to avoid using EXCEL.
Thank you.
Thanks to Paolo Mantovani for the excellent add-on to calc (I’m amazed that it isn’t included as standard) BTW the 20061118 version worked for me. Hint to Window$ users just download the .pkg file and add it to your extensions as-is, easy peasy!
No Add-Ons needed! A more easier approach than anything described here above: in OpenOffice v2, just copy the text to be inserted into a buffer (Ctrl C) and when in the Spreadsheet, find the cell where the insertion is to be made and right click on your mouse: choose “Paste Special”. By default the “Unformatted text” will be selected. Click “OK” and the insertion menu will pop up which will let you insert text to columns using delimiters. Tested on Red Hat Linux and Windows XP.
I’m still strggling with this.
I am running ver 2.1.
I don’t have Tools > Package Manager, I have Tools > Extension Manager.
If I add the .zip as an extension and then close and re-open Calc, nothing appears.
If I instead unzip the file and add the .oxt file through extension manager, it displays a number of items including text to columns with ‘Enabled’ next to each of them (which didn’t happen with the .zip).
When I close/reopen Calc, there is still no Addon menu item available.
I added both zipped and unzipped together, and still nothing.
Using paste-special just seems to paste in the raw data for me - no insertion menu pops up.
I’m looking fo an up-to-date, foolproof, idiots guide.
I’ve just noticed that it is accessible if I go to
- Tools > Macros > Run Macro… > expand My Macros > expand Text to Columns > Click on Main > Click on Main in the Macro Name section > Click Run
Presumably that can’t be the intended means of running the macro?
Dave, I’ve got the same problem. The only way to run it is via the Tools/Macros/Run Macro… Any idea why the Addons menu does not appear under Tools?
I am running OO 2.0.4 under Debian linux.
THANK YOU THANK YOU THANK YOU - I can completely dump Excel now that I have this utility!!!
Why doesn’t stupid OOO have the excel features pre-installed?
We are talking migration from Office users to OOO, and my feeling is that OOO has much to learn from our friends in Redmond.
We need to make OOO as close as possible to Microsoft Office, simply because Office is easy because everyone is USED to it.
I am very sorry that OOO looks archaic compared to Office 2007… The features seem to follow this.
This took me a while to figure out (bear with me here).
I can run this by going the Macro route…. but it has also inserted an icon in my toolbars (I was looking for text in my menus).
For what it’s worth, to install I unzipped the file and pointed the extension manager to the installer. Then I closed the OOo quickstarter, and restarted Calc. Calc 2.3.0, 20070328, Vista Home Basic.
For a screenshot of the icon, see:
http://farm3.static.flickr.com/2231/2079920963_0918dcc7bd_o.gif
Ah Ha!!!!!
The trick to the install and source of the confusion is that you need to unzip the compressed file.
Then install TextToColumns-20070328.oxt using
Tools>>Extension Manager
Then you need to restart OO.
Then you need to highlight a column and look for the new icon on toolbar. looks like the screenshot in message above
Got it. Thanks Bryan! Still new at this so I will write the idiots guide to installing it.
1. Download add-on (link at top of this page) to your desktop.
2. Double click on the icon and extract the .oxt file to somewhere on your desktop.
3. Open up OOspreadsheet
4. On top menu click on Tools -> Extension manager and click Add when you have highlighted ‘My extension’
5. Navigate menus to the .oxt file you extracted before.
6. Restart program
7. Text to columns icon will appear in the upper left directly under ‘File’
8. Quite whining about how open and free software isn’t as good as crappy software made by one of the richest corporations on the planet.
Trev,
Thanks a MILLION!!! Your instructions were so straightforward. I couldn’t find Package Manager.
Now I cant copy and paste from the column. Did I miss somethting
OpenOffice.org 2.4.0 includes Text to Columns, so macros are no longer necessary.