OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

importing data in text format to spreadsheet

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Derek Andrews
Newbie
Newbie


Joined: 10 Apr 2007
Posts: 3
Location: NS Canada

PostPosted: Tue Apr 10, 2007 6:09 am    Post subject: importing data in text format to spreadsheet Reply with quote

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.
Back to top
View user's profile Send private message Visit poster's website
rotomano
OOo Enthusiast
OOo Enthusiast


Joined: 13 Dec 2006
Posts: 198
Location: Greece

PostPosted: Tue Apr 10, 2007 8:57 am    Post subject: Reply with quote

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


Back to top
View user's profile Send private message
Derek Andrews
Newbie
Newbie


Joined: 10 Apr 2007
Posts: 3
Location: NS Canada

PostPosted: Tue Apr 10, 2007 11:14 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Visit poster's website
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Tue Apr 10, 2007 4:06 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Derek Andrews
Newbie
Newbie


Joined: 10 Apr 2007
Posts: 3
Location: NS Canada

PostPosted: Wed Apr 11, 2007 3:09 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group