Replace Carriage Return in Text file to be imported

[lman715]

I am importing a text file with 5 fields. However the 5th field is an open text format and records carriage returns.

The file is comma delimited, and each new line of data will start with defined text (PD), is it possible to use the replace command to search the fifth field of each "PD" row in the text file and remove the carriage returns to have a clean file?

I found this macro and it seems to do something close, but replaces all the carriage returns in the whole file and not just the 5th field.

Here is example of what is received:
"username","access_time","dbname","object_name","command"
"PDR1","Mar 28 2011 9:56AM ","DB1","","update master set
Master= 3,
Units =6,
lug = 9"
"PDC1","Mar 28 2011 9:56AM ","DB1","","update R_New set
Master= 62,
Units= 7,
Post = 81"

This is what I need it to look like:
"username","access_time","dbname","object_name","command"
"PD9R1","Mar 28 2011 9:56AM ","DB1","","update master set Master= 3, Units =6, lug = 9"
"PD9C1","Mar 28 2011 9:56AM ","DB1","","update R_New set Master= 62, Units= 7, Post = 81"

This is the code I found, but does not completely work...

Sub line_Removal()
'
Dim strFile As String
Dim strBuffer As String
Dim ff As Integer
strFile = "C:\testing\*.log"
strBuffer = Space(FileLen(strFile))
ff = FreeFile
Open strFile For Binary Access Read As #ff
  Get #ff, , strBuffer
Close #ff
strBuffer = Replace(strBuffer, Chr(13), "")
Kill strFile
Open strFile For Binary Access Write As #ff
  Put #ff, , strBuffer
Close #ff
'
End Sub

 

[lman715]

I did some more digging and may have found a starting point to the solution. I started a new thread at this link...

I have tried to be more concise in the new thread.
http://www.mrexcel.com/forum/showthread.php?t=578621

 

[lman715]

sorry, that link was for a different post, disregard the post above.

 

[hiker95]

lman715,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[lman715]

hiker95,

Thanks for notes at the bottom of your post, I wanted to attach a screen print, but couldn't figure out how.

Anyway, I am using Excel 2007.

 

[AlphaFrog]

Try something like this...

Sub line_Removal()
    '
    Dim strFile As String
    Dim strBuffer As String
    Dim ff     As Integer
    strFile = "C:\testing\*.log"
    strFile = "C:\Temp\Test1.log"
    strBuffer = Space(FileLen(strFile))
    ff = FreeFile
    Open strFile For Binary Access Read As #ff
    Get #ff, , strBuffer
    Close #ff
[COLOR="Red"]    strBuffer = Replace(strBuffer, vbCrLf, "")
    strBuffer = Replace(strBuffer, """PD", vbCrLf & """PD")[/COLOR]
    Kill strFile
    Open strFile For Binary Access Write As #ff
    Put #ff, , strBuffer
    Close #ff
    '
End Sub

It replaces all the carriage returns and then restores the carriage returns before each "PD

 

[lman715]

This worked almost perfectly. Thank you.

One minor glitch, The file has a headr, record count and trailer. Is there a way to tell it to skip the first two lines and the last line?

 

[lman715]

Never mind, I used your method and replaced the header,Trailer/Record Count witht he same.

This worked perfectly!!!

Thank you very much!!

 

[hiker95]

lman715,

Can we see your final macro code?

Please use Code Tags, see below in my Signature block: If posting VBA code, please use Code Tags - like this

 

[lman715]

Here is the Code that worked for the file...
As the header is the first line, was not necessary to adjust, however, the "Record" count line and the trailer,which begins with "End" were required.

I modified it to loop through a directory of files. Works perfect!

Note that the Replace items are case sensitive!!

Sub line_Removal()
    '
    Dim stPath As String
    Dim stFile As String
    Dim strFile As String
    Dim strBuffer As String
    Dim ff     As Integer
 
    stPath = "C:\testing\syb"
    stFile = Dir(stPath & "\*.log*")   ' get first log file
    Do Until stFile = ""
        strFile = stPath & "\" & stFile
        strBuffer = Space(FileLen(strFile))
        ff = FreeFile
        Open strFile For Binary Access Read As #ff
        Get #ff, , strBuffer
        Close #ff
        strBuffer = Replace(strBuffer, vbCrLf, "")
        strBuffer = Replace(strBuffer, """PD", vbCrLf & """PD")
        strBuffer = Replace(strBuffer, """pd", vbCrLf & """pd")
        strBuffer = Replace(strBuffer, """PS", vbCrLf & """PS")
        strBuffer = Replace(strBuffer, """ps", vbCrLf & """ps")
        strBuffer = Replace(strBuffer, "Record", vbCrLf & "Record")
        strBuffer = Replace(strBuffer, """Username", vbCrLf & """Username")
        strBuffer = Replace(strBuffer, "End", vbCrLf & "End")
        Kill strFile
        Open strFile For Binary Access Write As #ff
        Put #ff, , strBuffer
        Close #ff
        stFile = Dir()  ' get the next file
    Loop
    '
End Sub