[FIXED] How can I detect the end of a text file (EOF) in vbscript?

Issue

I'm using VBscript to sort the information in a text file to an excel sheet. I am able to do so, but only one line of the text is being read, and the loop control doesn't seem to go to the next line of the text file. I am using AtEndOfStream for this, but as I said, I am getting only one line of output. Can someone help me figure out how to execute the program till the end of the file?

Here is the code:

Set objExcel = CreateObject("Excel.Application")            'Bind to the Excel object
objExcel.Workbooks.Add                                      'Create a new workbook.
Sheet = 1                                                   'Select the first sheet
Set objSheet = objExcel.ActiveWorkbook.Worksheets(Sheet)    'Bind to worksheet. 
objSheet.Name = "ErrorSpreadsheet"                          'Name the worksheet
strExcelPath = "c:\scripts\ErrorSpreadsheet.xlsx"           'Set the save location

objSheet.Range("A1:E1").Font.Bold = True
objExcel.Columns(5).AutoFit()
objSheet.Cells(1, 1).Value = "Date"                         'Row 1 Column 1 (A)
objSheet.Cells(1, 2).Value = "Time"                         'Row 1 Column 2 (B)
objSheet.Cells(1, 3).Value = "Category"                     'Row 1 Column 3 (C)
objSheet.Cells(1, 4).Value = "Error"                        'Row 1 Column 4 (D)
objSheet.Cells(1, 5).Value = "Index"                        'Row 1 Column 5 (E)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("c:\scripts\ErrorMsg.txt",1)

i = 0
r = 2
c = 1
j = 0
Do While NOT objFile.AtEndOfStream 
    Redim Preserve arrFileLines(10)
    arrFileLines(i) = objFile.ReadLine
    text = arrFileLines(j)
    'Dim a() 
    a = Split(text,";")
    For Each line In a
        objSheet.Cells(r,c).Value = a(i)
        i = i + 1
        c = c + 1
    Next
    j = j + 1
    r = r + 1
Loop


objFile.Close
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

I've written the code now in such a way that I do not get any errors. In my excel file, I have both the heading line as well as one line of the split text file, but not any of the following lines. The text file is written like this:

       9/23;12:00;cat;error;236
       9/24;12:30;cat2;error;897
       9/24;4:06;cat5;error;23
       9/25;3:45;cat1;error;54
       9/26;1:09;cat6;error;18

So the output I'm getting in excel is Excel Output

Can someone help me figure out how to get to the end of the text file?

Solution

As I mentioned in the comments, your problem is with your variables, because you are trying to reusing them, but not doing it properly. If you manually walk into your code, you will see that, in the first iteration you are adding objFile.ReadLine to arrFileLines(0) and then storing arrFileLines(0) to text
But then you are walking in your inside For loop and iterating i, which would leave the For loop as 4 after the split.

The second time you go into your loop, you will be adding objFile.ReadLine to arrFileLines(4) and then storing arrFileLines(1) (which is empty) to text. You will not get any errors because the array has fixed dimension and will be inside the range until you finish walking through your file, but you will not get any results either.
This is why I would advise you to use different variables and avoid that re-usage.
Actually, if the only purpose is to add the values from CSV into the Excel sheet, you don't even need to store objFile.ReadLine, into arrFileLines since you are not using the array. Just add it directly to Text.

So, with a couple of modifications, like variable renaming and so forth, you would end up with something like this:

' The rest of your code, Variables declarations and so forth

iRow = 2    ' Starting Row
iCol = 1    ' Starting Col

Do While Not objFile.AtEndOfStream
    currLine = objFile.ReadLine
    arrLine = Split(currLine, ";")
    For Each item In arrLine
        objSheet.Cells(iRow, iCol).Value = item
        iCol = iCol + 1
    Next
    iCol = 1    ' Reset for every row
    iRow = iRow + 1
Loop

' The rest of your code

Answered By - Victor Moraes

Answer Checked By - David Goodson (PHPFixing Volunteer)