Splitting/Combing data question

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

After using the Text Import wizard, the bulk of the data is
in an acceptable format, but I'm left with this type of data,
split across columns:

In A2: 126 Prices for Meas
B2: sured Work
C2: s - Major W
D2: orks
A3: D G
B3: ROUNDWORKS

Corrected, the result is:

A B
2 126 Prices for Measured Works - Major Works
3 D GROUNDWORKS

I can locate these records using
If IsNumeric(Left(C.Value, 4)) Then
but I don't know how to proceed further.

Any pointers would be much appreciated.

Regards.
 
Hi Jason
some questions:
- How do you indentify a new column. That is why do you insert '125'
and 'D' in column A.
- Does the source data has a amximumn column number or how do you
identify the last column (the last non-blank?)

So you may explain the underlying algorithm for this with a little bit
more detail :-)
Otherwise this macro would be quite straightforward.
 
The data is in the pdf file format, and the only way I know to import
this is via the Text Import wizard. That is how I set up the columns.

It works for about 50% of the data........not the rest. There are about
20,000 rows of data !!

Some of the blocks of data do not align correctly with the result that
a column set for one block of data 'splits' data in another block

eg: | 0.25 | 55 | is okay, but further down in another block I'd
get 0|.25 | 55 |

Here's 'good' data:

10.25 153.78 1.2 85 and now 'bad'

8.75 158.99 78.34 5 doesn't align.

Regards and thanks.

Frank Kabel said:
Hi Jason
some questions:
- How do you indentify a new column. That is why do you insert '125'
and 'D' in column A.
- Does the source data has a amximumn column number or how do you
identify the last column (the last non-blank?)

So you may explain the underlying algorithm for this with a little bit
more detail :-)
Otherwise this macro would be quite straightforward.
Click to expand...
 
Hi Stuart
IMHO the only way to correct this is playing with the text import
settings. what setting did you choose?
- fixed lenght
- delimiters?

Looking at you data examples there is no logic I can imagine to
distinguish good/bad data.


--
Regards
Frank Kabel
Frankfurt, Germany
The data is in the pdf file format, and the only way I know to import
this is via the Text Import wizard. That is how I set up the columns.

It works for about 50% of the data........not the rest. There are
about 20,000 rows of data !!

Some of the blocks of data do not align correctly with the result that
a column set for one block of data 'splits' data in another block

eg: | 0.25 | 55 | is okay, but further down in another block
I'd get 0|.25 | 55 |

Here's 'good' data:

10.25 153.78 1.2 85 and now 'bad'

8.75 158.99 78.34 5 doesn't align.

Regards and thanks.
Click to expand...
 
I chose Delimited.

I, also, could see no logic.

The Import Wizard allows a line at which to commence import.
Does it allow a line at which to end the import?

Perhaps then I could import the data in blocks which would
"fit" the column settings.

Is there an alternative to the Import Wizard?

Regards and Thanks.


Frank Kabel said:
Hi Stuart
IMHO the only way to correct this is playing with the text import
settings. what setting did you choose?
- fixed lenght
- delimiters?

Looking at you data examples there is no logic I can imagine to
distinguish good/bad data.
Click to expand...
 
Hi Stuart
you said that the source file is a PDF document. Maybe you can use
Acrobat Distiller to export the PDF file to a different format and use
this file as import source.

--
Regards
Frank Kabel
Frankfurt, Germany
I chose Delimited.

I, also, could see no logic.

The Import Wizard allows a line at which to commence import.
Does it allow a line at which to end the import?

Perhaps then I could import the data in blocks which would
"fit" the column settings.

Is there an alternative to the Import Wizard?

Regards and Thanks.
Click to expand...
 
I only have Acrobat Reader 6.0 (obviously FreeWare)

Is Distiller freeware?

This is hopefully a one-off import, so I'd rather avoid the
purchase of new software.

Regards.

Frank Kabel said:
Hi Stuart
you said that the source file is a PDF document. Maybe you can use
Acrobat Distiller to export the PDF file to a different format and use
this file as import source.
Click to expand...
 
Hi
no Acrobat Distiller is (unfortunately) no freeware.

--
Regards
Frank Kabel
Frankfurt, Germany
I only have Acrobat Reader 6.0 (obviously FreeWare)

Is Distiller freeware?

This is hopefully a one-off import, so I'd rather avoid the
purchase of new software.

Regards.
Click to expand...
 
Back
Top