Parse csv file directly into excel 1

[rlee80]

Hi,

I have a file that I want to parse test.txt which is a csv file, there are 335 lines in this file with email users names and ins and outs etc. This code creates and formats the excel worksheet ok but when parsing the data directly into the worksheet there are only about 175 lines it seems to miss out a line when it loops through the file. Can anyone give me an idea of where I am going wrong

The format of the file is 335 lines like the one below

2004/06/03,00:02:35,in2,someone@internal.mail,postman@internal.mail,696,C:\MDAEMON\LOCALQ\md50000465133.msg,PostMan

Also I get input past end of file error!!

Thanks in advance

Rob



Option Explicit

Dim objFSO, objTextfile, objNewTextFile, objXL, arrEmailLog, i, count

Const ForReading = 1
'Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextfile = objFSO.OpenTextFile("C:\test.txt", ForReading)
'Set objNewTextFile = objFSO.OpenTextFile("C:\results.txt", ForWriting)

Set objXL = WScript.CreateObject("Excel.Application")

objXL.Visible = True

objXL.Workbooks.Add
objXL.Cells(1,1).Value = "Users E-Mail:" 'format excel spreadsheet
objXL.Cells(1,2).Value = "Date"
objXL.Cells(1,3).Value = "Time"
objXL.Cells(1,4).Value = "Direction"
objXL.Cells(1,5).Value = "Size in Bytes"

objXL.Columns(1).ColumnWidth = 30
objXL.Columns(2).ColumnWidth = 10
objXL.Columns(3).ColumnWidth = 10
objXL.Columns(4).ColumnWidth = 10
objXL.Columns(5).ColumnWidth = 12


count=2

Do While objTextfile.AtEndOfStream <> True 'loops through test.txt till end

If inStr(objTextfile.Readline, ",") Then

arrEmailLog = split(objTextFile.Readline, ",") 'create array from textfile

Wscript.Echo "User Name: " & arrEmailLog(3) 'use array to display data needed
Wscript.Echo "Date: " & arrEmailLog(0)
Wscript.Echo "Time: " & arrEmailLog(1)
Wscript.Echo "Direction: " & arrEmailLog(2)
Wscript.Echo "Size Bytes: " & arrEmailLog(5)


objXL.Cells(count,1).Value = arrEmailLog(3) 'use array to populate cells with data needed
objXL.Cells(count,2).Value = arrEmailLog(0)
objXL.Cells(count,3).Value = arrEmailLog(1)
objXL.Cells(count,4).Value = arrEmailLog(2)
objXL.Cells(count,5).Value = arrEmailLog(5)


Else

WScript.Echo "wrong file"

End If

count=count+1
'i=i+1

Loop

objTextFile.Close

 

[PHV]

What happens when you open the csv file directly from excel ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rlee80]

when opened in excel all the lines are there, the code seems to only parse every other line

Rob

 

[tsuji]

Hello rlee80,

Sure every other line.

Do While objTextfile.AtEndOfStream <> True 'loops through test.txt till end
    s=objTextfile.Readline    
    If inStr(s, ",") Then 
        
        arrEmailLog = split(s, ",") 'create array from textfile

regards - tsuji

 

[tsuji]

Further note:

Add declaring s
dim s
as you have Option Explicit.

- tsuji

 

[rlee80]

Thanks for that info tsuji, all data needed parses directly to excel now

Rob