I have a pipe delimited file and need to write with CR LF's

I had a 100MB text file that was pipe delimited, with CR开发者_Python百科 LF record delimiters. The problem is that one of the fields was freeform text, and included CRLF's in the field. I need to load data to SQL, but of course the CRLFs mess this up.

I replaced all the CRLFs with a FF FE hex value, but now need to read the file counting pipe delimiters, and insert the CRLFs after the last field.

I have to count seven pipe delimiters, then the last field is a 24byte date field where I will need to insert a CRLF .

Thoughts of best way to do this??

I didn't test this, but anyhow it may put you on the right track.

Dim b1 As Byte
Dim i As Long

' Open file in Binary mode (since you have at least one binary field)
Open "MyFile.dat" For Binary As #1

Do
    ' Advance to 7th | character from here
    For i = 1 To 7
        Do
            Get #1, , b1
            If b1 = 124 Then ' If Chr(b1) = "|" is perhaps more readable
                ' It's a "|" pipe
                Exit Do
            End If
        Loop
    Next i

    'Skip the 24-byte field
    Seek #1, Seek(1) + 24

    If EOF(1) Then
        ' We're all done
        Exit Do
    End If

    ' Record presumably ends here.
    ' Replace the next two bytes with the CR LF record delimiter
    Put #1, , CByte(10) ' CR
    Put #1, , CByte(13) ' LF
    ' Hopefully those were your hex FF and FE that just got overwritten. 
    ' Should really test for this before overwriting, 
    ' but what the heck.
Loop

Close #1

EDIT Just tested it. It pretty much works. Assumes DOS encoding if reading as text, though.

if you have the whole thing in memory in a variable, you could use split like myArray=split(var, "|") Then you could just concatenate from 0 to 6 and add a crlf there and continue until the end of the string