Importing XML return to MS Access. - I just want 1 table!

You've got a couple of options. Access can use an XLS file to transform data on import so that it all ends up in the right places, or you can make the LiveCycle form export the data in an XML format that works for importing into Access.

If you are not familiar with XML processing I'd suggest the second option. Make Acrobat export the XML in the proper grammar. First you need to figure out what this XML grammar is. One way is to create the Access table you want, fill it with some dummy data and then export to XML. Once you have the XML file, set it up as a Data Source for your form in LiveCycle Designer and then bind the form fields to the appropiate nodes in the DataSource. That's it. Now Acrobat will export XML data in the defined grammar.

You have to be a bit careful in this process. When hooking the XML up, Acrobat will interpret repeated XML nodes as repeated subforms/fields. And in the other direction, if your form has any repeated subforms/fields you will not be able to import these into a single table. Repeated form data, like the lines in an order form, implies multiple DB tables.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]

Then most important JavaScript Development tool in Acrobat
[url=http://www.pdfscripting.com/public/34.cfm#JSIntro][b]The Console Window (Video tutorial)[/b][/url]
[url=http://www.acrobatusers.com/tutorials/2006/javascript_console][b]The Console Window(article)[/b][/url]

Can you help?

I get a variety of emails with similar data I want to make into a database. (see examples below)
I want to scrape the emails and add the info to an acrobat form.
There are 13 fields in my form. 5 of them map directly. Fields that don't map, I will leave blank. Extra data will go to the comments box on the form. Later a human will cut text from the comments field and paste into the appropriate fields.

Also when someone fills out the form online, I want to send this data to my online data storage.

------------------------------------------------------------------------------------
FIELDS I want in my form. Form Labels are ALL CAPS.
-----------------------------------------------------------------------------------
DATE of email
EMAIL address
Ship to COMPANY NAME
Ship to CONTACT
Ship to ADDRESS 1
Ship to ADDRESS 2 (Ship to ADDRESS 3)
Ship to CITY
Ship to STATE
Ship to ZIP
PART NUMBER (SO#, WFM#)
Part DESCRIPTION
COMMENTS

See these examples of email text I receive. They are all a little different but some fields are the same.
------------------------------------------------
EXAMPLE 1
-------------------------------------------------
1 X 467847-001 SPS-DRV HDD 120G 4200/5400 RPM
SO# 607946356 WFM# 4610980662-461-1
SHIP TO:
BANK OF AMERICA
200 n. college st.
suite 2785
Chris Pasdon
CHARLOTTE, NC 28202

------------------------------------------------
EXAMPLE 2
--------------------------------------------------
Raytheon
2000 E El Segundo Blvd, MS P168
Tom Ralfton
EL SEGUNDO, CA 90245
SO#607790045 WFM#4610493890-461-1
488588-001 DRV, HDD 120G 5400 RPM
Steve A

------------------------------------------------
EXAMPLE 3
--------------------------------------------------

QTY 1 492571-001 SPS-MEM 2GB PC2-6400
SO# 0607937575 WFM# 4610637229-462-2
Ship to:
A & R Edelman
201 Baldwin Ave
Rafael Xolocotzi
SAN MATEO, CA 94401
Nelson De Leon

Using Acrobat to automatically scrape data from a PDF is possible because words on the page, and thier locations can be found with the doc.pageGetNthWord() and doc.pageGetNthWordQuads() functions. However, this is tricky business. There has to be some way to for the program to identify the data. The easiest and most common methodologies are Position, associated words, and format. The data has to be at a specific location on the page, associated with specific words on the page, or it has to have a unique and easily identifiable format.

I've written many scraper automation scripts so I know this is possible to do, but it is a difficult script to write, and it becomes more difficult, or impossible, if the data is not consistent.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]

Then most important JavaScript Development tool in Acrobat
[url=http://www.pdfscripting.com/public/34.cfm#JSIntro][b]The Console Window (Video tutorial)[/b][/url]
[url=http://www.acrobatusers.com/tutorials/2006/javascript_console][b]The Console Window(article)[/b][/url]