• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

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?
0
TRAININGBSC
Asked:
TRAININGBSC
  • 2
1 Solution
 
TimCotteeHead of Software ServicesCommented:
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
0
 
jritchie777Commented:
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
0
 
Patrick MatthewsCommented:
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
0
 
jritchie777Commented:
Split is the easiest way to go.  Depending on the order of the elements in the data file is how they will be assigned to the variable as they are split up.  For example:

Extension     Cost Code     Total Cost        (sample from data file)
TBC  <TAB>  123  <TAB>  $10.89

'Sample code
dim vData as variant

Do While Not EOF(TXTFile)
     Line Input #TXTFile, TXTLine
     vData = Split(TXTLine, vbTab)
     rst.AddNew
     rst![Extension] = Trim$(vData(0))      'Value of your extension:  TBC from example above
     rst![Cost_Code] = Trim$(vData(1))    'Value of your cost code:  123
     rst![Total_Cost] = Trim$(vData(2))    'Value of your total cost:   $10.89
     rst![Date] = strdata_ono
     rst.Update
Loop

Make sure you do the correct data type checking as you assign to the record set variables.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now