PDF or HTML form into Database (or Excel) then Merge into Pre-formatted Word Doc

Question

I have been searching for an answer to this problem for literally years. I am not a full programmer, but have had previous experience.

I have tried to hire people to do this with little success. I have looked for a standalone software product that does this...with little success.

What I am trying to do is take a HTML or PDF form letter that you can choose Radio button inputs, checkbox inputs or text box inputs, once filled out ...then to save it into a database of sorts, excel or mysql (easier would be most appreciated to use).

Then to take that information in the database and place the specific data into various specific areas in a pre-formatted word doc.

An example would be:

Name:(Text box) ABC, DEF.

Date:(Text Box or Dropdown Calendar) JAN-DEC, 1-31, 0001-2016. (etc).

Sex: (Radio Button) MALE, FEMALE.

Items: (Checkboxes) BANANAS, APPLES, ORANGES.

Then to place this info into a word doc like:

On MAY 02, 2016, ABC a tall MALE, and DEF a short FEMALE went to the store and picked up some BANANAS and APPLES. The forgot to purchase ORANGES so they had to go back the next day to get them.

1) Can someone please let me know if they know of any software that can do this? 2) Can someone tell me if they know of an easier way to do this?

I have a form that I will have about 300 pieces of data that have to be placed in a structured word doc.

Also I would want to be able to place the information from the database, BACK into the initial PDF or HTML form to make any corrections and then resave it back into the database.

The database would have hundreds of people in it and EACH their specific 300 points of data.

Big Project.

So with that in mind...does anyone have any ideas of the basic example that I gave? This can help me expand into the full version when I have the right foundation.

Thanks

Answer 1

What you've described can be done with Sharepoint. I've successfully accomplished this with SP 2010 and 2013.

In Sharepoint, create a custom list with columns for the data you need to capture. Create a document content type and replace the default template with your Word doc template. Create a document library with the same columns you created for your list and apply the document content type you created. Edit the document library template by embedding the library columns wherever needed via quick parts. Create a Workflow that fires when a new list item is created. Set the workflow to create a new document in the document library and assign field values to match the field values of the newly create list item. That should do it. You can even export the list to Excel if you wanted. This may sound a bit complicated, but its not. Pretty basic sharepointing.

Answer 2

First off - a lot of people make the mistake of trying to use MS-Excel as a database - it is not it is a spreadsheet don't try to use it as a database as you are just making hard work for yourself.

Microsoft Office Suite includes MS-Access which is a database in the pro editions.

LibreOffice the free office solution includes base.

Either will let you design forms, with validation, to enter data into a database, queries to get the data back out of the database and reports which are documents filled in with that data such reports can be in Word format from either tool.

Personally I would always recommend LibreOffice as more flexible as well as the price difference and a really good starting point - it can connect to a number of different databases as well as using its own.

If you must use html as a data input medium, rather than the Access/Base front end then you need to host the forms that you design on a server and provide a post mechanism for the data to be transferred to the database and possibly to trigger running the query and producing the report(s).

PDF forms can be created, (from within LibreOffice), that have a number of fields that can be saved into X/FDF and returned to the originator for the data to be automatically extracted and entered into the database and possibly to trigger running the query and producing the report(s).

As you say quite a big job - getting a good book might be an excellent start - the LibreOffice Documentation is a big help as are the document foundation books.

Answer 3

You could have a look at Kexi http://www.kexi-project.org/ which is worth gaining momentum. The website is a little outdated but the main developper is working hard to improve the software as well as the documentation. You'll find the latest information in the Kexi forum, which is part of the KDE forum.

Kexi is in a software in the spirit of "Filemaker" or "Microsoft Access". You can easily create templates of fields, which allow filling / editing records into database.

The project is currently supported by one developer, but it's really worth testing the software. Kexi let you generate reports ; I cannot remember the output format, but you could probably convert the documents to Word ones if necessary.

Kexi store projects in a standalone sqlite database. You don't need running a database server like for MySQL / PostgreSQL for instance. SQLite is a popular database formats and there are many editors (other than Kexi) that will also let you edit the records.

Currently, the forms as well as the data are stored in the same database.

Kexi can furthermore import data from MySQL and PostgreSQL. There are also projects to make it able to edit directly MySQL and PostgreSQL databases.

Kexi is available for Linux with KDE Desktop. There is also a past version compiled as a binary for Windows, and making the new version available for Windows is in the pipeline as well. I tested the project on Debian with KDE Desktop as well as on Windows 7. There are some minor bugs like graphical ones, but basically it works.

Answer 4

I would like preface what I am saying with I have a lot of experience with this subject matter. Although I don't do this for a living, I have done it for many personal projects. I have designed a number of forms that connect to a database in the past.

I would suggest you find a PHP framework, I discovered that although php can connect to database you have to code it in a certain way. This is because otherwise it will be vulnerable to hackers doing many things without a bunch of extra coding. The framework automatically handles most of these things for you.

One of the common example of hacker mischief is my name is

johnny; drop table users;

If you didn't sanitize your data manually in php then the hacker would successfully delete all your users. I spent a lot of time writing prepare statements to block this kind of behavior, but the many of the php frameworks do all of this for you.

hundreds of people is trivial to a database when you get 50,000-100,000 people you will have a small database. I don't recommend excel as it can't do forms or PDF well, and has other limitations.

It would be best to just use a mysql database and a web server with php support preferable version php7 and 7.2 if you can manage it.

The solution here is 2 parts

1. Getting the data into the database
2. Getting it out.

Getting it out is as simple as a mail merge. See this answer I wrote on how to setup a database connection https://superuser.com/questions/1259086/getting-data-from-mysql-into-microsoft-word/1259409#1259409

Hello <Insert column 1 here>,

Based on your form needs HTML(5) has all your needs covered.

Now we need to link that to a database. You might be able to use JQuery, but I know it can be done in php.

Depending on your skill level you can code directly in php or use a framework. The downside to using a framework is you have to learn how to program in it. The upside is they handle most of the technical work for you. I just started using https://laravel.com/

The benefit to the framework is it handles all the fiddly work of avoiding hackers using mysql injection and other bad things. It automatically pre-treats most of the inputed values. Now you still have to do a bit of input testing to make sure they don't enter dumb values like 1 or 999 for age. Many of the php frameworks even allow each user to setup an account they can login to using a username and password. It only takes a small amount of code to handle it everything to do with logins including encrypting the password.

PHP hides most of the annoying database things you might experience in other programming languages. A framework hides even more of the complexities.

Getting the data back into a HTML from the database would also be relatively easy especially if you used a framework.

For efficiency you will want to break up your 300 columns into at least 3 tables.

Doing it in a PDF isn't ideal, and HTML is going to make inputting and re-displaying your data much easier. HTML was literally designed for this purpose.

You will probably want to talk to a database person eventually to properly divide your data into multiple tables for the best possible performance. In database design there is what is called the "5th normal form" and someone who can get your data arranged in this way would be best.