Leading and Trailing line feeds

[teel73]

I have a huge issue that I've been trying to resolve. I imported some data from a text file and some of the memo fields contained symbols for line feeds. I was able to replace the symbols with the vbCrLf. Now my issue is removing the leading and trailing line feeds and extra line feeds. I tried using the Trim function but the blank lines are still there. For example: In my field [details], the value looks like this (everything between the brackets):

[





At 0826 on 2/25/10, Fannie Mae employee Alexander Antzoulatos contacted DC SCC and reported that his FM issued laptop was stolen on 2/24/10. The following is the incident report taken by S/O Lindsey. At approximately 1800 on 2/24/10, Mr. Antzoulators left his office (2570D) in the 4000 South building with his laptop and placed it in the trunk of his car. He departed from Fannie Mae and went to Washington Sports and Health Club in Bethesda Maryland. He parked his vehicle inside the adjacent parking garage on the P2 level at approximately 1830. He opened his trunk to retrieve his gym bag, and then proceeded to the gym. At approximately 1945 when he returned to his vehicle, Mr. Antzoulators stated that he pressed his remote key device to open the trunk but did not hear a sound. He then manually opened his trunk and at that point realized that his laptop was missing. He went to the driver side door and found the door unlocked.





He could not recall if he locked the door or not. Mr. Antzoulatos also noticed that his iPod was missing from the inside of his vehicle. Mr. Antzoulatos emailed his manager, Mr. Darris Hess of incident. Mr. Hess told Mr. Antzoulatos to file an official police report.


]

 

[DCrake]

Can you provide a sample text file

 

[teel73]

My attachment is Query1.txt .. thanks for any help.

 

[DCrake]

Ok PLace this function in a standard module

Public Function NoSpaces(sFile As String) As String
 

Dim ff As Long
ff = FreeFile
Dim strline As String


Open sFile For Input As #ff
Do Until EOF(ff)
  Line Input #ff, strline
    strline = strline & Trim(strline)
Loop
Close #ff
strline = Replace(strline, Chr(34), " ")

NoSpaces = strline


End Function

sFile is the full path and filename of the textfile you want to read
the resulting strline is the truncted block of text

To test this go in to the immediate window (Ctrl+G) then enter

?NoSpaces([COLOR="Red"]C:\......\Filename.txt[/COLOR])

For brevity only - use real path and file names

 

[teel73]

So .. I will need to export the data back out into a text field and then import it using that function?

I don't understand. I've already imported the data into the database table. That is where I'm trying to remove the line feeds.

 

[DCrake]

What you can do is to execute an append query

strline = Replace(strline, Chr(34), " ")

NoSpaces = strline
DoCmd.RunSQL "Insert into .... Set Field = '" strline & "'"

 

[teel73]

If sfile is the full path name .. where do I tell it that. This is what I have and it didn't do anything:

Public Function NoSpaces(sFile As String) As String
Dim ff As Long
ff = FreeFile
Dim strline As String
sFile = "C:\Documents and Settings\e3utbl\Desktop\Query1.txt"
Open sFile For Input As #ff
Do Until EOF(ff)
  Line Input #ff, strline
    strline = strline & Trim(strline)
Loop
Close #ff
strline = Replace(strline, Chr(34), " ")
NoSpaces = strline

End Function