importing data in text format to spreadsheet

[Derek Andrews]

I am trying to find some way to import data from census transciptions into a spreadsheet or database. Currently it is in .rtf format and looks like this:

1
POTTS William 37 Mealman Poole Dorset
Sabina 36 Lydlinch Dorset
Ellen 11 Scholar Knook Dorset
William 9 Scholar Essex Borcham
Mary 7 Scholar Borcham
William 5 Scholar Borcham
WOODWARD William Servant 21 Labourer Croscombe, Som.
2
FIELDER Thomas 50 Married Gardener Shepton Mallett, Som.
Eliza 58 Accountant Shepton Mallett, Som.
William 25 Shepton Mallett, Som.
John 22 Tailor Croscombe, Som.
3


I am trying to get this split up into fields for statistical analysis. I don't mind going through the text file and adding commas like this:

1,POTTS, William,, 37, Mealman, Poole Dorset
,,Sabina,, 36, Lydlinch Dorset
,,Ellen,, 11, Scholar, Knook, Dorset
,,William,, 9, Scholar, Essex Borcham
,,Mary,, 7, Scholar, Borcham
,,William,, 5, Scholar, Borcham
,WOODWARD, William, Servant, 21, Labourer, Croscombe Som.
2,FIELDER, Thomas,, 50, Gardener, Shepton Mallett Som.
,,Eliza,, 58, Accountant, Shepton Mallett Som.
,,William,, 25, Shepton Mallett, Som.
,,John,, 22, Tailor, Croscombe, Som.

Or, preferably with tabs so that I can check everything is in the right place.

Problem is, how do I export this to a format that can be imported to spreadsheet or database? I've looked through the help but haven't found anything that helps.

[rotomano]

you do no need to add commas, as spaces can be field delimitators, if you specify that when you import the file.


Search calc help files for "Text Import"

To access this command in calc click:
Menu File - Open - select under Files of type: "Text CSV" and select your file and a dialog box opens like this one to import your data in a spreadsheet

before doing any of that you need to convert your rtf file into a text file, by opening it with writer and doing a File->Save as ... TEXT

[Derek Andrews]

Thanks. I think that will do it. It was selecting the file type that I missed. There is still some weirdness going on, but I think I can work around it.

[noranthon]

You could try opening it first in Writer and use Tables >Convert text to table. Then copy and paste into Calc as "formatted text" using the drop-down option list with the Paste icon.
_________________
search forum by month

[Derek Andrews]

Thank you Noranthon. That looks like it will work better. The table makes it easier to check and manipulate the data, and the transfer into calc is pretty solid. For some reason, when I try rotomano's method today, the dialogue box doesn't open and it opens it in Write again. I must be doing something different than when I first tried it, but I don't know what.

Thank you both for your help