Opening Another Document in Calc

You can open a variety of spreadsheets, text files, and other documents in Calc spreadsheet format.

Opening Any Spreadsheet

To open any spreadsheet, just choose File > Open and navigate to the file. To see what spreadsheets or other formats are supported, click the format dropdown list and browse a while.

Converting Microsoft Office Spreadsheets to Calc

See Converting To and From Other Applications on page 160.

Opening an HTML Table in Calc: Simple

Choose File > Open and be sure All is selected as the file type. Select the HTML file you want to open. Then switch to Text CSV as the file format in the Open dialog, and click OK.

See also Saving a Spreadsheet in HTML Format on page 519 and Sending a Spreadsheet as Email on page 520.

Inserting an HTML Table or WebQuery Using Insert External Data

Calc can automatically insert, formatted somewhat differently, an entire HTML page or Web Query, or just one or more selected tables from an HTML page. The page can be local or a URL on the Web.

1. Prepare the HTML file, if it's local. You might only be able to get Calc to recognize it if it's got an . shtml extension. It depends on what your system associates with HTML. An example is shown in Figure 18-12.

   Figure 18-12. Example of locally saved HTML file that can be inserted into spreadsheets this way

   

2. Open a new or existing spreadsheet.

3. Choose Insert > External Data. The window in Figure 18-13 will appear

   Figure 18-13. External Data window: selecting items to insert from file

   

4. Click the browse icon to select the HTML file or type the URL of the Web page. Use Figure 18-13 for guidance in making other selections, then click OK

5. Figure 18-14 shows the results of selecting the HTML_tables option.

   Figure 18-14. Inserted HTML tables, HTML_tables option

   

6. If you need to make the text wrap in each cells, select all cells, right-click, and choose Format Cells . Then click the Alignment tab, and select the Line Break option.

Importing a Text File or Spreadsheet

Make sure the text file you want to import is set up with delimiters . A delimiter is just a technical term for some kind of character, such as a tab or a semicolon, that marks the stopping and starting points of data that will be separated into different spreadsheet cells.

For example, the semicolons in the text line in Figure 18-15 allow Calc to import the data into separate cells in a spreadsheet row:

Figure 18-15. Text delimiter

Delimiters can be any character that your system supports. You can even use tab character or a space as your delimiter. Text files can also be fixed width . That means your text file doesn't necessarily use delimiters, but each "column" in your text file is a specific width that Calc can translate into a spreadsheet column.

You can set up a data source that's a text file or a spreadsheet, so getting a text file into a spreadsheet doesn't help you a great deal. However, once you've got the data in a spreadsheet it's easier to create what the data source tool reads as different "tables" (different sheets). It's also just easier to manipulate. Also, if you're having problems opening a spreadsheet in Calc, you can get it in much more easily by using text file as the intermediate format.

1. Get the source file into the right format.

   • Get the spreadsheet into CSV text format. This depends on the spreadsheet.

   • Evaluate what separates the fields in the text file. If it's spaces, that's fine as long as there aren't any spaces within each field. This is probably fine for files with numeric information. However, for an address book, where you might have a field like "910 Harrison Ave" with two spaces in it, you'll get three different columns . For that type of file, you'll probably have to go through the file and use a tab, semicolon, or other character to separate the fields. Figure 18-16 shows what happens when you import text files like that

     Figure 18-16. Bad text import with space separator and spaces between fields

     

2. Open a new empty Calc spreadsheet. Choose File > Open and select Text CSV in the file format list. This is crucial; it won't work if you don't specify that format. Just press T a few times to find it.

3. The Text Import window will appear. Select appropriate options; see Figure 18-17.

   Figure 18-17. Specifying how to import the text data

   

4. Click OK. The data will appear as you formatted it in an untitled Calc spreadsheet, as shown in Figure 18-18. Save the file as a Calc spreadsheet

   Figure 18-18. Imported data appears in Calc spreadsheet