Re: Splitting/Combing data question

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 03/01/04 

Date: Mon, 1 Mar 2004 19:58:48 +0100

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
Stuart wrote:
> 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" <frank.kabel@freenet.de> wrote in message
> news:efF2Gz4$DHA.3284@TK2MSFTNGP09.phx.gbl...
>> 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.
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>> Stuart wrote:
>>> 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.
>>>
>>>
>>> ---
>>> Outgoing mail is certified Virus Free.
>>> Checked by AVG anti-virus system (http://www.grisoft.com).
>>> Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004
>>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004

Relevant Pages

  • Re: count days
    ... now depending on your settings try ... What language version of Excel are you using? ... > "Frank Kabel" wrote in message ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Loopback Processing and Deny Apply in ACL
    ... > regards to the GPO's getting applied then what will ... > actually get applied at logon in regards to loopback policy's and setting ... The computer configuration settings from this list are applied to the ...
    (microsoft.public.win2000.group_policy)
  • Re: Screen saver does not start
    ... Go to Start/Administrative Tools/Services/Themes. ... Also check your settings here: ... Restore Themes Functionality ...
    (microsoft.public.windows.mediacenter)
  • Re: Path And cron
    ... Tim Daneliuk wrote: ... Best regards, ... Several environment variables are set up automatically by the cron ... Given that the default shell is /bin/sh, are the settings ...
    (freebsd-questions)
  • Re: It is not the case
    ... >>> settings, I've got strange webproxy log entries saying that the ... >>> browser is not configured as web proxy client of local web proxy> service, ... >>> Regards, ...
    (microsoft.public.isa)