My Output .csv file.

[compxinu]

Hello All..

I have this VB code that will pull a file off of our host machine and dump it onto the pc. The VB code will then pars the file and put the appropriate data in a .csv file.

Problem:
My output .csv file has multiple columns and multiple rows. My Column2 and Column4 in Row1 are dropping to Row2 and so on. What should be in Row1 in Column2 and Column4 is showing up on Row2, but the rest of the data is okay. Hope this makes sense.

What am I doing wrong here? Here is my code:


Sub ReportViewerMain()
On Error GoTo ErrorHandler

Dim inputFolder As String
Dim fs

inputFolder = "C:\HP3k_Unispool_Detail"

Set fs = CreateObject("Scripting.FileSystemObject")
'Check to see if folder exists. Make it if not.
If fs.Folderexists(inputFolder) = False Then MkDir (inputFolder)

If FTPReport Then ViewReport (ParseReport)

Exit Sub
ErrorHandler:
Session.MsgBox Err.Description, vbCritical
End Sub
Function ParseReport() As String

On Error GoTo ErrorHandler

Dim inputFile As String
Dim outputFile As String
Dim line As String
Dim line1 As String
Dim line2 As String
Dim wholeLine As String
Dim filePath As String
Dim mydate As String
Dim scrap() As String
Dim numFields As Integer

Dim count As Integer
Dim nextLine As Integer

Session.StatusBar = "Converting report to Excel CSV format."

'Set the date stamp for the filename
mydate = Format(Now(), "MMDDYY")

inputFile = "C:\HP3k_Unispool_Detail\unispoolrawdata_" & mydate & ".txt"
outputFile = "C:\HP3k_Unispool_Detail\unispooldetail_" & mydate & ".csv"

Close #1
Close #2

'Delete outputfile if it exits
If Dir(outputFile) <> "" Then Kill outputFile

Open inputFile For Input As #1
Open outputFile For Append As #2

'Print the column headers in outputFile
Print #2, "Machina Name,Print Destination System,Job Number,Job Name,User Name,Spoolfile ID,File Name,Device,Source Spool ID,Source Device,Device Type,Priority,Lines,Date,Time,Date Close,Time Close"

count = 1
nextLine = 1


' Loop thru report until we get to the line we care about
Do Until EOF(1)
Line Input #1, line
If line Like "*End of configuration dumped on host*" Then Exit Do
Loop

'If we reached end of file then this is the wrong sort of file.
If EOF(1) Then
Session.MsgBox inputFile & vbCrLf & "Is not a known file format.", _
vbCritical, "Cannot Convert File"

ParseReport = ""
Exit Function
End If


'Now we parse the report that we care about
'Raw data comes in couplets like the below. We don't know whether we get a line1 or a line2 first.
'hp ; ; ;#O918299 ;L030;#O918299 ;
'#J'9739 ;NP444444;MGR.SYS ;A960ADCL;L513 ;4 ; 0; 24;09/19/04;23:51;09/20/04;01:41;

Do Until EOF(1)
'read a line of raw data
Line Input #1, line

'Ignore empty lines
If Trim(line) <> "" Then

'If we are looking for the first line of a couplet
If nextLine = 1 Then

'make sure it is a first line check for the # that always starts line2
If VBA.Left(line, 1) <> "#" Then

line1 = line

'Sometimes first line of data comes without delimiters
'If this is the first few lines, check that we have correct number of fields
If count < 6 Then
'I load up the scrap array and check UBound to count fields
scrap = Split(line1, ";", , vbTextCompare)
numFields = UBound(scrap, 1)
If numFields <> 6 Then
'we have to add semicolons to delimit fields.
line1 = line1 & ";;;;;;"
End If
End If
'Now we are ready for the second line of the couplet
nextLine = 2
End If

Else
line2 = line
wholeLine = line1 & line2
Print #2, ParseLine(wholeLine)

count = count + 1
'Set this back to line1
nextLine = 1
End If

End If
Loop

Close #1
Close #2

ParseReport = outputFile

Exit Function
ErrorHandler:
Session.MsgBox Err.Description, vbCritical
Close #1
Close #2
ParseReport = ""

' Debug.Print Err.Number & " " & Err.Description
' Stop
' Resume

End Function 'ParseReport

Function ParseLine(line As String) As String
On Error GoTo ErrorHandler

Dim lineArray() As String
lineArray = Split(line, ";", , vbTextCompare)


'The order for below as set by End User
'0, 1, 6, 7, 8, 3, 9, 10, 5, 10, 11, 12, 13, 14, 15, 16, 17,

ParseLine = Trim(lineArray(0)) & "," & _
Trim(lineArray(1)) & "," & _
Trim(lineArray(6)) & "," & _
Trim(lineArray(7)) & "," & _
Trim(lineArray(8)) & "," & _
Trim(lineArray(3)) & "," & _
Trim(lineArray(9)) & "," & _
Trim(lineArray(10)) & "," & _
Trim(lineArray(5)) & "," & _
Trim(lineArray(10)) & "," & _
Trim(lineArray(11)) & "," & _
Trim(lineArray(12)) & "," & _
Trim(lineArray(13)) & "," & _
Trim(lineArray(14)) & "," & _
Trim(lineArray(15)) & "," & _
Trim(lineArray(16)) & "," & _
Trim(lineArray(17))

Exit Function
ErrorHandler:
Session.MsgBox Err.Description, vbExclamation + vbOKOnly
' Debug.Print Err.Number & " " & Err.Description
' Stop
' Resume


End Function

Function FTPReport() As Boolean

Dim FTP As New ReflectionFTP3
Dim inputFile As String
Dim host As String
Dim user As String
Dim pWord As String
Dim hostFile As String
Dim hostFolder As String
Dim mydate As String
Dim fileGood As Boolean


host = "hp"
user = "root"
hostFolder = "/dir1/dir2"


mydate = Format(Now(), "MMDDYY")
inputFile = "C:\HP3k_Unispool_Detail\unispoolrawdata_" & mydate & ".txt"

FTPReport = False

Do
hostFile = Session.InputBox("Please enter the report file name only.", "File Location: .UNISPOOL.SYS")

'If user cancels then exit
If hostFile = "" Then Exit Function
'Check file for filenames that are too long, or that contain spaces or periods.
If Len(hostFile) > 8 Or InStr(1, hostFile, ".", vbTextCompare) <> 0 Or InStr(1, hostFile, " ", vbTextCompare) <> 0 Then
fileGood = False
Else
fileGood = True
End If

Loop Until fileGood = True

'Promt for password exit if user cancels
pWord = Session.PasswordBox("Password for " & user & " on HP")

If pWord = "" Then
Exit Function
Else
'Add a comma if there is not one
'If VBA.Left(pWord, 1) <> "," Then pWord = "," & pWord
End If


With FTP
.Open host, user, pWord
If .LastError <> 0 Then
Session.MsgBox .LastErrorString
Exit Function
End If

.SetCurrentDirectory hostFolder

If .LastError <> 0 Then
Session.MsgBox "Error: cannot set host directory to unispool.sys" & vbCrLf _
& "FTP ERROR: " & .LastErrorString
FTPReport = False
Else
.ReceiveFile inputFile, hostFile, rcASCII, rcOverwrite

If .LastError <> 0 Then
Session.MsgBox "FTP Transfer Failed" & vbCrLf & "FTP ERROR: " & .LastErrorString
FTPReport = False
Else
FTPReport = True
End If

End If

.Close
End With

Set FTP = Nothing
Exit Function
ErrorHandler:
Session.MsgBox Err.Description, vbExclamation + vbOKOnly

End Function 'FTPReport

Sub ViewReport(report As String)
On Error GoTo ErrorHandler

Const xlmaximized = -4137

'Exit if error
If report = "" Then Exit Sub

Dim xl As Object
Set xl = CreateObject("Excel.Application")

xl.Visible = True
xl.WindowState = xlmaximized
xl.Workbooks.Open report

Exit Sub
ErrorHandler:
Session.MsgBox Err.Description, vbExclamation + vbOKOnly
End Sub

 

[SkipVought]

Hi,

Sounds like you may have a vbLf character in your Input File in Row 1 Column 1 position or sumthin'.

Please PASTE a few rows from your .csv file.

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[compxinu]

Here is partial copy and paste of my .csv output file.

Machina Name Job Number Job Name Spoolfile ID File Name Source Spool ID
hp0 #S11390 FORMS STAGF173
hp0 #S11384 FORMS #O918286 STAGF173 #O918286
hp0 #J'9739 NP454A09 #O918299 A960ADCL #O918299
hp0 #J'9739 NP454A09 #O918396 A961ADCL #O528290

This copy and paste looks sketchy, it isn't easy pasting formated columns and rows in this editor.

I had to delete some columns so it looked somewhat presentable in this. But as you can see the Spoolfile ID column and the Source Spoolfile ID column is dropping. The Spoolfile #O918286 should be in row 2 not row 3. Spoolfile ID data and the Source Spoolfile ID data are the same at times just so you don't think that it is a typo.

Any help will be greatly appreciated.

 

[SkipVought]

You did NOT give me the .csv source. You gave me what was in Excel. (.csv is Comma Separated Values)

Open the .csv in Notepad or Wordpad and COPY/PASTE DIRECTLY from there, please.

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[compxinu]

Sorry,here it is.



Machina Name,Print Destination System,Job Number,Job Name,User Name,Spoolfile ID,File Name,Device,Source Spool ID,Source Device,Device Type,Priority,Lines,Date,Time,Date Close,Time Close
hp00,,#S11390,FORMS,MGR.hp,,STAGF173,L030,,L030,4,0,365,09/22/04,14:19,09/19/04,23:35
hp00,,#S11384,FORMS,MGR.hp,#O918286,STAGF173,L030,#O918286,L030,4,0,365,09/19/04,23:35,09/19/04,23:51
hp00,,#J'9739,NP454A09,MGR.hpIC09,#O918299,A960ADCL,L513,#O918299,L513,4,0,24,09/19/04,23:51,09/20/04,01:41
hp0900,,#J'9739,NP454A09,MGR.hpIC09,#O918396,A961ADCL,L511,#O528290,L511,4,0,24,09/20/04,01:42,09/20/04,01:41

 

[SkipVought]

Here's the data for the two columns that you mentioned
[tt]
Spoolfile ID

#O918286
#O918299
#O918396

Source Spool ID

#O918286
#O918299
#O528290
[/tt]
#O918286 appears to be in row 2. Is that a problem?



Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[compxinu]

Actually, I gave you data from the .csv file which is the final output file.

Here is data from the raw data that is parsed. The raw report that is parsed is a txt file. Thank you in advance.

hp00
#S11390 ;FORMS ;MGR.ANTSISS ;STAGF173;L030 ;4 ; 0;
365;09/22/04;14:19;09/19/04;23:35;
hp00 ; ; ;#O918286
;L030;#O918286 ;
#S11384 ;FORMS ;MGR.ANTSISS ;STAGF173;L030 ;4 ; 0;
365;09/19/04;23:35;09/19/04;23:51;
hp00 ; ; ;#O918299
;L030;#O918299 ;
#J'9739 ;NP454A09;MGR.ILIOIC09 ;A960ADCL;L513 ;4 ; 0;
24;09/19/04;23:51;09/20/04;01:41;

 

[SkipVought]

Please explain this data to me. It does not correspond to the previously posted example. Also the first hp00 set ONLY has 3 lines and fewer fields????????
[tt]
Machina Name,Print Destination System,Job Number,Job Name,User Name,Spoolfile ID,File Name,Device,Source Spool ID,Source Device,Device Type,Priority,Lines,Date,Time,Date Close,Time Close
hp00,,#S11390,FORMS,MGR.hp,,STAGF173,L030,,L030,4,0,365,09/22/04,14:19,09/19/04,23:35
hp00,,#S11384,FORMS,MGR.hp,#O918286,STAGF173,L030,#O918286,L030,4,0,365,09/19/04,23:35,09/19/04,23:51
hp00,,#J'9739,NP454A09,MGR.hpIC09,#O918299,A960ADCL,L513,#O918299,L513,4,0,24,09/19/04,23:51,09/20/04,01:41
hp0900,,#J'9739,NP454A09,MGR.hpIC09,#O918396,A961ADCL,L511,#O528290,L511,4,0,24,09/20/04,01:42,09/20/04,01:41[/tt]

Please be clear where the problem is and what you are attempting to accomplish.

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[compxinu]

As mentioned in one of my recent post, the data posted earlier was a sample of the final output file. Which I should have given a sample of the data before it is parsed, the input data file.

Meaning, the input file is the most recent example you see in the last post by me is how the file is transmitted to my pc and then it is parsed. It is the raw file that is on the host system.

Make sense?

I am not sure of your question, but what else can I provide to assist with your question?

Thank you.

 

[compxinu]

Here is the scenario:

. There is a file on the host system, that looks like this:
hp00
#S11390 ;FORMS ;MGR.ANTSISS ;STAGF173;L030 ;4 ; 0;
365;09/22/04;14:19;09/19/04;23:35;
hp00 ; ; ;#O918286
;L030;#O918286 ;
#S11384 ;FORMS ;MGR.ANTSISS ;STAGF173;L030 ;4 ; 0;
365;09/19/04;23:35;09/19/04;23:51;
hp00 ; ; ;#O918299
;L030;#O918299 ;
#J'9739 ;NP454A09;MGR.ILIOIC09 ;A960ADCL;L513 ;4 ; 0;
24;09/19/04;23:51;09/20/04;01:41;

. The host system file is transmitted to the pc and still looks like above.
. The file on the pc is then parsed via VB and outputted to .csv file.
The problem is:
My output .csv file has multiple columns and multiple rows. My ColumnF and ColumnI in Row2 are dropping to Row3 and so on. What should be in Row2 in ColumnF and ColumnI is showing up on Row3, but the rest of the data is okay.

Sorry for the confusion on my end.. Hope this makes sense now.

Thank you.