asked on

Devlopment in Access

I reading in a text with tab delimited.

For that i use the folowing script:

Do While Not EOF(TXTFile)
Line Input #TXTFile, TXTLine

rst.AddNew
rst![Extension] = Mid(TXTLine, 29, 4)
rst![Cost_Code] = Mid(TXTLine, 13, 10)
rst![Total_Cost] = Mid(TXTLine, 34, 5)
rst![Date] = strdate_ono

rst.Update
Loop

if a word is not four letters long, it puts a space in my table.

can someone help me with this?

TimCottee

Hi TRAININGBSC,

Do While Not EOF(TXTFile)
Line Input #TXTFile, TXTLine

rst.AddNew
rst![Extension] = Trim(Mid(TXTLine, 29, 4))
rst![Cost_Code] = Trim(Mid(TXTLine, 13, 10))
rst![Total_Cost] = Trim(Mid(TXTLine, 34, 5))
rst![Date] = strdate_ono

rst.Update
Loop

The Trim function gets rid of any leading/trailing spaces.

Tim Cottee

jritchie777

Another way is to get VB to do the work, use Split to find the tabs and trim the spaces from the results, either way should work. I just happen to like the Split command since I don't have to hard code locations - it works on a delimiter kind of a crude sscanf from C:

dim vData as variant

Do While Not EOF(TXTFile)
      Line Input #TXTFile, TXTLine
      vData = Split(TXTLine, vbTab)
      rst.AddNew
      rst![Extension] = Trim$(vData(0))
      rst![Cost_Code] = Trim$(vData(1))
      rst![Total_Cost] = Trim$(vData(2))
      rst![Date] = strdata_ono
      rst.Update
Loop

Patrick Matthews

If the file is tab-delimited, then why are you using Mid() to get your fields?

Do While Not EOF(TXTFile)
Line Input #TXTFile, TXTLine
arr = Split(TXTLine, Chr(9))
rst.AddNew
rst![Extension] = Trim(arr(2)) 'update each of these as needed; array is zero-based
rst![Cost_Code] = Trim(arr(1))
rst![Total_Cost] = Trim(Mid(3))
rst![Date] = strdate_ono

rst.Update
Loop

Patrick

ASKER CERTIFIED SOLUTION

jritchie777

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial