我有一个100MB的文本文件,它是管道分隔的,带有CR LF记录分隔符。问题是其中一个字段是自由形式文本,并且该字段中包含CRLF。我需要将数据加载到SQL中,但CRLF当然会把这件事搞砸。
我用FF-FE十六进制值替换了所有CRLF,但现在需要读取文件计数管道分隔符,并在最后一个字段后插入CRLF。
我必须计算七个管道分隔符,然后最后一个字段是一个24字节的日期字段,我需要在其中插入CRLF。
想到了最好的方法吗??
我没有测试这个,但无论如何,它可能会让你走上正轨。
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刚刚测试过。它基本有效。不过,如果以文本形式阅读,则假定为DOS编码。
如果您在一个变量中拥有内存中的全部内容,则可以使用split,比如myArray=split(var,"|")然后,您可以将0连接到6,并在那里添加一个crlf,然后继续,直到字符串