Clean and modify a text file 2

[tstrike]

I am getting a text file that I need to modify in order to use is properly. I was able to bring the file into MS Access to modify it, but need to be able to have the file automatically cleaned so that the file could be brought in durring the nightly process. Here is a sample of the file:
08300059,100,00006

There are two things I need to do with this data. The first and relitively easy part I think is to remove the spaces at the end of each line. I think I can use the Search and replace method to do this one. My issue is that I need to also find in the text file any item in the second field that is 500 to 899 or 980 to 989 and replace it with 000. This is the part I am not sure about how to fix. Anybady got any ideas for me?

Thank you

 

[Andrzejek]

Read you text file line-by-line and write to another text file line-by-line.

TRIM is your friend for your problem #1

Second field, you mean 08300059,[blue]100[/blue],00006 ?
SPLIT is your friend here...

Have fun.

---- Andy

 

[tstrike]

Andy,

Thank you for your response. Trim will do the job for Problem one. However, I am not sure how split will help me. Where the 100 is will be other numbers like 502, 504, 506, 508, or many others up to 899. I need to replace these numbers with 000. Can split do that?

Thank you

Trevor

 

[PWise]

Update query

update tablename
Set field2 = 000
where field2 between 500 and 989
and field2 not between 900 and 979

 

[Andrzejek]

Consider this:

Dim ary() As String
Dim strTextLine As String
  
strTextLine = "  08300059,100,00006   "
strTextLine = Trim(strTextLine)
  
ary = Split(strTextLIne, ",")
  
Select Case Val(ary(1))
    Case 500 to 899, 980 To 989 
        ary(1) = "000" 
End Select

strTextLine = Join(ary, ",")

Write strTextLine to a new text file

Have fun.

---- Andy

 

[tstrike]

Andy,

Thank you for your help. I think this will work.

Trevor

 

[tstrike]

Andy,

I had earlier created the cleanup process in access 2007. Can I create a VB script that will check the text file date and then execute the save export in access without the user going into access?

Thank you

 

[Andrzejek]

You may try this, or just:

MsgBox FileDateTime("C:\Somefile.txt")

Have fun.

---- Andy

 

[dilettante]

It sounds as if you really want forum705 instead.

This one is for the actual programming system Visual Basic (versions 5 and 6), not for Access VBA macro writing issues.

 

[tstrike]

I have the Access VBA part down, the part I need help with is creating a VB script that will execute the Access vba script so that who ever is loading the data can get the cleaned and compiled text file to load into our reporting system.

Thank you

Trevor

 

[strongm]

>VB script

This isn't the VB Script forum. This is VB5/6, a completely different beast. You want forum329