Null values in variant array

[Paul M. Cook]

Let's say you have a CSV file and you load it into a variant array using the
split function on VBCrLF. Then you load a variable with the line count and
loop through the array for 0 to line count. This works well unless you have
blank lines at the end of the CSV file. Now if you process the loop, you'll
get an out of bounds subscript at the end of the loop because you are
referencing null values at the end of the variant array.

How would you go about stripping the extraneous CRLFs from the end of the
file?

Paul

[Steve Gerrard]

Re: Null values in variant array


"Paul M. Cook" <pmBERMUDA_SHOR TScook@gte.net> wrote in message
news:w1NLf.92$p E4.64@trnddc04. ..[color=blue]
> Let's say you have a CSV file and you load it into a variant array using the
> split function on VBCrLF. Then you load a variable with the line count and
> loop through the array for 0 to line count. This works well unless you have
> blank lines at the end of the CSV file. Now if you process the loop, you'll
> get an out of bounds subscript at the end of the loop because you are
> referencing null values at the end of the variant array.
>
> How would you go about stripping the extraneous CRLFs from the end of the
> file?
>[/color]

If it is just an out of bounds subscript, then use UBound(MyArray) instead of
the line count. That will tell you how many actual array entries you have.

[Paul M. Cook]

Re: Null values in variant array


"Steve Gerrard" <mynamehere@com cast.net> wrote in message
news:UJWdnVVt0d MNVWLeRVn-qQ@comcast.com. ..[color=blue]
>
> "Paul M. Cook" <pmBERMUDA_SHOR TScook@gte.net> wrote in message
> news:w1NLf.92$p E4.64@trnddc04. ..[color=green]
> > Let's say you have a CSV file and you load it into a variant array using[/color][/color]
the[color=blue][color=green]
> > split function on VBCrLF. Then you load a variable with the line count[/color][/color]
and[color=blue][color=green]
> > loop through the array for 0 to line count. This works well unless you[/color][/color]
have[color=blue][color=green]
> > blank lines at the end of the CSV file. Now if you process the loop,[/color][/color]
you'll[color=blue][color=green]
> > get an out of bounds subscript at the end of the loop because you are
> > referencing null values at the end of the variant array.
> >
> > How would you go about stripping the extraneous CRLFs from the end of[/color][/color]
the[color=blue][color=green]
> > file?
> >[/color]
>
> If it is just an out of bounds subscript, then use UBound(MyArray) instead[/color]
of[color=blue]
> the line count. That will tell you how many actual array entries you have.
>
>[/color]

Here is some of the code:


Dim whole_file As String
Dim lines As Variant
Dim the_array() As Variant


Open InDirPath For Input As #1

' Load the file into a memory string

whole_file = Input$(LOF(1), 1)

Close #1

' Break the file into lines.
lines = Split(whole_fil e, vbCrLf)

' Dimension the array.
num_rows = UBound(lines)
ReDim the_array(num_r ows)

' Copy the data into the array.
For R = 0 To num_rows
the_array(R) = Split(lines(R), ",")
Next R


In my case, num_rows will include a count representing the extra CRs at the
end of the file. So if the file looks like this:

1234,abcd,9876< crlf>
2334,kjkjk,0008 <crlf>
<crlf>
<crlf>

Then num_rows will be 4.

[argusy]

Re: Null values in variant array

Paul,
if you can look at the csv file, (wordpad, whatever that works), see how many
carriage return/line feeds are at the end of the file.
If your csv data generator consistently returns the same number of CrLfs, then
you can set up your loop count subtracting the extraneous number of crLfs
generated.
If it varies, then you may have to do a reverse string search, counting CrLfs
until you get valid data, and either modify your loop count depending on how
many show up, or redim your upper boundary on your array to fix it.

see if just looping from 0 to line count-1 fixes the problem.
(and you get all your data)

look up help on "option base"

Argusy

Paul M. Cook wrote:[color=blue]
> Let's say you have a CSV file and you load it into a variant array using the
> split function on VBCrLF. Then you load a variable with the line count and
> loop through the array for 0 to line count. This works well unless you have
> blank lines at the end of the CSV file. Now if you process the loop, you'll
> get an out of bounds subscript at the end of the loop because you are
> referencing null values at the end of the variant array.
>
> How would you go about stripping the extraneous CRLFs from the end of the
> file?
>
> Paul
>
>[/color]

[Steve Gerrard]

Re: Null values in variant array


"Paul M. Cook" <pmBERMUDA_SHOR TScook@gte.net> wrote in message
news:ITPLf.713$ FE2.377@trnddc0 1...[color=blue]
>
> "Steve Gerrard" <mynamehere@com cast.net> wrote in message
> news:UJWdnVVt0d MNVWLeRVn-qQ@comcast.com. ..[color=green]
>>
>> "Paul M. Cook" <pmBERMUDA_SHOR TScook@gte.net> wrote in message
>> news:w1NLf.92$p E4.64@trnddc04. ..[/color]
>
> Here is some of the code:
>
>[/color]

Try this variation, checking the length of each lines(R), and only adding to
the_array if there is something there. Then it redims the_array to the final
size.

Private Sub Command1_Click( )
Dim whole_file As String
Dim lines As Variant
Dim the_array() As Variant
Dim num_rows As Long
Dim R As Long
Dim N As Long

whole_file = "1234,abcd,9876 " & vbCrLf _
& "2334,kjkjk,000 8" & vbCrLf _
& vbCrLf & vbCrLf

' Break the file into lines.
lines = Split(whole_fil e, vbCrLf)

' Dimension the array.
num_rows = UBound(lines)
ReDim the_array(num_r ows)

' Copy the data into the array.
N = 0
For R = 0 To num_rows
If Len(lines(R)) > 0 Then
the_array(N) = Split(lines(R), ",")
N = N + 1
End If
Next R

num_rows = N - 1
ReDim Preserve the_array(num_r ows)

For R = 0 To num_rows
For N = 0 To UBound(the_arra y(R))
Debug.Print the_array(R)(N) ,
Next N
Debug.Print
Next R

End Sub

[argusy]

Re: Null values in variant array

Now I've seen your example, I was right in thinking you were getting a couple or
three CrLfs at the end of your file.

could try
dim falsecount as long


' Copy the data into the array.
For R = 0 To num_rows
' air code!! I think it would count the crlf as two
if len(lines(R)) < 3 then ' could be zero
the_array(R) = Split(lines(R), ",")
falsecount = falsecount + 1
endif
Next R
'air code again!! I think this is how to do it
redim preserve array(num-rows - falsecount)


Argusy


Paul M. Cook wrote:[color=blue]
> "Steve Gerrard" <mynamehere@com cast.net> wrote in message
> news:UJWdnVVt0d MNVWLeRVn-qQ@comcast.com. ..
>[color=green]
>>"Paul M. Cook" <pmBERMUDA_SHOR TScook@gte.net> wrote in message
>>news:w1NLf.92 $pE4.64@trnddc0 4...
>>[color=darkred]
>>>Let's say you have a CSV file and you load it into a variant array using[/color]
>>[/color]
> the
>[color=green][color=darkred]
>>>split function on VBCrLF. Then you load a variable with the line count[/color]
>>[/color]
> and
>[color=green][color=darkred]
>>>loop through the array for 0 to line count. This works well unless you[/color]
>>[/color]
> have
>[color=green][color=darkred]
>>>blank lines at the end of the CSV file. Now if you process the loop,[/color]
>>[/color]
> you'll
>[color=green][color=darkred]
>>>get an out of bounds subscript at the end of the loop because you are
>>>referencin g null values at the end of the variant array.
>>>
>>>How would you go about stripping the extraneous CRLFs from the end of[/color]
>>[/color]
> the
>[color=green][color=darkred]
>>>file?
>>>[/color]
>>
>>If it is just an out of bounds subscript, then use UBound(MyArray) instead[/color]
>
> of
>[color=green]
>>the line count. That will tell you how many actual array entries you have.
>>
>>[/color]
>
>
> Here is some of the code:
>
>
> Dim whole_file As String
> Dim lines As Variant
> Dim the_array() As Variant
>
>
> Open InDirPath For Input As #1
>
> ' Load the file into a memory string
>
> whole_file = Input$(LOF(1), 1)
>
> Close #1
>
> ' Break the file into lines.
> lines = Split(whole_fil e, vbCrLf)
>
> ' Dimension the array.
> num_rows = UBound(lines)
> ReDim the_array(num_r ows)
>
> ' Copy the data into the array.
> For R = 0 To num_rows
> the_array(R) = Split(lines(R), ",")
> Next R
>
>
> In my case, num_rows will include a count representing the extra CRs at the
> end of the file. So if the file looks like this:
>
> 1234,abcd,9876< crlf>
> 2334,kjkjk,0008 <crlf>
> <crlf>
> <crlf>
>
> Then num_rows will be 4.
>
>
>[/color]

[argusy]

Re: Null values in variant array

bugger!!

change added line to
If len(lines(R) > 0

Argusy



argusy wrote:[color=blue]
> Now I've seen your example, I was right in thinking you were getting a
> couple or three CrLfs at the end of your file.
>
> could try
> dim falsecount as long
>
>
> ' Copy the data into the array.
> For R = 0 To num_rows
> ' air code!! I think it would count the crlf as two
> if len(lines(R)) < 3 then ' could be zero
> the_array(R) = Split(lines(R), ",")
> falsecount = falsecount + 1
> endif
> Next R
> 'air code again!! I think this is how to do it
> redim preserve array(num-rows - falsecount)
>
>
> Argusy
>
>
> Paul M. Cook wrote:
>[color=green]
>> "Steve Gerrard" <mynamehere@com cast.net> wrote in message
>> news:UJWdnVVt0d MNVWLeRVn-qQ@comcast.com. ..
>>[color=darkred]
>>> "Paul M. Cook" <pmBERMUDA_SHOR TScook@gte.net> wrote in message
>>> news:w1NLf.92$p E4.64@trnddc04. ..
>>>
>>>> Let's say you have a CSV file and you load it into a variant array
>>>> using
>>>
>>>[/color]
>> the
>>[color=darkred]
>>>> split function on VBCrLF. Then you load a variable with the line count
>>>
>>>[/color]
>> and
>>[color=darkred]
>>>> loop through the array for 0 to line count. This works well unless you
>>>
>>>[/color]
>> have
>>[color=darkred]
>>>> blank lines at the end of the CSV file. Now if you process the loop,
>>>
>>>[/color]
>> you'll
>>[color=darkred]
>>>> get an out of bounds subscript at the end of the loop because you are
>>>> referencing null values at the end of the variant array.
>>>>
>>>> How would you go about stripping the extraneous CRLFs from the end of
>>>
>>>[/color]
>> the
>>[color=darkred]
>>>> file?
>>>>
>>>
>>> If it is just an out of bounds subscript, then use UBound(MyArray)
>>> instead[/color]
>>
>>
>> of
>>[color=darkred]
>>> the line count. That will tell you how many actual array entries you
>>> have.
>>>
>>>[/color]
>>
>>
>> Here is some of the code:
>>
>>
>> Dim whole_file As String
>> Dim lines As Variant
>> Dim the_array() As Variant
>>
>>
>> Open InDirPath For Input As #1
>>
>> ' Load the file into a memory string
>>
>> whole_file = Input$(LOF(1), 1)
>>
>> Close #1
>>
>> ' Break the file into lines.
>> lines = Split(whole_fil e, vbCrLf)
>>
>> ' Dimension the array.
>> num_rows = UBound(lines)
>> ReDim the_array(num_r ows)
>>
>> ' Copy the data into the array.
>> For R = 0 To num_rows
>> the_array(R) = Split(lines(R), ",")
>> Next R
>>
>>
>> In my case, num_rows will include a count representing the extra CRs
>> at the
>> end of the file. So if the file looks like this:
>>
>> 1234,abcd,9876< crlf>
>> 2334,kjkjk,0008 <crlf>
>> <crlf>
>> <crlf>
>>
>> Then num_rows will be 4.
>>
>>
>>[/color]
>[/color]

[Paul M. Cook]

Re: Null values in variant array

Thanks, guys.

I was unaware of the ability to redim and preserve an array and wanted a
clean way of fixing this without building a whole new array or putting in a
lot of exception logic.

It works great now with the ideas you gave.

Paul

"argusy" <argusy@slmembe r.on.net> wrote in message
news:43FFCE90.2 050402@slmember .on.net...[color=blue]
> Paul,
> if you can look at the csv file, (wordpad, whatever that works), see how[/color]
many[color=blue]
> carriage return/line feeds are at the end of the file.
> If your csv data generator consistently returns the same number of CrLfs,[/color]
then[color=blue]
> you can set up your loop count subtracting the extraneous number of crLfs
> generated.
> If it varies, then you may have to do a reverse string search, counting[/color]
CrLfs[color=blue]
> until you get valid data, and either modify your loop count depending on[/color]
how[color=blue]
> many show up, or redim your upper boundary on your array to fix it.
>
> see if just looping from 0 to line count-1 fixes the problem.
> (and you get all your data)
>
> look up help on "option base"
>
> Argusy
>
> Paul M. Cook wrote:[color=green]
> > Let's say you have a CSV file and you load it into a variant array using[/color][/color]
the[color=blue][color=green]
> > split function on VBCrLF. Then you load a variable with the line count[/color][/color]
and[color=blue][color=green]
> > loop through the array for 0 to line count. This works well unless you[/color][/color]
have[color=blue][color=green]
> > blank lines at the end of the CSV file. Now if you process the loop,[/color][/color]
you'll[color=blue][color=green]
> > get an out of bounds subscript at the end of the loop because you are
> > referencing null values at the end of the variant array.
> >
> > How would you go about stripping the extraneous CRLFs from the end of[/color][/color]
the[color=blue][color=green]
> > file?
> >
> > Paul
> >
> >[/color]
>[/color]