[Solved] Import data from spreadsheet by index

Discuss the word processor
Post Reply
adampultz
Posts: 2
Joined: Tue Apr 06, 2021 9:17 pm

[Solved] Import data from spreadsheet by index

Post by adampultz »

I am just starting out with OpenOffice and have one goal:
For the co-op in which I live, I need to create a solution for importing data from a spreadsheet into a text document.
I have a template letter to send to creditors letting them know how much money we owe them.
Instead of manually putting in creditor name and amount owed, how would I pull these values from a spreadsheet into a text file?

For example:

Dear [creditor name],

we owe you [amount owed].

Best regards, xxxx

I have all the information in a spreadsheet. How do I tell the text file to pull the value from A3 for creditor name and B3 for amount owed?

Thank you, Adam
Last edited by Hagar Delest on Thu Apr 08, 2021 9:07 am, edited 1 time in total.
Reason: tagged solved.
OpenOffice 4.1 on MacOS 10.15
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Import data from spreadsheet by index

Post by FJCC »

You can do this semi-manually fairly easily. The first step is to make a Base document that connects your Writer documents to the spreadsheet.
Select the menu File -> New -> Database.
Select Connect To An Existing Database and then set the drop down list to Spreadsheet. Click Next.
Browse to the Spreadsheet location and select the file. Click Next.
Do register the database but you do not have to open it for editing. Save it somewhere convenient but you will not have to work with it directly.
Open a new Writer document and put in the text you want, leaving blanks where you want data from the spreadsheet to appear.
Press the F4 key to view the available data sources. A pane will open just above your Writer document. On the left it will show the registered Base files. Click the + next to the file you just saved and then click the + next to the Tables folder to see the data sheet.
The data source pane will now show some of the data in the spreadsheet. Drag the column headers, not the data itself, to the locations in the Writer document. The entries will appear as the column names enclosed in < >. See the attached image.
Once the fields are placed, click on the left edge of a row of data and then click the Data To Fields icon, which is highlighted in the attached picture. The data from that row will appear in the Writer document.

You can make many letters at once using a mail merge. You can do that through the menu File > Print, which will ask you if you want to make a form letter from your document and then allow you to choose multiple records to print.
Writer_Spreadsheet_Data.jpg
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
adampultz
Posts: 2
Joined: Tue Apr 06, 2021 9:17 pm

Re: Import data from spreadsheet by index

Post by adampultz »

Thank you so much for the quick response. This works.
OpenOffice 4.1 on MacOS 10.15
Post Reply