[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

Using criteria to generate a new text file which replace orginal value in text file

I have a text file with 2 column and about 2000 rows of value. It looks like below

709.0 1220.0
391.0 1181.0
295.0 303.0
289.0 1303.0
2714.0 303.0
2745.0 1303.0
261.0 1254.0
And so on…

And a value corresponded criteria like
100~200 =A,
201~300 =B
Over 100 = K
and so on…

I would to write a program that take the whole file and substitute each number with a symbol of your choice (e.g., A, B...K.). The result should be a new file with two columns of symbolic names like below

G K
C K
B C
B K
K C
And so on….
0
hsuyf
Asked:
hsuyf
  • 6
  • 3
  • 3
  • +2
1 Solution
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Hi hsuyf,

You have no question history...what language are you working in here?

    VBScript (.vbs)
    VBA (MSOffice)
    VB6
    VB.Net 2003
    VB.Net 2005
0
 
PockyMasterCommented:

the following will do something like this in VB6, but note : I calculate a symbol name like you described, but e.g. 2700 will result in non-letter characters!

Option Explicit

Private Sub Command1_Click()

Dim FileNr As Long
FileNr = FreeFile
Open "c:\test.txt" For Input As #FileNr
Dim FileNrOutput As Long
FileNrOutput = FreeFile
Open "c:\test_out.txt" For Output As #FileNrOutput

'open your file


Dim sLine As String

'read your lines
While Not EOF(FileNr)

Dim Items() As String
'get the data
Input #FileNr, sLine

Items = Split(sLine, " ")

'
If UBound(Items) >= 1 Then

    Dim sNewVal1 As String
    Translate Items(0), sNewVal1
    Dim sNewVal2 As String
    Translate Items(1), sNewVal2
   
    Print #FileNrOutput, sNewVal1 & " " & sNewVal2
   
End If


Wend

'close your file
Close (FileNr)
Close (FileNrOutput)

End Sub

Private Sub Translate(orgVal As String, ByRef newVal As String)

    Dim dNew As Double
    If Not IsNumeric(orgVal) Then Exit Sub
   
    dNew = CDbl(orgVal)
       
    newVal = Chr(Asc("A") + (dNew \ 100))
   
End Sub
0
 
hsuyfAuthor Commented:
I want to use VB6 to solve this question. Thanks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PockyMasterCommented:
I assumed you would, according the group you posted in :D
Did you try my solution?
0
 
hsuyfAuthor Commented:
Thanks for your hlep, I just try it but there is no any output showing. I am kind of confused here. could you please help me more about it.

my input file is a text file with thousand rows like below
354.0 1353.0
1655.0 1184.0
239.0 1266.0
289.0 1266.0
421.0 1253.0
249.0 1253.0
273.0 2124.0
709.0 1220.0
391.0 1181.0
and so on.....

and I want to set an If statement (or use case statement) like
if value <= 500, assign it to letter "A"
if value between 501 to 1000 assign it to letter "B"
if  value >= 1001 assign it to letter "c"

the output will be in a new "text file" and looks like
G K
C K
B C
B K
K C
And so on….



0
 
PockyMasterCommented:
In my example I write your output to "c:\test_out.txt"
if you want it to be divided per 500, change the example code to:
newVal = Chr(Asc("A") + (dNew \ 100)) --> newVal = Chr(Asc("A") + (dNew \ 500))



0
 
AtanAsfalothCommented:
This will do the trick, I think...





Private Sub InterpreteFile()
    'Declare Vars
    Dim lngInputHandle As Long
    Dim lngOutputHandle As Long
    Dim strLine As String
    Dim arrValues() As String
    Dim I As Integer
    Dim strOutputLine As String
    Dim strCharacter As String
    '
    'Open input and outputfiles
    lngInputHandle = FreeFile
    Open "C:\inputfile.txt" For Input As #lngInputHandle
    lngOutputHandle = FreeFile
    Open "C:\ouputfile.txt" For Output As #lngOutputHandle
    '
    'Start reading
    Do Until EOF(lngInputHandle)
        'Empty outputlinevariable
        strOutputLine = ""
        '
        'Read line
        Line Input #lngInputHandle, strLine
        'Separate the numbers
        arrValues = Split(strLine, " ")
        '
        'Loop through the numbers on one line
        For I = 0 To UBound(arrValues)
            '
            'Check Conditions
            Select Case CDbl(Replace(arrValues(I), ".", ","))
            Case Is <= 500 'If value <= 500 --> output A
                strCharacter = "A"
            Case Is <= 1000 'If 501 <= value <= 1000 --> output B
                strCharacter = "B"
            Case Is >= 1001 'If value >= 1001 --> output C
                strCharacter = "C"
            Case Else
                strCharacter = "?"
            End Select
            '
            'Add character to outputline
            strOutputLine = strOutputLine & strCharacter & " "
        Next I
        '
        'Remove trailing space
        strOutputLine = Left(strOutputLine, Len(strOutputLine) - 1)
        '
        'Write line to outputfile
        Print #lngOutputHandle, strOutputLine
    Loop
    '
    'Close files
    Close #lngInputHandle, #lngOutputHandle
End Sub



Atan Asfaloth
0
 
PockyMasterCommented:
You might use a case statement like AtanAsfaloth, but that will leave you stuck to A,B,C

The Chr/Asc construction will take you A..Z and further , in less code!
0
 
AtanAsfalothCommented:
That is completely true, however, if you want to use codes like A, B, C, F, K, Z, L, etc... in a non-standard way, my code allows this :)
0
 
PockyMasterCommented:
Yeah, but then I would still prefer a construction like:

Private Function GetCode(Index as integer ) as string
dim myIndexes as string = "ABZFSWERPQ"
return myIndex.SubString(Index, 1)
End Function

But that's all a matter of taste :D
0
 
hsuyfAuthor Commented:
Hi thanks for your quickly hlep! ^_^

but first,  In the output file ('c:\test_out.txt"), it shows strange character in one line looks like  below

&#8259;†††††&#8260;&#8269;†††††

can I have result looks like2 columns with as many rows as orginal input looks like
C K
B C
B K
K C
And so on….

by the way, instead of ascending (or descending) order,
what if I want to use "if statement" to assing number to words something like
if value <= 500, assign it to "Monday"
if value between 501 to 1000 assign it to  "Friday"
if  value >= 1001 assign it to  "Satuturday"
and output file looks like

Monday Friday
Saturday Friday
Friday Monday
and so on...

Thanks
0
 
AtanAsfalothCommented:
Using my script above, you can easily replace the A, B and C by eveything you like....
0
 
cool12399Commented:
Hi,

I'm assuming you are using chinese windows. The output is being written as unicode.
To get rid of the strange characters, output a file as binary. So basically this code:

Option Explicit

Private Sub Command1_Click()

Dim FileNr As Long
FileNr = FreeFile
Open "c:\test.txt" For Input As #FileNr
Dim FileNrOutput As Long
FileNrOutput = FreeFile
Open "c:\test_out.txt" For Binary As #FileNrOutput

'open your file


Dim sLine As String

'read your lines
While Not EOF(FileNr)

Dim Items() As String
'get the data
Input #FileNr, sLine

Items = Split(sLine, " ")

'
If UBound(Items) >= 1 Then

    Dim sNewVal1 As String
    Translate Items(0), sNewVal1
    Dim sNewVal2 As String
    Translate Items(1), sNewVal2
   
    Print #FileNrOutput, sNewVal1 & " " & sNewVal2
   
End If


Wend

'close your file
Close (FileNr)
Close (FileNrOutput)

End Sub

Private Sub Translate(orgVal As String, ByRef newVal As String)

    Dim dNew As Double
    If Not IsNumeric(orgVal) Then Exit Sub
   
    dNew = CDbl(orgVal)
       
    newVal = Chr(Asc("A") + (dNew \ 100))
   
End Sub
0
 
PockyMasterCommented:
No, it's just returning strange characters because of the Translate function :D
If he's inputting values that are higher than 2600 than you will go out of the alphabet and end up in strange numbers :D

Just build in your translation rules in the translate function and you're done
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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