Visual Basic Question

[TomElZ]

Hey.

I've been asked to create a string slicing programme that will take data from a spreadsheet and output it to a delimited ASCII file. The data in the text file follows the structure as shown below:

<time:00:00:00:date:01/10/00:lat:52.34:long:28.30:wind:20:temp:-12recipitation:0.03:visibility:50>

The text file has about 7-8 rows off data following the same structure but with a few changes. So I want to remove the semi-colons from each row of data and format the text file so it has the correct columns on the first row and then the data below but with the long, lat removed etc. However I can't just remove all the semi colons as time uses them.

Time Date Lat Long Wind

Like that type of structure. I haven't got a clue how to start. I've read that I could read the text file into an array and then go from there.

Any help or pointers would be appreciated.

Cheers.

 

[jonifen]

First off, are you using VB6 or VB.NET?
If you are using VB6 then read on. If VB.NET, the below may point you in the right direction but the code probably won't work).


Because the fields are delimited with colons, you could use the Split() command to get it into an array. Unfortunately, it would cause a problem with the Time field which the hours/mins/secs are also separated by colons.

However, if the fields are always in that order, you'd know the time would be across 3 parts of the array.

You could do something like this.

Private Sub SplitStringToArray() 
Dim sArray As String()
Dim sInput As String
Dim sTime As String
Dim sDate As String
Dim sLat As String
Dim sLong As String
Dim sWind As String
Dim sTemp As String
Dim sPrec As String
Dim sVis As String

sInput = "<time:00:00:00:date:01/10/00:lat:52.34:long:28.30:wind:20:temp:-12:Precipitation:0.03:visibility:50>"

'Remove the pointy brackets at each end
sInput = Replace(sInput,"<","")
sInput = Replace(sInput,">","")

'Split to array
sArray = Split(sInput,":")

'The time is built with 3 array entries "00", "00" and "00" (1, 2 and 3)
sTime = sArray(1) & ":" & sArray(2) & ":" & sArray(3)
sDate = sArray(5)
sLat = sArray(7)
sLong = sArray(9)
sWind = sArray(11)
sTemp = sArray(13)
sPrec = sArray(15)
sVis = sArray(17)

End Sub

I've used String values for all the fields you'd pull out for ease of use - you can use Date or Integer/Double instead if you prefer though - the code is just to help you on the way.

I've not tested the code by the way (I don't have VB6 on this machine to test it), but it should work

Edit: re-read the original post. You can use the variables created to organise the data you want, and ditch what you don't want. You could then build up the line you're outputting to the output file and write it out.

 

[TomElZ]

How can I go about displaying this in say for example a text box to see if it's what I need?

 

[jonifen]

If you had a textbox called Text1 and you were showing the time you'd pulled out, you'd do:

Text1.Text = sTime

If you were building the output (so adding multiple fields) you'd just add the extra fields doing:

Text1.Text = sTime & " " & sDate & " " & sTemp

I've put spaces between the fields there, you could put a tab in if you preferred, doing:

Text1.Text = sTime & vbTab & sDate & vbTab & sTemp

instead.

Hope that helps

 

[TomElZ]

Yeah that really helps chears.

How could I go about formatting the first row to be the field names?

 

[jonifen]

In my first post, where I dumped out the time, date etc from the array into separate string variables, you'd do something similar for the headers as they're in the array too.

The header name is in the array part before the value.

sTimeHeader = sArray(0)
sTime = sArray(1) & ":" & sArray(2) & ":" & sArray(3)
sDateHeader = sArray(4)
sDate = sArray(5)
sLatHeader = sArray(6)
sLat = sArray(7)
sLongHeader = sArray(8)
sLong = sArray(9)
sWindHeader = sArray(10)
sWind = sArray(11)
sTempHeader = sArray(12)
sTemp = sArray(13)
sPrecHeader = sArray(14)
sPrec = sArray(15)
sVisHeader = sArray(16)
sVis = sArray(17)

The code above might have explained it better...

If you wanted to just output the headings you mentioned in the first post, you could do this:

Dim sHeaderLine As String

'This should output the header as "Time Date Lat Long Wind"
sHeaderLine = sArray(0) & " " & sArray(4) & " " & sArray(6) & " " & sArray(8) & " " & sArray(10)

And write that to the file first (before any line of data).

 

[Dave M]

removed

charming, you could have left your question to help others in future.

 

[TomElZ]

Cheers for your help.

That method is perfect if I was just using one row of data. However the idea is to try to read all the data from the text file into VB then do the formatting.

Any ideas?

Cheers.

 

[jonifen]

As it's a text file you can read it in line by line, do the conversion on the current line, write it to the output file and go onto the next line and so on.

Dim bFirstLine As Boolean
Dim sFirstLine As String
Dim sLine As String

bFirstLine = True

Open "c:\input.txt" For Input As #1
Open "c:\output.txt" for Append As #2

'Now iterate the lines
While Not EOF(1)
Line Input #1, sLine

'Do the data conversion here and generate the output line as sOutput

'Make the header only once (hence the bFirstLine marker)
If bFirstLine = True Then
'Create the first line here to sHeader
Print #2, sHeader
bFirstLine = False
End If

'Now write the line to the output file
Print #2, sOutput
Wend

Close #2
Close #1

Again, I don't have access to vb (I'm on my phone lol) to verify that it works 100% but I've checked it through and it looks good (I'm a vb6 programmer for my job by the way )

 

[jonifen]

To add to my previous posts, I've got the laptop out and given it a whirl in VB6 for you. I can't attach the zipped project (maybe the forum doesn't allow attachments, I don't know) so I've just copied the code below.

My example had one form with a button (Command1) on it.

Option Explicit

Private Sub Command1_Click()
    On Error GoTo ErrorHandler
    
    Dim bFirstLine As Boolean
    
    Dim sLineArray() As String
    
    Dim sHeader As String
    Dim sLine As String
    
    Dim sOutput As String
    
    Dim sTime As String
    Dim sDate As String
    Dim sLat As String
    Dim sLong As String
    Dim sWind As String
    
    
    bFirstLine = True
    
    Open App.Path & "\input.txt" For Input As #1
    Open App.Path & "\output.txt" For Append As #2
    
    'Now iterate the lines
    While Not EOF(1)
        Line Input #1, sLine
        
        'Do the data conversion here and generate the output line as sOutput
        sLineArray = SplitStringToArray(sLine)
        
        'Check that sLineArray is a valid array (covers your back incase of a differently formatted line)
        If IsValidArray(sLineArray) Then
            sTime = sLineArray(1) & ":" & sLineArray(2) & ":" & sLineArray(3)
            sDate = sLineArray(5)
            sLat = sLineArray(7)
            sLong = sLineArray(9)
            sWind = sLineArray(11)
            
            sOutput = sTime & " " & sDate & " " & sLat & " " & sLong & " " & sWind
            
            'Make the header only once (hence the bFirstLine marker)
            If bFirstLine = True Then
                'Create the first line here to sHeader
                sHeader = sLineArray(0) & " " & sLineArray(4) & " " & sLineArray(6) & " " & sLineArray(8)
                
                Print #2, sHeader
                bFirstLine = False
            End If
            
            'Now write the line to the output file
            Print #2, sOutput
        End If
    Wend
    
    Close #2
    Close #1
    
    MsgBox "Complete!"
    
    Exit Sub
ErrorHandler:
    MsgBox "An error has occurred!" & vbNewLine & _
           "Error Number: " & Err.Number & vbNewLine & _
           "Description:  " & Err.Description
End Sub


Private Function SplitStringToArray(ByVal sInputLine As String) As String()
    'Remove the pointy brackets at each end
    sInputLine = Replace(sInputLine, "<", "")
    sInputLine = Replace(sInputLine, ">", "")
    
    'Split to array
    SplitStringToArray = Split(sInputLine, ":")
End Function

Private Function IsValidArray(ByRef sArray() As String) As Boolean
    On Error GoTo ErrorHandler
    
    Dim lArrayCheck As Long
    
    lArrayCheck = UBound(sArray)
    IsValidArray = True
    
    Exit Function
ErrorHandler:
    IsValidArray = False
End Function

I've tested it through with a dummy file of:

<time:00:00:00:date:01/10/00:lat:52.34:long:28.30:wind:20:temp:-12:precipitation:0.03:visibility:50>
<time:00:05:00:date:01/10/00:lat:52.34:long:28.30:wind:21:temp:-13:precipitation:0.13:visibility:40>
<time:00:10:00:date:01/10/00:lat:52.34:long:28.30:wind:20:temp:-13:precipitation:0.15:visibility:42>

and I get the following in my output file:

time date lat long wind
00:00:00 01/10/00 52.34 28.30 20
00:05:00 01/10/00 52.34 28.30 21
00:10:00 01/10/00 52.34 28.30 20

Enjoy

Edit: I've added a check against the array (that is built from the input string/line). It covers your back from a line if it's not in the expected format - it'll just skip it instead