how do I stack multiple columns into one?

[EnvynLust]

I made a .pdf form using livecycle designer, the form works great but now dealing with the data after has not been as much fun. report.csv

The form collects contact information, the csv that is spits out separates it not only by first/last/address1/address2 but by person.eg: person1firstname, person1lastname, etc... person2first, person2last, person2address1, etc.

I know the column headers are a disaster, it is from the structure of the form SIF[0].P1[0].PI[0].PIFirst[0]
SIF is the name of the document, Page1 is the subpage container for the page, PI is another sub-container, PIFirst is where data is entered in the form.

The form has places for 9 people with the last being able to duplicate itself for additional site staff, meaning the columns will always be of varying length due to this and that he information is compiled in adobe x pro, in batches of however many replies I get.

On the csv data starts in B2:EU2, each new row is a different form that was submitted.
I need all of the information to go into a different workbook with all of the first names in the same column starting in D5, all last in same column C5, all address 1 in same column F5, etc...

Instution B5
Last C5
First D5
Address1 F5
address 2 G5
City H5
State J5
Zip K5
Phone N5
Email P5
Fax R5

Im sure this might be easier if I knew how to use xml's, xmd's, etc... but I have zero programming experience. Writing some java into the pdf was challenging enough. I will be using this form to collect contact information from hundreds of places and potentially thousands of people, hence a macro would be nice.

Unfortunately there will also be duplicates in the data, is there any vb code that can remove duplicates, but is also searching by both first and last name, since their in different cells?

I have been banging my head against a wall for 2 days trying to figure this out on my own. I appreciate any help.

[rylo]

Hi

Will the first name always be in the same column across in all the CSV files? I notice that it is not a consistent spacing, but will the spacing always be the same? Columns B, M, X, AJ, AW, BU, CF, CR, DR, EG?

Or will that heading be in different columns, only identified by the word first in the heading name?

rylo

[EnvynLust]

Hi

Will the first name always be in the same column across in all the CSV files? I notice that it is not a consistent spacing, but will the spacing always be the same? Columns B, M, X, AJ, AW, BU, CF, CR, DR, EG?

Or will that heading be in different columns, only identified by the word first in the heading name?

rylo

yes, spacing will always be the same.

The pdf I made has the option to add as many contacts as the user desires, expanding the form, and the number of columns every time the user adds another contact. I have received 7 of the forms currently, and the farthest the csv goes currently is to column KE. The farthest first name in this export is in JT, column header is SIF[0].P1[0].Staff[8].AddFirst[0] who was the eighth person added by the user, but future csv's could potentially have far more additional contacts.

[rylo]

Hi

Gees, you are right about the headings. There is no consistency in the headings, or the position of the data within the new block.

Take an representative heading: SIF[0].P1[0].PI[0].PIFirst[0] (Note that not all are the same length in terms of "blocks")
They are all prefixed with SIF[0].P1[0].
So the third block (in this case .P1[0[]) seems to be the first point of change of data. In this case it would be, consistent parts removed, P1
Following this is a range of data, not in a consistent order that has the items you require.

However, this is not consistently the case.

Reg, PC and BC appear at least twice, and then don't have all the relevant information.

Can you give more detail on exactly how we are to know when a new item is found?

rylo

[EnvynLust]

Hi Rylo,

I think i have found a solution to this.
acrobat x pro is able to export the data in 2 formats, csv and xml
using the xml output it was fairly easy to create a xml map in excel
the mapped data can expand as many rows as it needs and dumps all of the contact info where ever you map it, putign all of the names in the same column for example.

now i just need to figure out how to make this user friendly for my co-workers...
currently they would have to:
-add the developer tab so the import button is available
-import the xml the created in acrobat
-copy the cells and paste values (to disassociate it with the xml import)
-create a new column with the following formula: =first&last
-use the remove duplicates feature based upon the combined firstlast (duplicates were created in the pdf with a same as button i made with some javascript that functions similar to when you purchase things online, and click the shipping adderss is same as billing address duplicating existing info, I will have to modify this for later versions of the pdf)
-run a find and replace against institution names to make sure they match whats in the system (eg: "name, LLC" becomes " "name LLC)
-run a vlookup against the fixed institution names pointing to a different spreadsheet with those institution's unique id #'s
-paste values again
-save & email to whomever processes the new contacts entry.

these are separate problems than what i posted here originally so I will mark this as solved.

Thanks for taking a look rylo