Open Office - parsing text into spreadsheet ?

In the good old days Lotus 123 had a great feature for parsing a string of text into individual cells. I think it was Data.Parse or something equally obvious.

Upon moving to Excel it took me a while but I eventually found the equivalent under something like slightly relevant yet obscure like Tools.TextToColumns Earlier versions of Excel at least had a 123->Excel key stroke converter which saved a lot of time at the start.

Now I have moved to Open Office and I can't find the thing again. I know it must be there. It used to be possible to force Excel to parse anyway by opening plain text files in Excel and getting it to bring up an import wizard which allowed you to specify where the column breaks were but Open Office is now so smart that it reckons you actually want to open the file in the word processor even though you start to open it in the spreadsheet. I have F1'd in Open Office (v2.0) but can't find anything of use.

Anyways, given a line like this :
31-JAN-05 CUSTOMER_A €15000
Does anyone know the commands to convert the 3 different fields into individual cells ?

Cheers,

z

With both Excel and OpenOffice I've always found that simply opening the text file containing formatted data information (e.g. CSV - comma separated value or tab delimited etc.) presented me with a dialog allowing me to specify the details of the import from text file.

Hmm, doing file.open and choosing a text file from within the spreadhseet in Open Office just caused it to open in the Word Processor for me. Maybe I will try renaming it to something other than .txt and see if that works. The data in question is actually space delimited, but I could probably coma delimit it from the source application.

Will try and see what happens.

Cheers,

z

OK - this should work:

• Start -> All Programs -> OpenOffice <version> -> Open Document
• Files of type -> Text CSV (*.csv, *.txt)
• Select the structured text file to open
• Use the Text Import - [csv.txt] dialog box to select the appropriate import criteria

I don't know if this is for v1.x of Open Office, but under Programs.OpenOffice in v2.x there is no Open Document option - just the individual applications.

I tried a few things and basically opening a text file called file.xls caused it to open in the word processor while opening the exact same file except calling it file.txt caused it to open correctly in the import wizard in the spreadsheet.

Cheers,

z

Sorry - that was for OOo 1.1.4. I haven't tried OOo 1.9+/2.0 beta yet. Maybe they've overhauled the interface again (like when the moved from a single desktop interface to discrete applications a few years back)?

I tried a few things and basically opening a text file called file.xls caused it to open in the word processor while opening the exact same file except calling it file.txt caused it to open correctly in the import wizard in the spreadsheet.

Click to expand...

I don't understand - I thought that you said earlier that this did not work?

My original problem seems to centre around the file extension - if I take a plain text file and try to open with two different file extensions I get two different results.

My first post was prompted because whatever extension I was using (which I can't remember - it may have been no extension) didn't trigger the import wizard, whereas I have now discovered (with your help) that using .txt and clicking on File.Open in the spreadsheet will cause it to open in a spreadsheet, while using .xls and clicking on File.Open in the spreadsheet will cause it to open in the word processor.

z