Help transferring data

Discuss the word processor

Help transferring data

Postby erika2k17 » Sat Nov 11, 2017 12:27 am

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
erika2k17
 
Posts: 3
Joined: Sat Nov 11, 2017 12:22 am

Re: Help transferring data

Postby RusselB » Sat Nov 11, 2017 5:09 am

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.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: 4113
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Help transferring data

Postby John_Ha » Sun Nov 12, 2017 3:57 pm

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.
AOO 4.1.4, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the up to date Writer guide for information. Click the Help button on a pop-up window for extensive help on that function.
John_Ha
Volunteer
 
Posts: 4536
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Help transferring data

Postby erika2k17 » Tue Nov 14, 2017 12:41 am

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 6 times
OpenOffice version 4.1.1
Windows 10
erika2k17
 
Posts: 3
Joined: Sat Nov 11, 2017 12:22 am

Re: Help transferring data

Postby John_Ha » Tue Nov 14, 2017 2:15 pm

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.

Clipboard01.png
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   Expand viewCollapse view
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   Expand viewCollapse view
         <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.
AOO 4.1.4, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the up to date Writer guide for information. Click the Help button on a pop-up window for extensive help on that function.
John_Ha
Volunteer
 
Posts: 4536
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Help transferring data

Postby erika2k17 » Tue Nov 14, 2017 4:33 pm

Wow, thanks for the great information! I will definitely look at these options. Thank you!!!
OpenOffice version 4.1.1
Windows 10
erika2k17
 
Posts: 3
Joined: Sat Nov 11, 2017 12:22 am

Re: Help transferring data

Postby John_Ha » Tue Nov 14, 2017 5:22 pm

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.
AOO 4.1.4, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the up to date Writer guide for information. Click the Help button on a pop-up window for extensive help on that function.
John_Ha
Volunteer
 
Posts: 4536
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Help transferring data

Postby Villeroy » Tue Nov 14, 2017 10:52 pm

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: https://forum.openoffice.org/en/forum/v ... 10#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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24584
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Help transferring data

Postby Villeroy » Tue Nov 14, 2017 11:32 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24584
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Writer

Who is online

Users browsing this forum: No registered users and 29 guests