Help transferring data

Discuss the word processor
Post Reply
erika2k17
Posts: 3
Joined: Sat Nov 11, 2017 12:22 am

Help transferring data

Post by erika2k17 »

Hi. I created a fill-able form in OpenOffice Writer which I use as my "project detail page", a page to have all my customer contact info and project info on.
Is there a way to take the data I input into the form and transfer only the data entry into a spread sheet? I would like to extract the names/address in my form into a spread sheet so I can import into Quick books faster.

Thank you in advance for your help!
Erika
OpenOffice version 4.1.1
Windows 10
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Help transferring data

Post by RusselB »

Can you provide a copy of your Writer document with some sample data.
Also, is there a reason why you want to do multiple steps (Writer -> Calc -> Quick books)?
What data are you actually wanting/needing to send to Quick books?
What file formats will Quick books accept?
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Help transferring data

Post by John_Ha »

Welcome to the forum.

If you upload a small document file (eg .odt) with your data, or a small sample page, someone will look at it to see how to extract the data. Use the Upload attachment tab below where you type - 128 kB max here or use a file share site.
erika2k17 wrote:Hi. I created a fill-able form in OpenOffice Writer which I use as my "project detail page", a page to have all my customer contact info and project info on.
It would probably better to store this data in a spreadsheet (or simple database) and forget about the form, as it will be easy to extract the data.
Clipboard01.png
Also, you can then use Mail Merge easily to extract data from the spreadsheet and create letters, forms, mailing labels, addressed envelopes etc - see Chapter 11 - Using Mail Merge in the Writer Manual.

As a new poster you will find much useful information in the Writer FAQ, the Writer Tutorials, the up to date Writer guide and the Writer Manual. When a pop-up window opens, click the Help button for extensive help on that function - it is often more comprehensive than the manual.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
erika2k17
Posts: 3
Joined: Sat Nov 11, 2017 12:22 am

Re: Help transferring data

Post by erika2k17 »

Thank you both for the information! I will definitely look at those suggestions. I am attaching a sample of the form I created.
I wanted to export information into a spreadsheet because I have quickbooks pro 2016 and I found a way to import customer information quickly if it is on a spreadsheet.

Again, thank you for your time!
Attachments
New Client Interview 2 FORM sample.odt
(11.98 KiB) Downloaded 119 times
OpenOffice version 4.1.1
Windows 10
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Help transferring data

Post by John_Ha »

I cannot find an easy way to copy all the user inserted data out of the form in one go. If I use Select All ..., I just copy the headings. The only way to copy the data seems to be to copy each field individually.
User data inserted into .odt file
User data inserted into .odt file
It would be trivially easy for someone who could write an AOO macro to pull out the data.

Alternatively any programmer (they do not need AOO skills) could extract content.xml from the .odt file and get the data. As you can see from the extract below as each bit of user data is named and therefore easily found by a string search:

Code: Select all

TextField" xml:id="control1" form:id="control1" form:current-value="Customer Fred"
DateField" xml:id="control2" form:id="control2" form:current-value="2017-11-14"
TextField" xml:id="control3" form:id="control3" form:current-value="01234567"
TextField" xml:id="control4" form:id="control4" form:current-value="fred@fred.com"
TextField" xml:id="control5" form:id="control5" form:current-value="1 High Street"
DateField" xml:id="control6" form:id="control6" form:current-value="2017-11-17"
TextField" xml:id="control7" form:id="control7" form:current-value="real date"
DateField" xml:id="control8" form:id="control8" form:current-value="2017-11-21"
TextField" xml:id="control9" form:id="control9" form:current-value="yes"
TextField" xml:id="control10" form:id="control10" form:current-value="16"
TextField" xml:id="control11" form:id="control11" form:current-value="Bill"
the relevant parts of content.xml are shown below:

Code: Select all

			<office:forms form:automatic-focus="false" form:apply-design-mode="false">
				<form:form form:name="Form" form:apply-filter="true" form:command-type="table" form:control-implementation="ooo:com.sun.star.form.component.Form" office:target-frame="" xlink:href="" xlink:type="simple">
					<form:text form:name="Customer Name" form:control-implementation="ooo:com.sun.star.form.component.TextField" xml:id="control1" form:id="control1" form:current-value="Customer Fred" form:convert-empty-to-null="true">
						<form:properties>
							<form:property form:property-name="ControlTypeinMSO" office:value-type="float" office:value="0"/>
							<form:property form:property-name="DefaultControl" office:value-type="string" office:string-value="com.sun.star.form.control.TextField"/>
							<form:property form:property-name="ObjIDinMSO" office:value-type="float" office:value=""/>
						</form:properties>
					</form:text>
					<form:date form:name="Date" form:control-implementation="ooo:com.sun.star.form.component.DateField" xml:id="control2" form:id="control2" form:current-value="2017-11-14" form:min-value="1800-01-01" form:validation="true">
						<form:properties>
							<form:property form:property-name="ControlTypeinMSO" office:value-type="float" office:value="0"/>
							<form:property form:property-name="DefaultControl" office:value-type="string" office:string-value="com.sun.star.form.control.DateField"/>
							<form:property form:property-name="Dropdown" office:value-type="boolean" office:boolean-value="true"/>
							<form:property form:property-name="MouseWheelBehavior" office:value-type="float" office:value="0"/>
							<form:property form:property-name="ObjIDinMSO" office:value-type="float" office:value=""/>
							<form:property form:property-name="Text" office:value-type="string" office:string-value="11/14/17"/>
						</form:properties>
					</form:date>
					<form:text form:name="Phone" form:control-implementation="ooo:com.sun.star.form.component.TextField" xml:id="control3" form:id="control3" form:current-value="01234567" form:convert-empty-to-null="true">
						<form:properties>
							<form:property form:property-name="ControlTypeinMSO" office:value-type="float" office:value="0"/>
							<form:property form:property-name="DefaultControl" office:value-type="string" office:string-value="com.sun.star.form.control.TextField"/>
							<form:property form:property-name="ObjIDinMSO" office:value-type="float" office:value=""/>
						</form:properties>
					</form:text>
					<form:text form:name="Phone" form:control-implementation="ooo:com.sun.star.form.component.TextField" xml:id="control4" form:id="control4" form:current-value="fred@fred.com" form:convert-empty-to-null="true">
						<form:properties>
							<form:property form:property-name="ControlTypeinMSO" office:value-type="float" office:value="0"/>
							<form:property form:property-name="DefaultControl" office:value-type="string" office:string-value="com.sun.star.form.control.TextField"/>
							<form:property form:property-name="ObjIDinMSO" office:value-type="float" office:value=""/>
						</form:properties>
					</form:text>
					<form:textarea form:name="Address" form:control-implementation="ooo:com.sun.star.form.component.TextField" xml:id="control5" form:id="control5" form:current-value="1 High Street" form:convert-empty-to-null="true">
						<form:properties>
							<form:property form:property-name="ControlTypeinMSO" office:value-type="float" office:value="0"/>
							<form:property form:property-name="DefaultControl" office:value-type="string" office:string-value="com.sun.star.form.control.TextField"/>
							<form:property form:property-name="MultiLine" office:value-type="boolean" office:boolean-value="true"/>
							<form:property form:property-name="ObjIDinMSO" office:value-type="float" office:value=""/>
							<form:property form:property-name="VScroll" office:value-type="boolean" office:boolean-value="true"/>
						</form:properties>
					</form:textarea>
					<form:date form:name="Date" form:control-implementation="ooo:com.sun.star.form.component.DateField" xml:id="control6" form:id="control6" form:current-value="2017-11-17" form:min-value="1800-01-01" form:validation="true">
						<form:properties>
							<form:property form:property-name="ControlTypeinMSO" office:value-type="float" office:value="0"/>
							<form:property form:property-name="DefaultControl" office:value-type="string" office:string-value="com.sun.star.form.control.DateField"/>
							<form:property form:property-name="Dropdown" office:value-type="boolean" office:boolean-value="true"/>
							<form:property form:property-name="MouseWheelBehavior" office:value-type="float" office:value="0"/>
							<form:property form:property-name="ObjIDinMSO" office:value-type="float" office:value=""/>
							<form:property form:property-name="Text" office:value-type="string" office:string-value="11/17/17"/>
						</form:properties>
					</form:date>
					<form:text form:name="Real Temp Date" form:control-implementation="ooo:com.sun.star.form.component.TextField" xml:id="control7" form:id="control7" form:current-value="real date" form:convert-empty-to-null="true">
						<form:properties>
							<form:property form:property-name="ControlTypeinMSO" office:value-type="float" office:value="0"/>
							<form:property form:property-name="DefaultControl" office:value-type="string" office:string-value="com.sun.star.form.control.TextField"/>
							<form:property form:property-name="ObjIDinMSO" office:value-type="float" office:value=""/>
						</form:properties>
					</form:text>
					<form:date form:name="Date" form:control-implementation="ooo:com.sun.star.form.component.DateField" xml:id="control8" form:id="control8" form:current-value="2017-11-21" form:min-value="1800-01-01" form:validation="true">
						<form:properties>
							<form:property form:property-name="ControlTypeinMSO" office:value-type="float" office:value="0"/>
							<form:property form:property-name="DefaultControl" office:value-type="string" office:string-value="com.sun.star.form.control.DateField"/>
							<form:property form:property-name="Dropdown" office:value-type="boolean" office:boolean-value="true"/>
							<form:property form:property-name="MouseWheelBehavior" office:value-type="float" office:value="0"/>
							<form:property form:property-name="ObjIDinMSO" office:value-type="float" office:value=""/>
							<form:property form:property-name="Text" office:value-type="string" office:string-value="11/21/17"/>
						</form:properties>
					</form:date>
					<form:text form:name="Shop C/S?" form:control-implementation="ooo:com.sun.star.form.component.TextField" xml:id="control9" form:id="control9" form:current-value="yes" form:convert-empty-to-null="true">
						<form:properties>
							<form:property form:property-name="ControlTypeinMSO" office:value-type="float" office:value="0"/>
							<form:property form:property-name="DefaultControl" office:value-type="string" office:string-value="com.sun.star.form.control.TextField"/>
							<form:property form:property-name="ObjIDinMSO" office:value-type="float" office:value=""/>
						</form:properties>
					</form:text>
					<form:textarea form:name="How many needed for install" form:control-implementation="ooo:com.sun.star.form.component.TextField" xml:id="control10" form:id="control10" form:current-value="16" form:convert-empty-to-null="true">
						<form:properties>
							<form:property form:property-name="ControlTypeinMSO" office:value-type="float" office:value="0"/>
							<form:property form:property-name="DefaultControl" office:value-type="string" office:string-value="com.sun.star.form.control.TextField"/>
							<form:property form:property-name="MultiLine" office:value-type="boolean" office:boolean-value="true"/>
							<form:property form:property-name="ObjIDinMSO" office:value-type="float" office:value=""/>
						</form:properties>
					</form:textarea>
					<form:textarea form:name="Referred By" form:control-implementation="ooo:com.sun.star.form.component.TextField" xml:id="control11" form:id="control11" form:current-value="Bill" form:convert-empty-to-null="true">
						<form:properties>
							<form:property form:property-name="ControlTypeinMSO" office:value-type="float" office:value="0"/>
							<form:property form:property-name="DefaultControl" office:value-type="string" office:string-value="com.sun.star.form.control.TextField"/>
							<form:property form:property-name="MultiLine" office:value-type="boolean" office:boolean-value="true"/>
							<form:property form:property-name="ObjIDinMSO" office:value-type="float" office:value=""/>
							<form:property form:property-name="VScroll" office:value-type="boolean" office:boolean-value="true"/>
						</form:properties>
					</form:textarea>
				</form:form>
			</office:forms>
If you want to stay with the .odt file, then suggestions are:

1 Ask for help on the Macros and UNO API forum

2 You can pay for someone to do a task on Paid support

3 Literally any (even first year student) programmer would be able to unzip the .odt file, read content.xml, and extract out the user-inserted data.

However the preferred way is probably:

1 Abandon the .odt

2 Enter the data into a spreadsheet or simple database.

A spreadsheet is simple to set up but clunky to use and you risk overwriting your data in error.

A database is more work to set up but much easier to use when set up, much more secure and offers many more functions - if you have the data you can answer easily questions like "All projects using more than 3 people between 2016 and 2017 where C/s was not used.

3 If you want the .odt as well, create a form letter from the spreadsheet or database where you extract data and put it into a Writer document. See Chapter 11 - Using Mail Merge in the Writer Manual.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
erika2k17
Posts: 3
Joined: Sat Nov 11, 2017 12:22 am

Re: Help transferring data

Post by erika2k17 »

Wow, thanks for the great information! I will definitely look at these options. Thank you!!!
OpenOffice version 4.1.1
Windows 10
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Help transferring data

Post by John_Ha »

Also check Creating a form for data entry in Chapter 15 of the Writer manual.

It seems that you can link a form with a database (which I think could be a spreadsheet) so that the data in the form is added to the database (spreadsheet). That seems to do what you want.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Help transferring data

Post by Villeroy »

Database connections to a directory of text files, a directory of dBase files and to spreadsheet documents are so called "file based database connections". They are somewhat limited because they do not support relations nor queries on more than one table and they have only a small set of built-in functions: http://www.openoffice.org/dba/specifica ... tions.html
dBase is the only editable among the three. Spreadsheets and text files are read-only and they behave badly when you update links to modified files. You have to restart the whole office suite before modified spreadsheet data find their way to serial letters, reports, forms which are no input forms anyway because you can't write anything. With dBase you can edit contents in forms, queries, tables or in the data source window and get the current data when you refresh your report, serial letter, form, whatever.

Most of the things you can do with a relational database connection and Calc are integrated in this pair of files: viewtopic.php?t=88516&p=416210#p416210 (contaminated with a one-line macro). Once the database is registered, you can do everything in the Calc interface, enter data, view data, summarize data.

The "feature scale" of Base connections:
read-only file connections (sheet and text)
dBase directory
External third-party database
Embedded HSQLDB
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Help transferring data

Post by Villeroy »

Connect Quickbooks with MS Access: http://qodbc.com/qodbcaccess.htm

The same may be possible with Base and a prepared ODBC data source
File>New>Database
Connect to existing...
Type: ODBC
LibreOffice on Windows has a button which opens the ODBC administrator.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
jessicacain
Banned
Posts: 2
Joined: Tue Dec 12, 2017 8:38 am
Location: X: thesagenext.com/

Re: Help transferring data

Post by jessicacain »

John_Ha wrote: If you want to stay with the .odt file, then suggestions are:

1 Ask for help on the Macros and UNO API forum

2 You can pay for someone to do a task on Paid support

3 Literally any (even first year student) programmer would be able to unzip the .odt file, read content.xml, and extract out the user-inserted data.

However, the preferred way is probably:

1 Abandon the .odt

2 Enter the data into a spreadsheet or simple database.

A spreadsheet is simple to set up but clunky to use and you risk overwriting your data in error.

A database is more work to set up but much easier to use when set up, much more secure and offers many more functions - if you have the data you can answer easily questions like "All projects using more than 3 people between 2016 and 2017 where C/s was not used.

3 If you want the .odt as well, create a form letter from the spreadsheet or database where you extract data and put it into a Writer document. See Chapter 11 - Using Mail Merge in the Writer Manual.
Hi John, thanks for giving these points. Helping me with the same issue.
Jessica Cain is an account manager at SageNext Infotech - one of the leading [url=https://www.thesagenext.com/solutions/quickbooks-hosting]QuickBooks hosting[/url] provider.
Post Reply