Conditionally removing carriage returns

[notadba]

Hi,

I have a problem with an extract file that I am trying to use in another application. The file wraps rows of data extracted from a database table that has a 'long' field with carriage returns. What I need to do is to join up the errant rows so that I can then use the file to import. (I realise that the best option is to fix the extract code, but this will take months for the developers to get around to.

The file looks like:
ID FIELD1 FIELD2 LONGFIELD
8% 123 abc Free text
8% 124 def free text with a
carriage return wrapping text
posible over many line
8% etc

Need to join up the wrapped lines. The consistent data pattern is that 'real' rows start with a 7 digit ID starting with 8. There are line feed/carriage returns on the end of every row.

Looking for a script approach to fixing this.

Thanks in advance.

 

[vbSun]

I would go like this.

1. Declare two string arrays [blue](ex. strSource,strDest)[/blue] and an integer [blue](ex. intSourceIndex)[/blue]
2. Split the content of the text file using vbCrlf/vbCr and assign it to the first array. [blue](strSource=Split(strContents,vbCr)[/blue]
3. Loop through the source array to see whether the first char is 8. [blue](if left(trim(strSource(i),1)=8 then)[/blue]
4. If yes, assign it to the second array's corresponding variable, using intSourceIndex [blue](ex. strDest(intSourceIndex)=strDest(i))[/blue]
If no, look at the next element in the strSource array., increment loop index by 1 and decrease intSourceIndex by 1.
[blue](i=i+1:intSourceIndex = intSourceIndex -1)[/blue]
5. Make step 4 recursive, till you find the 8 in the next array element.
6. Finally, you will have strDest with array elements in the format that you would like.

Let us know if you still have trouble..

------------------------------------------
The faulty interface lies between the chair and the keyboard.

 

[strongm]

Something like the following should work ...

[blue]With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "(\r\n)(\D+?|[^8]\D{7}.+)(?=\r\n|$)"
    MsgBox .Replace(Text1.Text, "$2")
End With[/blue]

 

[strongm]

oops! '7' should read '6' in .Pattern

 

[strongm]

Hello? Were either of the suggestions here of any help at all?

 

[notadba]

Sorry for late response, have been away on other sites with their own 'special' issues. The problem here has been solved with some ETL functions in Cognos, but will test later today on some other files that we are having issues with - as this is a common issue for extracts here.

Thanks all for your responses. Will let you know how it goes.