Help With Tab Delimited File

[kimmelsd33]

I would like some expert advice. I am writing in VB6. I am opening a tab delimited file, deleting the first 50 lines, and rewriting the file to a temp file. The temp file has about 20 columns with x number of lines. What I am trying to do, is open the tab delimited file, and read each column into a named variable, ex. depth<column1>m pf<column2>tg<c olumn3>,etc. I have written a function to take the variable from column2(mpf) & create another variable(fph) to insert back into the file, at column2. This would move everything from column2(mpf) on, up one column. The columns and variables are as follows:
depth(column1), mpf(column2),tg (column3),c1(co lumn4),etc. with the variables. I need to create the variable fph and insert it into column2, where the new file will read as follows:
depth(column1), fph(column2),mp f(column3),tg(c olumn4),c1(colu mn5),etc to the end of the line. The code I have is as follows:

Public tFile As String
Public mpf As Double
Public fSave As String
 
Public Function TrimAll(Str)
'***remove all non ASCI chrs and reduce internal whitespace to single
    Dim i, strTemp, strOut, strCh
 
    strTemp = Str
    For i = 1 To Len(strTemp)
        strCh = Mid(strTemp, i, 1) '***look at each character in turn
        '***if the chr is a space and the previous added chr was a space ignore it
        '***otherwise add it on
        If Not (strCh = " " And Right(strOut, 1) = " ") Then
            strOut = strOut & strCh
        End If
    Next
    TrimAll = strOut
End Function
 
Private Sub cmdConvert_Click()
    Dim Script As Object
    Dim ObjStream As Object
    Dim strLine As String
    Dim a As Integer
 
    'check for .las file
    Set Script = CreateObject("Scripting.FileSystemObject")
     If Script.FileExists(tFile) Then
        a = True
        Set ObjStream = Script.opentextfile(tFile, 1, False, 0)
     Else:
        MsgBox "There is no LAS data in file. Please update with new LAS data.", vbInformation + vbOKOnly
        a = False
        Exit Sub
     End If
 
    If tFile > "" Then
    'open file
    Set Script = CreateObject("Scripting.filesystemobject")
    Open tFile For Input As #1 'read from this file
    Open "C:DB_CUETemp.dat" For Output As #2 'write to this file
 
    'delete header and print rest of data to temp file
    If a = True Then
        Dim i As Integer
        i = 0
            Do While i < 50 And Not ObjStream.AtEndOfStream
                strLine = ObjStream.Readline
        i = i + 1
        Loop
 
        Do While Not ObjStream.AtEndOfStream
          strLine = TrimAll(ObjStream.Readline)
          YourArray = Split(strLine, "  ") '***use the tab key to create space between quotes.***
        Print #2, strLine
        Loop
    End If
 
    Close #1
    Close #2
 
    'save file
    With CommonDialog1
        .Filter = "All Files|*.las*|"
        .InitDir = "C:DB_CUE"
        .DialogTitle = "Save File As"
        .ShowSave
    End With
    fSave = CommonDialog1.FileName
    'copy temp file
    FileCopy "C:DB_CUETemp.dat", fSave '& ".las"
    'delete temp file once copied
    Kill "C:DB_CUETemp.dat"
 
        If MsgBox("File is complete. Would you like to view the file?", vbQuestion + vbYesNo, "LAS iGenerator") = vbYes Then
            Shell ("Explorer.exe tFile"), vbNormalFocus
'            Unload Me
        End If
    Else:
    If MsgBox("You must select a file to convert before you can continue.", vbExclamation + vbOKOnly) = vbOK Then
        Exit Sub
    End If
    End If
End Sub

Could you help shed some light on this, or refer me to someone who may? Thanks for your time.

[vb5prgrmr]

This should be faster and easier to read...

Dim FName As String, FNumb As Integer
Dim InnerForLoopCounter As Integer, OuterForLoopCounter As Integer
Dim LineArray() As String, ElementArray() As String
Dim InputText As String, OutputText As String
 
FName = "InputPathFileName" 'need to change
FNumb = FreeFile
 
Open FName For Input As #FNumb
InputText = Input(FileLen(FName), #FNumb)
Close #FNumb
 
LineArray = Split(InputText, vbNewLine)
 
'Skip First 50
For OuterForLoopCounter = 50 To UBound(LineArray)
  ElementArray = Split(LineArray(ForLoopCounter), vbTab)
  OutputText = OutputText & ElementArray(0) & vbTab "item to insert"
  For InnerForLoopCounter = 1 To UBound(ElementArray)
    OutputText = OutputText & vbTab & ElementArray(InnerForLoopCounter)
  Next InnerForLoopCounter
  OutputText = OutputText & vbNewLine
Next OuterForLoopCounter
 
FName = "OutputPathFileName" 'need to change
FNumb = FreeFile
 
Open FName For Output As #FNumb
Print #FNumb, OutputText
Close #FNumb
 

NOTE: Done freehand and so is untested and from memory so if I happened to flip something around, well Oopps! Sorry!

Also, since inner for loop is looping through a constant number of elements you could get the upper bounds of the array prior to entering the outer for loop and thus speed up this algorithm by a considerable amount (upwards of 50% faster).

Good Luck

[kimmelsd33]

Thanks for the response. This code is not putting the tab delimited file back together, and I have a function that the data in the second column, needs to run through, thus creating data for another variable and column of data. This new column of data needs to be inserted behind the first column, once the file is being written to the temp file. Does that make sense? If I can place each column of data into certain variable, pertained to each column, then I believe it would be easier to run the named variable through the function, and that value stored in another variable, and inserted into the temp file in a tab delimited text file. ? Not sure, but I think the logic would work. I am having problems creating it as I need it. Does this make sense?

[vb5prgrmr]

Okay, then... Where I have "item to insert" is where you would put your function that returns a string and I think you said it does its thing from the 2nd colume so it would be...

  OutputText = OutputText & ElementArray(0) & vbTab & MyFunction(ElementArray(1))
 

As for not reassembling the file... It works here for me but if you are wanting the first 50 lines to be reassembled back into the file then you will need another string variable... or...

ReDim Preserve LineArray(49) As String
 
FName = "OutputPathFileName" 'need to change 
FNumb = FreeFile 
 
Open FName For Output As #FNumb 
Print #FNumb, Join(LineArray, vbNewLine) & vbNewLine & OutputText 
Close #FNumb 
 

Not sure about the second vbNewLine...



Good Luck

[Guido Geurs]

dear,

I this a solution? see attachment

br,

[kimmelsd33]

Thanks again vbprgrmr. I must be doing something wrong. It is giving me an error message. This is what I have:
[code]
Public Sub Perform()
Dim FName As String, FNumb As Integer
Dim InnerForLoopCou nter As Integer, OuterForLoopCou nter As Integer
Dim LineArray() As String, ElementArray() As String
Dim InputText As String, OutputText As String

FName = "C:\DB_CUE\test file.las" 'need to change
FNumb = FreeFile

Open FName For Input As #FNumb
InputText = Input(FileLen(F Name), #FNumb)
Close #FNumb

LineArray = Split(InputText , vbNewLine)

'Skip First 50
For OuterForLoopCou nter = 50 To UBound(LineArra y)
ElementArray = Split(LineArray (ForLoopCounter ), vbTab)
OutputText = OutputText & ElementArray(0) & vbTab & CalcFPH(Element Array(1)) ' "item to insert"
For InnerForLoopCou nter = 1 To UBound(ElementA rray)
OutputText = OutputText & vbTab & ElementArray(In nerForLoopCount er)
Next InnerForLoopCou nter
OutputText = OutputText & vbNewLine
Next OuterForLoopCou nter

FName = "C:\DB_CUE\TEST 1.txt" 'need to change
FNumb = FreeFile

Open FName For Output As #FNumb
Debug.Print OutputText
Print #FNumb, OutputText
Close #FNumb
End Sub

Public Function CalcFPH() As String
CalcFtPerHr = 60 / mpf
End Function
[code]

I don't need it to reassemble adding the first 50 lines of data. I just need it to reassemble adding the extra function variable in column 2. Thanks for all your help. It is coming along though with your great help and patience. Thanks again

[kimmelsd33]

Thanks again vbprgrmr. I must be doing something wrong. It is giving me an error message. This is what I have:

Public Sub Perform()
Dim FName As String, FNumb As Integer
Dim InnerForLoopCounter As Integer, OuterForLoopCounter As Integer
Dim LineArray() As String, ElementArray() As String
Dim InputText As String, OutputText As String
 
FName = "C:\DB_CUE\testfile.las" 'need to change
FNumb = FreeFile
 
Open FName For Input As #FNumb
InputText = Input(FileLen(FName), #FNumb)
Close #FNumb
 
LineArray = Split(InputText, vbNewLine)
 
'Skip First 50
For OuterForLoopCounter = 50 To UBound(LineArray)
ElementArray = Split(LineArray(ForLoopCounter), vbTab)
OutputText = OutputText & ElementArray(0) & vbTab & CalcFPH(ElementArray(1)) ' "item to insert"
For InnerForLoopCounter = 1 To UBound(ElementArray)
OutputText = OutputText & vbTab & ElementArray(InnerForLoopCounter)
Next InnerForLoopCounter
OutputText = OutputText & vbNewLine
Next OuterForLoopCounter
 
FName = "C:\DB_CUE\TEST1.txt" 'need to change
FNumb = FreeFile
 
Open FName For Output As #FNumb
Debug.Print OutputText
Print #FNumb, OutputText
Close #FNumb
End Sub
 
Public Function CalcFPH() As String
CalcFtPerHr = 60 / mpf
End Function
 

I don't need it to reassemble adding the first 50 lines of data. I just need it to reassemble adding the extra function variable in column 2. Thanks for all your help. It is coming along though with your great help and patience. Thanks again

[vb5prgrmr]

okay, with this code (which is the same)...

Option Explicit
 
Private Sub Command1_Click()
Dim FName As String, FNumb As Integer
Dim InnerForLoopCounter As Integer, OuterForLoopCounter As Integer
Dim LineArray() As String, ElementArray() As String
Dim InputText As String, OutputText As String
 
FName = "e:\temp\testfile.txt" 'need to change
FNumb = FreeFile
 
Open FName For Input As #FNumb
InputText = Input(FileLen(FName), #FNumb)
Close #FNumb
 
LineArray = Split(InputText, vbNewLine)
 
'Skip First 50
For OuterForLoopCounter = 50 To UBound(LineArray)
  ElementArray = Split(LineArray(OuterForLoopCounter), vbTab)
  OutputText = OutputText & ElementArray(0) & vbTab & "item to insert"
  For InnerForLoopCounter = 1 To UBound(ElementArray)
    OutputText = OutputText & vbTab & ElementArray(InnerForLoopCounter)
  Next InnerForLoopCounter
  OutputText = OutputText & vbNewLine
Next OuterForLoopCounter
 
FName = "e:\temp\output.txt" 'need to change
FNumb = FreeFile
 
Open FName For Output As #FNumb
Print #FNumb, OutputText
Close #FNumb
 
End Sub
 

the attached file is the output...

Which looks just fine (NOTE: there was one change in the code on this line (ElementArray = Split(LineArray (OuterForLoopCo unter), vbTab)) the loop counter was previously just ForLoopCounter and needed to be OuterForLoopCou nter and that is what option explicit is for to catch those undeclared variables...Too ls>Options>Requ ire Variable declaration>ok)



Good Luck

[Guido Geurs]

dear,

Maybe this tool will do it. see attachment
Please call me if you want any modifications.

br,

[kimmelsd33]

The code works great. The problem I am having problems with is the "item to insert". I need the ElementArray(1) to process through a function, and return a value stored in a variable, and reinserted. I have tried a couple of ways to modify your code to make it happen, but it's not working. The function ElementArray(1) needs to go through is:

Function CalcFPH(ElementArray) as String
CalcFPH=60/ElementArray(1)
End Function
 

I've tried creating a variable such as FPH to store the value of the function in, and then insert that, but my output is coming up blank. The key is to process the second column through the function and reinsert the value back into the second column when it writes the file. Other than that, it all works well. Thanks for your help