reading a file that has null or blank field

hi,

when reading through a CSV file and a field is missing I receive a run time
error. I have the on error resume next commented out for now during
debugging. But, how do I handle if a field is missing when trying to assign
the array? In this case would missing mean "NULL"? The code is as follows:

set objFSO = CreateObject("Scripting.FileSystemObject")
set objSourceFile = objFSO.OpenTextFile(strSourceFilename, ForReading)
if err <> 0 then ErrHandler

do until objSourceFile.AtEndOfStream
arySourceFields = split(objSourceFile.ReadLine,",",-1,1)
if err <> 0 then ErrHandler
wscript.stdout.write arySourceFields(0) '''remove.
loop
.
.
.


Example of csv file contents:
500,world,1,1,5
501,now,2,6,0
.
.
.
600,,,
601

the problem occurs for records like 601 above. notice there are no commas
seperating other fields. the csv just has first field and nothing in
remaining fields like rest of records. Code works fine for all other
instances including 600,,,

Assigning arySourceFields(x) to any variable or simply doing the
stdout.write above causes runtime of:
createfiles.vbs(102,1) Microsoft VBScript runtime error: Subscript out of
range: 'wscript.stdout'

How do I prevent the error from occuring in my code without the on error
resume next? Isn't there a function that would check to see if the subject
is valid?

Thank you.

ekkehard
Wed Feb 28 10:20:21 CST 2007

RdS wrote:

> hi,
>
> when reading through a CSV file and a field is missing I receive a run time
> error. I have the on error resume next commented out for now during
> debugging. But, how do I handle if a field is missing when trying to assign
> the array? In this case would missing mean "NULL"?

No, there aren't NULL values in plain text files processed with
the FileSystemObject. The error results from trying to access
elements from arySourceFields which aren't there because Split()
couldn't create them due to lack of ","s.

> The code is as follows:
>
> set objFSO = CreateObject("Scripting.FileSystemObject")
> set objSourceFile = objFSO.OpenTextFile(strSourceFilename, ForReading)
> if err <> 0 then ErrHandler
>
Const UBOUND_EXPECTED = 4 ' 5 items indexed 0 ... 4

> do until objSourceFile.AtEndOfStream
> arySourceFields = split(objSourceFile.ReadLine,",",-1,1)
If UBOUND_EXPECTED <> UBound( arySourceFields ) Then
skip or raise hell
Else
work with all fields
End If
> if err <> 0 then ErrHandler
> wscript.stdout.write arySourceFields(0) '''remove.
> loop
> .
> .
> .
>
>
> Example of csv file contents:
> 500,world,1,1,5
> 501,now,2,6,0
> .
> .
> .
> 600,,,
> 601
>
> the problem occurs for records like 601 above. notice there are no commas
> seperating other fields. the csv just has first field and nothing in
> remaining fields like rest of records. Code works fine for all other
> instances including 600,,,
>
> Assigning arySourceFields(x) to any variable or simply doing the
> stdout.write above causes runtime of:
> createfiles.vbs(102,1) Microsoft VBScript runtime error: Subscript out of
> range: 'wscript.stdout'
>
> How do I prevent the error from occuring in my code without the on error
> resume next? Isn't there a function that would check to see if the subject
> is valid?
>
> Thank you.

RdS
Wed Feb 28 12:36:43 CST 2007

thanks for your reply.

I understand why it is happening, but I don't know how to handle such an
issue. Isn't there a function that would allow me to handle error?

thanks again.

"ekkehard.horner" wrote:

> RdS wrote:
>
> > hi,
> >
> > when reading through a CSV file and a field is missing I receive a run time
> > error. I have the on error resume next commented out for now during
> > debugging. But, how do I handle if a field is missing when trying to assign
> > the array? In this case would missing mean "NULL"?
>
> No, there aren't NULL values in plain text files processed with
> the FileSystemObject. The error results from trying to access
> elements from arySourceFields which aren't there because Split()
> couldn't create them due to lack of ","s.
>
> > The code is as follows:
> >
> > set objFSO = CreateObject("Scripting.FileSystemObject")
> > set objSourceFile = objFSO.OpenTextFile(strSourceFilename, ForReading)
> > if err <> 0 then ErrHandler
> >
> Const UBOUND_EXPECTED = 4 ' 5 items indexed 0 ... 4
>
> > do until objSourceFile.AtEndOfStream
> > arySourceFields = split(objSourceFile.ReadLine,",",-1,1)
> If UBOUND_EXPECTED <> UBound( arySourceFields ) Then
> skip or raise hell
> Else
> work with all fields
> End If
> > if err <> 0 then ErrHandler
> > wscript.stdout.write arySourceFields(0) '''remove.
> > loop
> > .
> > .
> > .
> >
> >
> > Example of csv file contents:
> > 500,world,1,1,5
> > 501,now,2,6,0
> > .
> > .
> > .
> > 600,,,
> > 601
> >
> > the problem occurs for records like 601 above. notice there are no commas
> > seperating other fields. the csv just has first field and nothing in
> > remaining fields like rest of records. Code works fine for all other
> > instances including 600,,,
> >
> > Assigning arySourceFields(x) to any variable or simply doing the
> > stdout.write above causes runtime of:
> > createfiles.vbs(102,1) Microsoft VBScript runtime error: Subscript out of
> > range: 'wscript.stdout'
> >
> > How do I prevent the error from occuring in my code without the on error
> > resume next? Isn't there a function that would check to see if the subject
> > is valid?
> >
> > Thank you.
>

RdS
Wed Feb 28 14:18:27 CST 2007

sorry...but I see you answered inline. :) That helped a lot.

I cannot statically assigned an upper boundary because some records don't
contain all fields. Would there be another way to do this to handle this
situation?

thanks again.


"ekkehard.horner" wrote:

> RdS wrote:
>
> > hi,
> >
> > when reading through a CSV file and a field is missing I receive a run time
> > error. I have the on error resume next commented out for now during
> > debugging. But, how do I handle if a field is missing when trying to assign
> > the array? In this case would missing mean "NULL"?
>
> No, there aren't NULL values in plain text files processed with
> the FileSystemObject. The error results from trying to access
> elements from arySourceFields which aren't there because Split()
> couldn't create them due to lack of ","s.
>
> > The code is as follows:
> >
> > set objFSO = CreateObject("Scripting.FileSystemObject")
> > set objSourceFile = objFSO.OpenTextFile(strSourceFilename, ForReading)
> > if err <> 0 then ErrHandler
> >
> Const UBOUND_EXPECTED = 4 ' 5 items indexed 0 ... 4
>
> > do until objSourceFile.AtEndOfStream
> > arySourceFields = split(objSourceFile.ReadLine,",",-1,1)
> If UBOUND_EXPECTED <> UBound( arySourceFields ) Then
> skip or raise hell
> Else
> work with all fields
> End If
> > if err <> 0 then ErrHandler
> > wscript.stdout.write arySourceFields(0) '''remove.
> > loop
> > .
> > .
> > .
> >
> >
> > Example of csv file contents:
> > 500,world,1,1,5
> > 501,now,2,6,0
> > .
> > .
> > .
> > 600,,,
> > 601
> >
> > the problem occurs for records like 601 above. notice there are no commas
> > seperating other fields. the csv just has first field and nothing in
> > remaining fields like rest of records. Code works fine for all other
> > instances including 600,,,
> >
> > Assigning arySourceFields(x) to any variable or simply doing the
> > stdout.write above causes runtime of:
> > createfiles.vbs(102,1) Microsoft VBScript runtime error: Subscript out of
> > range: 'wscript.stdout'
> >
> > How do I prevent the error from occuring in my code without the on error
> > resume next? Isn't there a function that would check to see if the subject
> > is valid?
> >
> > Thank you.
>

ekkehard
Wed Feb 28 14:38:13 CST 2007

RdS wrote:

> thanks for your reply.
>
> I understand why it is happening, but I don't know how to handle such an
> issue. Isn't there a function that would allow me to handle error?
[...]

>> If UBOUND_EXPECTED <> UBound( arySourceFields ) Then
>> skip or raise hell
>> Else
>> work with all fields
>> End If

Sorry for being not clear:

(1) There won't be any error, if you test the UBound() after
the split

(2) What do to after reading a line like
601
or
this,should,be,considered,garbage,too,I,think

depends on your specs. Perhaps you can silently ignore
such lines, perhaps you should log the fact, or even
abort the program immediately?

(3) In the Else branch you can access 5 elements with
confidence. Whether they contain valid data has to
be checked
arySourceFields(0) <> ""
IsNumeric( arySourceFields(0) )
dicAllowedWords.Exists( arySourceFields(1) )
.....

ekkehard
Wed Feb 28 14:48:59 CST 2007

RdS wrote:

> sorry...but I see you answered inline. :) That helped a lot.
>
> I cannot statically assigned an upper boundary because some records don't
> contain all fields. Would there be another way to do this to handle this
> situation?

do until objSourceFile.AtEndOfStream
arySourceFields = split(objSourceFile.ReadLine,",",-1,1)
Select Case UBound( arySourceFields )
Case 4 ' ok no need to do more
Case 1 ' just the first number (I hope, you must check!)
arySourceFields = Array( arySourceFields( 0 ), "world", 3, 4, 5 )
Case Else
skip or raise hell
e.g. WScript.Echo "Bad data in Line ..." : Exit Do
End Select
work with all fields
loop

RdS
Wed Feb 28 15:01:05 CST 2007

ok. got it. I changed if to the following and it works all of the time:
if 0 < ubound(arySourceFields) then

thanks again

"ekkehard.horner" wrote:

> RdS wrote:
>
> > hi,
> >
> > when reading through a CSV file and a field is missing I receive a run time
> > error. I have the on error resume next commented out for now during
> > debugging. But, how do I handle if a field is missing when trying to assign
> > the array? In this case would missing mean "NULL"?
>
> No, there aren't NULL values in plain text files processed with
> the FileSystemObject. The error results from trying to access
> elements from arySourceFields which aren't there because Split()
> couldn't create them due to lack of ","s.
>
> > The code is as follows:
> >
> > set objFSO = CreateObject("Scripting.FileSystemObject")
> > set objSourceFile = objFSO.OpenTextFile(strSourceFilename, ForReading)
> > if err <> 0 then ErrHandler
> >
> Const UBOUND_EXPECTED = 4 ' 5 items indexed 0 ... 4
>
> > do until objSourceFile.AtEndOfStream
> > arySourceFields = split(objSourceFile.ReadLine,",",-1,1)
> If UBOUND_EXPECTED <> UBound( arySourceFields ) Then
> skip or raise hell
> Else
> work with all fields
> End If
> > if err <> 0 then ErrHandler
> > wscript.stdout.write arySourceFields(0) '''remove.
> > loop
> > .
> > .
> > .
> >
> >
> > Example of csv file contents:
> > 500,world,1,1,5
> > 501,now,2,6,0
> > .
> > .
> > .
> > 600,,,
> > 601
> >
> > the problem occurs for records like 601 above. notice there are no commas
> > seperating other fields. the csv just has first field and nothing in
> > remaining fields like rest of records. Code works fine for all other
> > instances including 600,,,
> >
> > Assigning arySourceFields(x) to any variable or simply doing the
> > stdout.write above causes runtime of:
> > createfiles.vbs(102,1) Microsoft VBScript runtime error: Subscript out of
> > range: 'wscript.stdout'
> >
> > How do I prevent the error from occuring in my code without the on error
> > resume next? Isn't there a function that would check to see if the subject
> > is valid?
> >
> > Thank you.
>