Problem in reading in case of line breaks

[ashishprem]

Hi,

I am reading from a CSV file. My code is something like this:

Set f = fs.OpenTextFile(sDesktopPath & "\" & "Download.txt")
f.ReadLine
Do While Not f.AtEndOfStream
    sLine = Replace(f.ReadLine, Chr(39), Chr(39) & Chr(39)) 'Replace all ' symbol with '' symbol
    sLine = Replace(sLine, Chr(34), Chr(39)) 'Replace " with '
    sLine = Replace(sLine, "&", "&") 'Remove "amp;"
    sBuffer = Split(sLine, Chr(39) & vbTab & Chr(39))  
    For i = 1 To UBound(sBuffer) - 1
        sBuffer(i) = "'" & Trim(sBuffer(i)) & "'"
    Next
    
    sBuffer(0) = sBuffer(0) & Chr(39)
    sBuffer(UBound(sBuffer)) = Chr(39) & sBuffer(UBound(sBuffer))
    
    'For integers - remove '
    sBuffer(8) = Replace(sBuffer(8), Chr(39), "")
    sBuffer(9) = Replace(sBuffer(9), Chr(39), "")
    sBuffer(10) = Replace(sBuffer(10), Chr(39), "")
    sBuffer(17) = Replace(sBuffer(17), Chr(39), "")
    sBuffer(20) = Replace(sBuffer(20), Chr(39), "")
    
 '''''''''''some other code constructs
Loop

It is working fine. But in some cases my 11th columns has line breaks and its not able to read next columns(from 12th column onwards) and as a result statement
sBuffer(17) = Replace(sBuffer(17), Chr(39), "")
is resulting in error.

Any suggestions to solve in case when the record has line breaks in a column.If no line breaks its working perfectly.

Regards,
Ashish

 

[namliam]

Well, you can use the "On Error goto" construction to trap this error and handle the next line as appending information to the previous line