OpenOffice Calc FAQ – File handling

  • How do I open a tab-delimited file in OpenOffice.org Spreadsheet? What if I have a different type of delimiter?
  • My formula from an Excel worksheet doesn’t work!
  • Is it possible to open a Microsoft Excel file that is protected by a password in Calc?
  • How do I insert external data into an existing Calc spreadsheet file?
  • Why does Calc refuse to open my .txt file?
  • How do I output my spreadsheet data as an ASCII, delimited text file?
  • A large Calc spreadsheet was converted from another application. Some of my rows are missing! What happened?

How do I open a tab-delimited file in OpenOffice.org Spreadsheet? What if I have a different type of delimiter?

 

First, if your ASCII file is not already named with a .txt extension, rename it that way.

  1. Select File > Open from the main menu and browse to find and select the file.
  2. Select File type: Text CSV (.csv; .txt)
    Note: This choice is near the bottom in the spreadsheet file types section of the list.
  3. Click Open.
  4. In the dialog that appears next, select the Separator options.
    These are the characters or methods used in the file to separate the fields of data. The same methods must be specified in this box as those used in the file to import the data into a spreadsheet. After selecting the separator type, a preview of the data will be displayed in the Fields section. If the data visually lines up in columns, then the correct separator has been selected. If not, a different separator type may be used in the file. The goal is to match the correct character used as a separator in the file, so that the data will line up nicely in the visible cells.
  5. When the data lines up, click OK.

Hints:

  • The characters used as a separators and delimiters will be visible, if you open the .txt file in Writer and enable the hidden characters (View > Nonprinting Characters).
  • If your file still only opens in Writer, check if it doesn’t contain illegal characters, e.g. null characters. They will show as (rows of) #’s in Writer. Delete these illegal characters (in Writer), save (a copy of?) the file and reopen this in Calc, as “Text CSV”.
  • Use Windows Explorer to find subject file i.e. XYZ.TAB, then click Right on this file and use Open with … and select OO Calc as the program to open it.
    OO will now show the Separator Options window. Select Tabulator.

My formula from an Excel worksheet doesn’t work!

 

This can be caused by many reasons, with the most common reason being that OpenOffice.org uses semi-colons (;) between arguments, instead of commas (,) like in Excel

Is it possible to open a Microsoft Excel file that is protected by a password in Calc?

 

1.1.x

With 1.1.x releases of OpenOffice.org, it is not possible to do this directly. Such a file can be opened from Calc only if you remove the password using the original application.

 

2.x

2.x versions of OpenOffice.org, will open the password protected file when the correct password was entered.

How do I insert external data into an existing Calc spreadsheet file?

 

2.x

You can insert tabular data from an external files into an existing Calc spreadsheet file as follows. A new sheet will be created for the inserted data.

  • Select Insert – Sheet From File and select the file with the data
  • If you have selected a text file with values separated by delimiters you will see an import dialog to set the corresponding options

Why does Calc refuse to open my .txt file?

 

A text file with tabular values must be imported into Calc using .csv format. You cannot open it by double-clicking on the file name, unless it has the .csv extension, since a text file will automatically open in Writer.

If you want to open a .txt file with tabular data using Calc:

  • Rename the file so that it uses the .csv file extension.

or

  • Select File – Open from the menu and select Text CSV from the File type list.
    This is quite a way down in the list so it is easy to miss. An easy way of navigating there is to press Tseveral times when inside the list field until the filter name comes up.

How do I output my spreadsheet data as an ASCII, delimited text file?

 

  1. Select: File – Save As… from the main menu
  2. In the Save as dialog that appears, select File type Text CSV (.csv; .txt) from the list of spreadsheet file types.
    Press T several times in the list to quickly jump to that entry.
  3. Click to enable the box next to Edit filter settings.
  4. Click Save.
  5. In the Export of text files dialog box that pops up, enter the field and text delimiters of your choice.
    The Text delimiter is the character that will surround any text entries in your spreadsheet, to keep each phrase together as an entity, when the file is exported into ASCII format.

A large Calc spreadsheet was converted from another application. Some of my rows are missing! What happened?

 

OpenOffice.org spreadsheets supports a maximum of 65,536 rows (32,000 in releases 1.1.x). Spreadsheets converted from other applications that contain more than 65,536 rows will be truncated.

Notes:

  • Split large spreadsheets from other applications into smaller worksheets, so that each has fewer than 65,536 rows prior to converting them. A range containing fewer than 65,536 rows will convert correctly in OpenOffice.org 2.x