Opening CSV or Text Files as Calc Spreadsheets--and Vice Versa

When I worked at Sun, there were a few things people always asked, that were hard to figure out.

- How do you print the same rows or columns on every page of a spreadsheet? (See this post. )

- How do you print handouts for a presentation? (See this post.) 

- How do you get a blank paragraph line above a table that's at the top of a document? (You now simply have to press Return in the upper left cell of the table.)

Another very common question was:

How in the world do you open a perfectly good .txt  or .csv file (comma-separated values) in a spreadsheet? Basically, you've got data in rows and columns, but separated by tabs or commas rather than columns in a spreadsheet, and you want it in a spreadsheet. Comma-delimted files are a common way to get data out of a spreadsheet or database and into another.

The trouble is, in OpenOffice.org, if you just choose File > Open, the .txt or .csv file opens in Writer. Not what you want.

   

 

Here's how to open it in a Calc spreadsheet.

 

Opening a .txt or .csv File in a Calc Spreadsheet

1. Start OpenOffice.org. You can be in Writer, Draw, Calc—it doesn't matter.

2. Choose File > Open. (Click the picture to see a larger version of it, if you want.)

3. In the File Type list, select Text CSV. It's about a third of the way down the list of types, or you can click in the file type list and press T four times.

4. The window should now look like this, so just click Open.

5. You'll get a window where you specify how the file was created: what separates the data into columns, etc. (Click the picture to see a larger version, if you want.)

6. Also in the same window, if you have date-format data or other formats you want to specify, you can do that column by column.

7. When you're done, just click OK. You'll see the data in a spreadsheet.

8. You'll want to save it as a spreadsheet at some point. Choose File > Save As and select the standard OpenSpreadsheet .ods format.

9. Click Save.

 

 

 

Saving a Calc Spreadsheets as a .txt or .csv File

1. Let's say it's the other way around—you've got a spreadsheet and you want it to be in a nice neutral .txt or .csv file. Be sure the file is open.

 

2. Choose File > Save As and select the same Text CSV format in the file format list. This time you can click T just once.

3. Click Save.

 

4. You'll probably see this message. Click Yes.

 

 

5. In the next window, specify the type of character you want to separate columns, commas or tabs or another character.

6. Click OK; you're done.

 

---

Tags

OpenOffice.org    open office    OpenOffice.org training    open office training   open source   open source training   Office 12  Linux   comma-separated   text   databases  calc  spreadsheets    csv   

Comments

[Laurent Godard]

Thanks for this detailed article

As CSV file extensions are usually customized by "users", depending on their origin, one often need to adapt OOo so that it recognize them automatically as CSV files

Here are the instructions to setup an extension to be opened as a CSV file by default
http://blogs.nuxeo.com/sections/blogs/laurent_godard/2006_02_24_enhancing-ooo-calc-csv-file-recognition

Laurent

[graeme sprigge]

Ok, here's a question. How do I convert a single column of address into a .csv file.

i.e
Bill Blogs
31 Smith St
Maryville
Illinois 23432

John Sneddon
23 Gool St
Karaba
Mandalay 23442

Into
Bill Blogs , 31 Smith St, Maryville, Illinois 23432

Either that or how do I automate a whole column of addresses vertically into a separate column for each address component which I can then make into a csv.

[timothy rank]

How do I convert a single column of
addresses like this one below into .csv file. Owner: Sue S Address:123Main Street Kalamazoo,Mi.12345 How do I eliminate the titles "owner","address"

[Solveig]

Hi Timothy,

So the text has titles, like this?

Owner Address
Sue 123 Main
Bob 406 Pearl

I'm not sure why you'd want to eliminate them since they're useful and not actually included in the data if you create a database from this.

Once you create the spreadsheet, you can just delete the row that the titles are in.

[Solveig]

Hi Graeme,

You've got a bit of a tough situation there. If you want to just search for carriage returns and do it manually, see this entry.
http://openoffice.blogs.com/openoffice/2005/12/finding_and_rep.html

If you possible, figure out something that all the first, second, or third lines have in common (like "St."), search for it along with the carriage return, replace it with itself plus a space.

[Simon]

thanks for this, v.helpful :D

[Herby]

I followed the instructions upto step 4. However I do not get the window as displayed in step5, I get a window with 3 fields - an import into Writer.

Thanks
Regards

Herby

[Dave Satwicz]

this worked perfectly for me

[Lino Alphonso]

Thanks,

Your information was really helpful and worked perfectly for me

[tomer]

hi thank u! uv been great help
and may i say ur beatiful too

[Jeremy Kneebone]

Thanks very much for your help. I'm not a big Microsoft fan so I picked up OpenOffice.

I never needed a spreadsheet program all that much until recently. I had always fumbled around with excel or found someone to help me get a basic thing done when needed.

Your tips have helped me make the connections between what I did know in excel and how to use and surpass that in the calc program from Open Office. Thanks.

[gianni]

First of all, thank you very much for your helpfull infomation.
Even though I'm not able to open the database of my monthly calls that I download from my mobile company account;
It works fine with excel but not with Openoffice.
If you want I can send it.

Bye, Gianni

[Solveig]

Hi Gianni,

What type of database is it that you download? Text csv, Access, spreadsheet, ?

Solveig