Solved: Remove spaces from tab delimited text file

[austenr]

Is there a trim function like in Excel to eliminate extra spaces from a text file on import? Can't seem to find what I am looking for. Thanks.

[austenr]

Never mind I found it. How would you incorporate it into this code to remove the trailing spaces? Also what I came up with didn't remove the pipe delimiter.

Public Sub ConvertTextFile(strInputFile As String, strOutputFile As String)

Dim strInputString As String
Dim strOutputString As String
Dim varSplit As Variant
Dim intUB As Integer
Dim intCount As Integer

Open strInputFile For Input As #1
Open strOutputFile For Output As #2

Do Until EOF(1)
Line Input #1, strInputString
varSplit = Split(strInputString, "|", , vbTextCompare)
intUB = UBound(varSplit)
intCount = 0
While intCount <> intUB + 1
strOutputString = strOutputString & varSplit(intCount) & Chr(9)
intCount = intCount + 1
Wend
Print #2, strOutputString
strOutputString = ""
intUB = 0
Loop
Close #1
Close #2

End Sub

[OBP]

This is the code that I developed to produce the file that I sent you.
Dim fs, f, teststring As String, linecount As Integer, maxcount As Integer, rs As Object, charcount As Integer
Open "c:\Access\TEST.TXT" For Input As #1 ' Open file.
linecount = 0

Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
linecount = linecount + 1
Loop
Close #1 ' Close file.

maxcount = linecount
linecount = 0
'MsgBox maxcount
Open "c:\Access\TEST.TXT" For Input As #1 ' Open file.
Open "c:\Access\TABBEDTEST.TXT" For Output As #2
For linecount = 1 To maxcount ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
teststring = TextLine ' Print to the Immediate window.
'MsgBox teststring
For charcount = 1 To Len(teststring)
'MsgBox Mid(teststring, charcount, 1) & " = " & Asc(Mid(teststring, charcount, 1))
If Asc(Mid(teststring, charcount, 1)) = 124 Then teststring = Left(teststring, charcount - 1) & Chr$(9) & Right(teststring, Len(teststring) - charcount)
Next charcount
Print #2, teststring
Next linecount
Set rs = Nothing
Close #1 ' Close file.
MsgBox "done"
You might need to check the Right() to make sure I am getting the whole string OK.

PS you don't actually need the prt doing the line count, that was there to check I was getting 3 lines.

[austenr]

Thanks Tony

[austenr]

Tony,

I've tried adapting your code to the attached database form. The button works to open and view the TEST file but when I add your code to the Process button to convert it to tabbed, nothing happens. Ideas?

[austenr]

Here is the incoming text file if anyone is interested.

[OBP]

Sorry, I can't work with Access 2007 databases.

[austenr]

I'll throw together the form in 2003 and post it in a bit. The text files you have.

[austenr]

Here is the DB in 2003.

[OBP]

Did you create a Folder called Access on drive c: and put the txt Files on it?

[OBP]

As it works for me.
But is does need a Close #2 before the msgbox as it leaves the file open in Access.

[austenr]

No but I put the text file on my desktop and changed the path in the code

[OBP]

Austen, what can I say, it worked in my database and it also works in your database on my txt files.

[OBP]

This is the result of running your code twice.

[austenr]

My apologizes my friend. 2007 must be quirky. I used your code on 2003 qnd it works absolutly fine. Nice job and thanks again. Have a good one.
:hi:

[OBP]

Damned Access 2007, how are you going to proceed? use 2003?

[austenr]

Thats all i can do. I wish they would leave well enough alone (Microsoft). 2007 is about as badly put together as Vista IMHO.

[OBP]

I am avoiding it as long as possible. :devil2: