473,320 Members | 1,865 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Null values in variant array

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
Feb 25 '06 #1
7 9517

"Paul M. Cook" <pm******************@gte.net> wrote in message
news:w1NLf.92$pE4.64@trnddc04...
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?


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.
Feb 25 '06 #2

"Steve Gerrard" <my********@comcast.net> wrote in message
news:UJ********************@comcast.com...

"Paul M. Cook" <pm******************@gte.net> wrote in message
news:w1NLf.92$pE4.64@trnddc04...
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?

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.


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_file, vbCrLf)

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

' 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.

Feb 25 '06 #3
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:
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


Feb 25 '06 #4

"Paul M. Cook" <pm******************@gte.net> wrote in message
news:ITPLf.713$FE2.377@trnddc01...

"Steve Gerrard" <my********@comcast.net> wrote in message
news:UJ********************@comcast.com...

"Paul M. Cook" <pm******************@gte.net> wrote in message
news:w1NLf.92$pE4.64@trnddc04...


Here is some of the code:


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,0008" & vbCrLf _
& vbCrLf & vbCrLf

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

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

' 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_rows)

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

End Sub
Feb 25 '06 #5
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:
"Steve Gerrard" <my********@comcast.net> wrote in message
news:UJ********************@comcast.com...
"Paul M. Cook" <pm******************@gte.net> wrote in message
news:w1NLf.92$pE4.64@trnddc04...
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?


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.

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_file, vbCrLf)

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

' 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.


Feb 25 '06 #6
bugger!!

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

Argusy

argusy wrote:
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:
"Steve Gerrard" <my********@comcast.net> wrote in message
news:UJ********************@comcast.com...
"Paul M. Cook" <pm******************@gte.net> wrote in message
news:w1NLf.92$pE4.64@trnddc04...

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?
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.

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_file, vbCrLf)

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

' 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.


Feb 25 '06 #7
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" <ar****@slmember.on.net> wrote in message
news:43**************@slmember.on.net...
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:
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

Feb 27 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Kris M | last post by:
How do i handle a null value for a date variable type. I am retrieving date data from an access database and storing the records in an array for processing. The array field has a date type and the...
2
by: Raoul | last post by:
I wrote a COM server object in C++ a few months ago. I can use it from Visual Basic, Visual C++, S-Plus and a number of other scripting environments. What I can't do is use it with my FAVOURITE...
7
by: Uwe Range | last post by:
Hi to all, just ran into a problem: I wrote a function which works with several values collected on a form. I thought it would be no problem, if the fields were empty, because I made the...
7
by: Douglas Buchanan | last post by:
I cannot access certain column values of a list box using code. I have a list box 'lstPrv' populated by the query below. SELECT tblPrv.fkPrvID, lkpCat.CatNm, lkpSrv.SrvNm, lkpCat.pkCatID,...
8
by: Lyn | last post by:
I am trying to get my head around the concept of default, special or empty values that appear in Access VBA, depending on data type. The Access Help is not much (help), and the manual that I have...
2
by: Jim McGivney | last post by:
In asp 2.0 I am trying to insert a row using a detailsview control connected to an accessDataSource. I get the error message below. I am having trouble identifing which data field is causing the...
0
by: MikalE | last post by:
I’m using a third-party ActicveX component that has the following description for one of its events: They mean by this that LocationType is an array of VARIANTS (containing enumeration) When...
3
ADezii
by: ADezii | last post by:
Null as it relates to database development is one of life's little mysteries and a topic of total confusion for novices who venture out into the database world. A Null Value is not zero (0), a zero...
10
by: Toby Gallier | last post by:
Hello! I have a form that is calculating averages as follows: " =(NZ()+Nz()+Nz())/3 " However I need to now adjust for null values , so for example if value2 is null I would then need to...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.