PDF > text > Excel - sorting "blocks" into columns.

[jonski]

I appear to have volunteered myself into a nightmare!

I've been given a series of PDFs, which are a series of names and addresses
of volunteers.

Now, the data is in the format of

Name
Address
Number
Joined
More random stuff

Now, of course, if each "block" was in a certain format, given a little
while I'm sure I could find or work out some sort of macro thing.

But it's not. Sometimes the address has 3, sometimes 4 lines. Sometimes
there's a phone number. Sometimes, it's a different way round.

Now imagine 500 names and addresses, each of about 6 lines - that's a LOT of
click-drag!

I was wondering - is it possible to form some kind or routine along the
lines of:
I click a cell. If I then press, for example, 1 on the keyboard, it will
move that cell up and across to the right column.

So, let's say I have

Name
Address

And I wanted it to be

A B
Name Addresse

etc, but (as I said above) without a pattern...I have no clue where to
start! Is such a thing possible?

Thanks in advance!

 

[Joe User]

I appear to have volunteered myself into a nightmare! [....]
Is such a thing possible?

The answer is: it depends!

I cannot give you a dispositive answer. But since I'm not sure anyone else
can, I will share my experience with doing something similar. Be
forewarned: it was not good :-(.

I tried to do something similar with a PDF file with results from a Muddy
Buddy event. The first time, I had some difficulties to overcome, but they
were managable. The second time, something changed in the file such that
the difficulties became insurmountable.

So I concluded that it depends on what version of Adobe was used to create
the PDF and/or what features were used. That is only speculation because
the MB organizers never got back to me with details.

(I suggested that they ask the supplier of the data to provide an Excel file
as well as a PDF in the future. We'll see if they listened to me this
year.)

When I did have success, my approach was to save the PDF as a text file.
That resulted in a non-structured sequence of lines.

Then I wrote a VBA macro to read the lines, work around some occassional
irregularities, and store the re-assembled data into columns in sequential
rows.

Sometimes the address has 3, sometimes 4 lines. Sometimes
there's a phone number. Sometimes, it's a different way round.

This may be something that you can workaround. It depends on the pattern of
lines after you do the PDF-to-text conversion.

If you wish, I would be willing to take a look-see and try to write the
necessary VBA macro. Send both the text file as an attachment to joeu2004
"at" hotmail.com. The email subject should be similar to this thread's.

(Note: I am "one the road" right now. I probably will not be able to work
on this until the weekend.)

I was wondering - is it possible to form some kind or routine along
the lines of:
I click a cell. If I then press, for example, 1 on the keyboard, it will
move that cell up and across to the right column.

That would be possible. But as you say, imagine doing that for each of 500
"entries", each with multiple lines.

but (as I said above) without a pattern...I have no clue where to start!

Patterns are literally in the eye of the beholder. You may be correct:
there is no pattern, or even a few set of patterns that we can work around.
But I might see a pattern (or two or three) that you overlooked.

As I said, I'm willing to give it a go. But there are no guarantees of
success. So continue to seek alternatives.


----- original message -----