I have a function in my database that imports preformatted text files and picks the values from certain positions using the Mid$ function. These values are then used to populate new documents.
The problem I have is that if a user’s input file contains a tab instead of the associated number of spaces, it skews all the data sideways resulting in the wrong characters being read into each variable.
Is there any way that as I import my text file (using Line Input#) I can convert all tabs present into the correct number of spaces instead?
I searched the forum but found only articles concerning tabs not being recognised on the web.
Well, Charles has the optimal solution for this. I do need to brush up on the available functions for script. My solution for this is now in the bit bucket.
in a text file a tab is generally 8 spaces. maybe you can write a logic to get the text between 5 spaces (i.e. atleast 5 spaces on either side or only the left…to take into effect the last value)…and then trim it. all this needs to be done for every line input. keep in mind that the number of values returned could be variable…since different lines can have different data and hence different number of tabs separating them. hope this is clear.
Unfortunately I don’t really understand what you mean. Let me explain again - the input text file has data running across it in specified positions. So at position 26, for 2 columns, I might expect to find a numerical value.
However, if a person has skipped some columns before that column using tabs, when I check position 26 it doesn’t reflect my numerical value, because the tabs have skewed my data across!
Dim FieldStarts(4) As Integer
FieldStarts(0) = 1
FieldStarts(1) = 11
FieldStarts(2) = 26
FieldStarts(3) = 30
FieldStarts(4) = 59
Dim curPos As Integer
Dim ePos As Integer
Dim FieldContents(4) As String
'…
Line Input #file1, inputStr
curPos = 1
ePos = Len(inputStr)
For i = 0 To Ubound(FieldStarts)
If i = Ubound(FieldStarts) Then
fLen = ePos
Else
fLen = (FieldStarts(i + 1) - FieldStarts(i)
End If
scanStr = Mid(inputstr, curPos, fLen)
tPos = Instr(scanStr, Chr(9))
If tPos > 0 Then
FieldContents(i) = Left(scanStr, tPos)
curPos = tPos + 1
While Mid(inputStr, curPos, 1) = Chr(9)
curPos = curPos + 1
Wend
Else
FieldContents(i) = scanStr
curPos = curPos + Len(scanStr)
End If
Next
Subject: RE: That will not work. A tab can represent from 1 to 8 spaces depending on the length of the text before it.
Thanks Bill, you’re dead right, I tried Charles’ method and found exactly what you said. I couldn’t find a way of detecting how many spaces it represented either, but I’m still working on it.
If you read through a line of text which contains tabs and get it to tell you what character is at what position, it gives some very bizarre results, especially when compared to what I see in Notepad.
I guess I might just have to force the users to not use tabs, or get them to use a program such as Textpad where you can remove tabs whilst retaining the positions.