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

[Jesse]

This post is quite helpful and over two years later. One would think they'd fix the UI. Never, ever, should

calc->File->Open perform writer->File->Open

This smarter-than-the-average-user annoys more than helps.

[gioby]

The problem is when you want to open a csv file which has an extension different than csv or txt.
In this case, you won't be able to use the trick you have described in this post, because if you select 'Files of type'->CSV it won't list your file.

[Chris]

Thanks, but this does not work with the latest OO. Instead of the import dialog above, I just get an ASCII Filter Options dialog. Then no matter what I choose there, it opens the file in Writer instead of Calc.

Looks like I'll have to buy MS Office after all.

[Solveig]

Hi Chris,

>>Looks like I'll have to buy MS Office after all.

No, no! ;> In the 3.0 version of OOo it's even easier you don't have to specify the csv file format. I'll bet that's not a real CSV file you're opening.

[RXT]

This didn't work for me. The CSV file still opened as a text file. I imported the file by inserting the sheet (menu: insert > insert sheet from file).

http://inpics.net/tutorials/calc2/graphics/data32g2.gif

[Dave]

Hi,
I would like to save an OO calc spreadsheet in CSV
format, and have the formulas saved as well. In excel, if a cell
with a formula is formatted Text, then the formula is
output to the csv file, and correctly interpreted when
read in to either excel or OOcalc. I can't see how to get
OO to output the formulas?

Thanks,
Dave

[Thomas Pilkington]

Is there a way to set a default for step 5. My users open up the same .cvs file many times and would like to skip hitting OK on this screen everytime.

Thanks,

Thomas

[free pass4sure]

Thanks for this detailed article.Your information was really helpful and worked perfectly for me.

[Herold]

Is there a way to reveal codes in OOo Writer. By that I mean showing on screen a symbol showing the location of, and the identification of an otherwise invisible character, for example, a carriage return. This is an option I find most useful in Corel's WordPerfect. I would rather use OOo Writer, but lack of "reveal codes," (or my inability to find out how to access it) is one of the things standing in the way.

[Randu]

Is there a way to save an open office spreadsheet using a mac in an ms-dos csv fle??

[Caley]

Is there a way to set your csv parameters as a default so that every time you open a csv file you won't have to set your separator or field options?

[Emiliano]

thanks a lot, angel! you saved my life!

:-)

[Ken Shoufer]

Using your method, I tried opening a ".txt" file that had values separated with commas and it still launched "Writer". When I changed the extension of the file to ".csv" the "Text Import" dialog box come up. I am using version 3 of OpenOffice. Maybe they changed it since you wrote this post.