[Solved] Importing Spreadsheet columns into text file table

Discuss the word processor

[Solved] Importing Spreadsheet columns into text file table

Postby MarkinPA » Sat Jul 14, 2018 7:28 pm

Hello

I have an open office spread sheet with 5 columns and 263 rows.

I've created an open office text document and created a table of 5 columns and 263 rows.

I'm not able to copy the spreadsheet entries int the corresponding table.

Help!

Thank you
Last edited by Hagar Delest on Sun Jul 15, 2018 9:17 pm, edited 1 time in total.
Reason: tagged [Solved].
Open Office 4.1.5 Win 8
MarkinPA
 
Posts: 4
Joined: Sat Jul 14, 2018 7:24 pm

Re: Importing Spreadsheet columns into text file table

Postby RoryOF » Sat Jul 14, 2018 7:59 pm

Will it copy on a column by column basis? What happens if you select a number of rows to copy less than 255?
Apache OpenOffice 4.1.5 on Xubuntu 18.04 (mostly 64 bit version) and infrequently on Win2K/XP
14 October 2016 was Pooh's 90th birthday
User avatar
RoryOF
Moderator
 
Posts: 26913
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Importing Spreadsheet columns into text file table

Postby RusselB » Sat Jul 14, 2018 8:33 pm

From the Writer help file
Inserting Data From Spreadsheets
Use the clipboard to copy the contents of a single cell. You can also copy a formula from a cell into the clipboard (for example, from the input line of the formula bar) so that the formula can be inserted into a text.
To copy a cell range into a text document, select the cell range in the sheet and then use either the clipboard or drag-and-drop to insert the cells into the text document. You will then find an OLE object in the text document, which you can edit further.
If you drag cells to the normal view of a presentation document, the cells will be inserted there as an OLE object. If you drag cells into the outline view, each cell will form a line of the outline view.
When you copy a cell range from OpenOffice Calc to the clipboard, the drawing objects, OLE objects and charts within this range are also copied.
If you insert a cell range with an enclosed chart, the chart will keep its link to the source cell range only if you copied the chart and the source cell range together.
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 4452
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Importing Spreadsheet columns into text file table

Postby Villeroy » Sat Jul 14, 2018 9:38 pm

The easy way to drag raw table data into Writer or Calc with a lot of preparation:
With a spreadsheet as storage medium, I recommend to separate dedicated list range(s) by selecting the list area and calling menu:Data>Define...
Type a database range name, click [More Options] and choose if that list has column labels or not.
Save the modified spreadsheet to disk.
Now we declare this spreadsheet as a data source for Writer or Calc documents:
File>New>Database...
[X] Connect to existing database
Type: Spreadsheet
Specify the spreadsheet document
[X] Register the database
Save the database document.
Nothing has been copied, imported, converted. All your data are still in the spreadsheet. The database is just another view on that spreadsheet.
The used area on each sheet is displayed as a database table and you should find your named list range as well. Database tables (sheets) that make no sense in this context can be hidden via menu:Tools>Table Filter...
menu:Insert>Query (Design View)... choose your named list and then double-click the column in question. The "Alias" lets you give another name to the column and you can choose to sort the column.
Save the query in the database. A query is just another specific view on a database table, in this case the view includes one specific column only.
Save the database.
------------------------------------
From now on you have your pseudo-database (a spreadsheet connection is not a true database) in the data source window of Calc and Writer (menu:View>Data Sources).
Simply drag the query icon from the left pane into Writer or Calc and see.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25555
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing Spreadsheet columns into text file table

Postby Villeroy » Sat Jul 14, 2018 10:39 pm

How to merge a table with an additional column:
1) Import the single column one way or the other so you have 2 Writer tables.
2) Insert/append a new blank column to the target table.
3) Copy the single-column table.
4) Select the first cell of the blank new column and paste
5) Delete the one-column table

From a range of spreadsheet cells you can get a Writer table without using a database connection:
Copy and paste-special as plain text
Select the plain text and menu:Table>Convert>Text to table...

If you frequently have to import subsets of data from a spreadsheet, you are better off with a registered data source.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25555
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing Spreadsheet columns into text file table

Postby MarkinPA » Sun Jul 15, 2018 1:39 am

Villeroy wrote:How to merge a table with an additional column:
1) Import the single column one way or the other so you have 2 Writer tables.
2) Insert/append a new blank column to the target table.
3) Copy the single-column table.
4) Select the first cell of the blank new column and paste
5) Delete the one-column table

From a range of spreadsheet cells you can get a Writer table without using a database connection:
Copy and paste-special as plain text
Select the plain text and menu:Table>Convert>Text to table...

If you frequently have to import subsets of data from a spreadsheet, you are better off with a registered data source.


Hi; thanks for the reply. Since I'm only doing this once, I don't feel I want to worry about OLE (no idea what that is) or connecting one document to another.

I tried your way, but the "text to table" is greyed out. I click on the copied text, Table->Convert is OK, but "Text to table" is greyed out!
Open Office 4.1.5 Win 8
MarkinPA
 
Posts: 4
Joined: Sat Jul 14, 2018 7:24 pm

Re: Importing Spreadsheet columns into text file table

Postby MarkinPA » Sun Jul 15, 2018 1:41 am

RoryOF wrote:Will it copy on a column by column basis? What happens if you select a number of rows to copy less than 255?


No.
Open Office 4.1.5 Win 8
MarkinPA
 
Posts: 4
Joined: Sat Jul 14, 2018 7:24 pm

Re: Importing Spreadsheet columns into text file table

Postby MarkinPA » Sun Jul 15, 2018 2:09 am

Edit->Paste Special->Formatted text(RTF)

And the table is there!

I then use the Table Properties to adjust the formatting.

I have no idea why that didn't work before, but I have my table!
Open Office 4.1.5 Win 8
MarkinPA
 
Posts: 4
Joined: Sat Jul 14, 2018 7:24 pm


Return to Writer

Who is online

Users browsing this forum: Google [Bot] and 43 guests