Handle Text File

Topic

New Reply

Hi

I created some code to handle a text file and export to a location on the server.
The code searches a string or value and replaces in a text file.
The text file is then collected by an external website and uses the data.
Originally the file was imported into an estimating system and then used on the website, this could take 3 days within our administration method, so to save these days I am going direct from the web to collect the data.
I’m getting an error which ( I think) 0originates from XML language which for this case has no relevance, the error occurs if there is no value or null between the “””” quotes in the file.

To make this work correctly, I would have to check every line of code with an IF statement

(Example)
strSearchtext1 = “#ASSESS#”
If strSearchtext1 = “” then
strSearchstring1 = “.”
Else
strReplacetext1 = Left(JN & Me.EST_NO & Space(100), Len(strSearchtext1))
End if

This would take ages to complete, my question is, is there a quicker way of doing this?

Code as below:

Private Sub Command43_Click()
Dim strInputfile As String
Dim strOutputfile As String
Dim strHelpHireFile As String

Dim JN As String

JN = InputBox(“Please Enter Your ATX Prefix”, “”)

If JN = “” Then
MsgBox “You Must Enter A Valid ATX Prefix” & vbCrLf & _
“Please Try Again”, vbInformation, “Information”: Exit Sub
End If

If Len(JN) > 3 Then
MsgBox “Too Many Characters” & vbCrLf & _
“You Can Only Use 3 Characters” & vbCrLf & _
“Please Enter A Valid 3 Character Prefix”, vbInformation, “Information”: Exit Sub
End If

If Len(JN) < 3 Then
MsgBox "Not Enough Characters" & vbCrLf & _
"You Need To Use 3 Characters" & vbCrLf & _
"Please Enter A Valid 3 Character Prefix", vbInformation, "Information": Exit Sub
End If

strInputfile = "C:MM-UtilitiesATX Import ProjectAudaImport.txt"
strOutputfile = "C:MM-Utilities" & JN & Me.EST_NO & ".txt"
strHelpHireFile = "L:ATX-Helphire-Export" & JN & Me.EST_NO

Dim strLine As String
Dim strSearchtext1 As String
Dim strReplacetext1 As String
Dim strSearchtext2 As String
Dim strReplacetext2 As String
Dim strSearchtext3 As String
Dim strReplacetext3 As String
Dim strSearchtext4 As String
Dim strReplacetext4 As String
Dim strSearchtext5 As String
Dim strReplacetext5 As String
Dim strSearchtext6 As String
Dim strReplacetext6 As String
Dim strSearchtext7 As String
Dim strReplacetext7 As String
Dim strSearchtext8 As String
Dim strReplacetext8 As String
Dim strSearchtext9 As String
Dim strReplacetext9 As String
Dim strSearchtext10 As String
Dim strReplacetext10 As String
Dim strSearchtext11 As String
Dim strReplacetext11 As String
Dim strSearchtext12 As String
Dim strReplacetext12 As String
Dim strSearchtext14 As String
Dim strReplacetext14 As String
Dim strSearchtext15 As String
Dim strReplacetext15 As String
Dim strSearchtext16 As String
Dim strReplacetext16 As String
Dim strSearchtext17 As String
Dim strReplacetext17 As String
Dim strSearchtext18 As String
Dim strReplacetext18 As String
Dim strSearchtext19 As String
Dim strReplacetext19 As String
Dim strSearchtext20 As String
Dim strReplacetext20 As String
Dim strSearchtext21 As String
Dim strReplacetext21 As String
Dim strSearchtext23 As String
Dim strReplacetext23 As String
Dim strSearchtext24 As String
Dim strReplacetext24 As String
Dim strSearchtext25 As String
Dim strReplacetext25 As String
Dim strSearchtext26 As String
Dim strReplacetext26 As String
Dim strSearchtext27 As String
Dim strReplacetext27 As String
Dim strSearchtext28 As String
Dim strReplacetext28 As String
Dim strSearchtext29 As String
Dim strReplacetext29 As String

strSearchtext1 = "#ASSESS#"
strReplacetext1 = Left(JN & Me.EST_NO & Space(100), Len(strSearchtext1))
strSearchtext2 = "#DATE2222#"
strReplacetext2 = Left(Date & Space(100), Len(strSearchtext2))
strSearchtext3 = "#REGIS #"
strReplacetext3 = Left(Me.REG & Space(100), Len(strSearchtext3))
strSearchtext4 = "#CLAIMNUMBER2222222#"
strReplacetext4 = Left(Me.CLM_NO & Space(100), Len(strSearchtext4))
strSearchtext5 = "#SEARCHNAME22222222#"
strReplacetext5 = Left(Me.OWN_NME & Space(100), Len(strSearchtext5))
strSearchtext6 = "#ASSESSOR #"
strReplacetext6 = " "
strSearchtext7 = "#OWNER #"
strReplacetext7 = Left(Me.OWN_NME & Space(100), Len(strSearchtext7))
strSearchtext8 = "#ADDRESS1 #"
strReplacetext8 = Left(Me.OWN_ADD_1 & Space(100), Len(strSearchtext8))
strSearchtext9 = "#ADDRESS2 #"
strReplacetext9 = Left(Me.OWN_ADD_2 & Space(100), Len(strSearchtext9))
strSearchtext10 = "#ADDRESS3 #"
strReplacetext10 = Left(Me.OWN_ADD_3 & Space(100), Len(strSearchtext10))
strSearchtext11 = "#ADDRESS4 #"
strReplacetext11 = Left(Me.OWN_ADD_4 & Space(100), Len(strSearchtext11))
strSearchtext12 = "#PC##P#"
strReplacetext12 = Left(Me.OWN_PCD & Space(100), Len(strSearchtext12))
strSearchtext14 = "#TEL1222222222222#"
strReplacetext14 = Left(Me.OWN_TEL_H & Space(100), Len(strSearchtext14))
strSearchtext15 = "#TEL2222222222222#"
strReplacetext15 = Left(Me.OWN_TEL_W & Space(100), Len(strSearchtext15))

strSearchtext16 = "#OWNER #"
strReplacetext16 = Left(Me.OWN_NME & Space(100), Len(strSearchtext16))
strSearchtext17 = "#ADDRESS1 #"
strReplacetext17 = Left(Me.OWN_ADD_1 & Space(100), Len(strSearchtext17))
strSearchtext18 = "#ADDRESS2 #"
strReplacetext18 = Left(Me.OWN_ADD_2 & Space(100), Len(strSearchtext18))
strSearchtext19 = "#ADDRESS3 #"
strReplacetext19 = Left(Me.OWN_ADD_3 & Space(100), Len(strSearchtext19))
strSearchtext20 = "#ADDRESS4 #"
strReplacetext20 = Left(Me.OWN_ADD_4 & Space(100), Len(strSearchtext20))
strSearchtext21 = "#PC##P#"
strReplacetext21 = Left(Me.OWN_PCD & Space(100), Len(strSearchtext21))
strSearchtext23 = "#TEL1222222222222#"
strReplacetext23 = Left(Me.OWN_TEL_H & Space(100), Len(strSearchtext23))
strSearchtext24 = "#TEL2222222222222#"
strReplacetext24 = Left(Me.OWN_TEL_W & Space(100), Len(strSearchtext24))
strSearchtext25 = "#MILE#"
strReplacetext25 = Left(Me.VEH_MIL & Space(100), Len(strSearchtext25))
strSearchtext26 = "#CHASSIS222222222222#"
strReplacetext26 = Left(Me.VEH_CHA_NO & Space(100), Len(strSearchtext26))
strSearchtext27 = "#POLICY2222222222222222222222222222#"
strReplacetext27 = Left(Me.POL_NO & Space(100), Len(strSearchtext27))
strSearchtext28 = "#COLOUR2222#"
strReplacetext28 = Left(Me.VEH_PNT_COL & Space(100), Len(strSearchtext28))
strSearchtext29 = "#EXCE#"
strReplacetext29 = Left(Me.EXS & Space(100), Len(strSearchtext29))

Open strInputfile For Input As #1 ' Open file for intput.
Open strOutputfile For Output As #2 ' Open file for output.
Open strHelpHireFile For Output As #3
Do While Not EOF(1)
Line Input #1, strLine

strLine = Replace(strLine, strSearchtext1, strReplacetext1)
strLine = Replace(strLine, strSearchtext2, strReplacetext2)
strLine = Replace(strLine, strSearchtext3, strReplacetext3)
strLine = Replace(strLine, strSearchtext4, strReplacetext4)
strLine = Replace(strLine, strSearchtext5, strReplacetext5)
strLine = Replace(strLine, strSearchtext6, strReplacetext6)
strLine = Replace(strLine, strSearchtext7, strReplacetext7)
strLine = Replace(strLine, strSearchtext8, strReplacetext8)
strLine = Replace(strLine, strSearchtext9, strReplacetext9)
strLine = Replace(strLine, strSearchtext10, strReplacetext10)
strLine = Replace(strLine, strSearchtext11, strReplacetext11)
strLine = Replace(strLine, strSearchtext12, strReplacetext12)
strLine = Replace(strLine, strSearchtext14, strReplacetext14)
strLine = Replace(strLine, strSearchtext15, strReplacetext15)
strLine = Replace(strLine, strSearchtext16, strReplacetext16)
strLine = Replace(strLine, strSearchtext17, strReplacetext17)
strLine = Replace(strLine, strSearchtext18, strReplacetext18)
strLine = Replace(strLine, strSearchtext19, strReplacetext19)
strLine = Replace(strLine, strSearchtext20, strReplacetext20)
strLine = Replace(strLine, strSearchtext21, strReplacetext21)
strLine = Replace(strLine, strSearchtext23, strReplacetext23)
strLine = Replace(strLine, strSearchtext24, strReplacetext24)
strLine = Replace(strLine, strSearchtext25, strReplacetext25)
strLine = Replace(strLine, strSearchtext26, strReplacetext26)
strLine = Replace(strLine, strSearchtext27, strReplacetext27)
strLine = Replace(strLine, strSearchtext28, strReplacetext28)
strLine = Replace(strLine, strSearchtext29, strReplacetext29)

Print #2, strLine
Print #3, strLine
Loop

Close #1
Close #2
Close #3
End Sub

Reply | Quote

Replies

Hi Dave,

I don’t understand. You specify the search strings strSearchText1 etc. explicitly, and none of them is empty. So why would you have to check whether they’re empty? You know in advance that they aren’t…

Reply | Quote

Hans

Sorry I didn’t explain.
The search text looks for place holders in the template file which is : C:MM-UtilitiesATX Import ProjectAudaImport.txt
This file is used every time and contains everything.
Its the replace text which can contain no record.

OOOpps sorry !!

Reply | Quote

But the replace text will never be empty either – it will be the same length as the search text…

Reply | Quote

Hans

Sorry had to take a couple of days away from this.

Take for instance:

strSearchtext19 = “#ADDRESS3 #”
strReplacetext19 = Left(Me.OWN_ADD_3 & Space(100), Len(strSearchtext19))

strSearchtext looks for the string “#ADDRESS3 #” which is 11 characters and try’s to replace it, if there is nothing to replace it with then it will use “” because there is nothing in the table or field. So I would have to use something like:

strSearchtext19 = “#ADDRESS3 #” ‘(This will always be in the template – fixed length)
If strReplacetext19 = “” then
strReplacetext19 = ” ”
else
strReplacetext19 = Left(Me.OWN_ADD_3 & Space(100), Len(strSearchtext19)) ‘ (This could be empty if there is no data)
end if

Not sure if this makes sense but its the best way I can explain it.

Reply | Quote

I’m sorry, it’s still not clear to me.

Left(Me.OWN_ADD_3 & Space(100), Len(strSearchtext19)) will never be an empty string, it’s a string with the same length as strSearchText19, i.e. 11 characters. These could be 11 spaces, if Me.OWN_ADD_3 is null.

Reply | Quote