need some advice please - array? or?

[Tom]

I have many data files containing data from our old system. I have 12 filenames
to deal with (1 file per table). I have 5 cycles (we sell utilities) each
containing multiple [meter] books, except for the last (5), which has only 1
book. Cycle 1 has 28 books, and the other 3 have varying numbers of books.

For the most part, the book numbers increment by 1 from the first book, o.e.,
101, 102, 103, etc. But there are a couple of exceptions. There are several
books which don't exist, so the numbers are skipped.

I've been working on a much smaller app from our old system to work out the
'kinks' in cycling thru multiple years and tables. I figure it'll be an easy
conversion to cycle thru my books and tables. (No separate years to deal with.)
But in the code I'm working with now, I only have 5 years to deal with, so I've
used 5 if statements.

So here's the thing. Faced with up to say, 30 books or so per cycle, and 5
cycles, I don't think that using if statements would be the way to go. I'm
thinking of using an array. But as of yet, I have not played with arrays in VB.
(In a previous life, as a COBOL programmer, I used to play with them. But that
was at least 8 years ago...). So I was hoping that perhaps someone might be
able to provide some guidance here. And if an array *is* the way to go, perhaps
a little help in setting my array up. An array for each cycle perhaps?

In COBOL I could set something up like:

"101201301401501"
"102202302402 "
"103203303403 "
"105204306404 "
"106205307405 "

where the 1st column of 3 would be cycle 1, the 2nd column of 3 would be cycle
2, and so on. I don't even know if I can do this in VB, much less how. My help
file doesn't seem to be much help in this regard.

Hmmm... While I've been reading thru the array related posts in this ng (back
to mid 2003), I got to thinking. Perhaps just a table of the meter book numbers
that exist might be the way to go. Open the table, read the book number, and do
my import. Hmmm...

Oh yeah, I almost forgot. I'm using Access 2000.

Thanks in advance, I look forward to your response.

Tom

 

[Guest]

I have many data files containing data from our old system. I have 12 filenames
to deal with (1 file per table). I have 5 cycles (we sell utilities) each
containing multiple [meter] books, except for the last (5), which has only 1
book. Cycle 1 has 28 books, and the other 3 have varying numbers of books.

For the most part, the book numbers increment by 1 from the first book, o.e.,
101, 102, 103, etc. But there are a couple of exceptions. There are several
books which don't exist, so the numbers are skipped.

I've been working on a much smaller app from our old system to work out the
'kinks' in cycling thru multiple years and tables. I figure it'll be an easy
conversion to cycle thru my books and tables. (No separate years to deal with.)
But in the code I'm working with now, I only have 5 years to deal with, so I've
used 5 if statements.

So here's the thing. Faced with up to say, 30 books or so per cycle, and 5
cycles, I don't think that using if statements would be the way to go. I'm
thinking of using an array. But as of yet, I have not played with arrays in VB.
(In a previous life, as a COBOL programmer, I used to play with them. But that
was at least 8 years ago...). So I was hoping that perhaps someone might be
able to provide some guidance here. And if an array *is* the way to go, perhaps
a little help in setting my array up. An array for each cycle perhaps?

In COBOL I could set something up like:

"101201301401501"
"102202302402 "
"103203303403 "
"105204306404 "
"106205307405 "

where the 1st column of 3 would be cycle 1, the 2nd column of 3 would be cycle
2, and so on. I don't even know if I can do this in VB, much less how. My help
file doesn't seem to be much help in this regard.

Hmmm... While I've been reading thru the array related posts in this ng (back
to mid 2003), I got to thinking. Perhaps just a table of the meter book numbers
that exist might be the way to go. Open the table, read the book number, and do
my import. Hmmm...

Oh yeah, I almost forgot. I'm using Access 2000.

Thanks in advance, I look forward to your response.

Tom

Tom,

What is the structure of the "old" data files (tables)? And what format
(text files, dbf,...).

What is the structure of the new Access table?

Have you thought of using an Append Query? Maybe to a temp table that you
could do some massaging of data, then appending to the main table? Or even
directly to the main table?

You could import the data to a second mdb, do the massaging, then append to
the "real" mdb.

Much less involved that using arrays.... there are wizards to help import
the "old" data tables.

HTH

 

[Tom]

I have many data files containing data from our old system. I have 12 filenames
to deal with (1 file per table). I have 5 cycles (we sell utilities) each
containing multiple [meter] books, except for the last (5), which has only 1
book. Cycle 1 has 28 books, and the other 3 have varying numbers of books.

For the most part, the book numbers increment by 1 from the first book, o.e.,
101, 102, 103, etc. But there are a couple of exceptions. There are several
books which don't exist, so the numbers are skipped.

I've been working on a much smaller app from our old system to work out the
'kinks' in cycling thru multiple years and tables. I figure it'll be an easy
conversion to cycle thru my books and tables. (No separate years to deal with.)
But in the code I'm working with now, I only have 5 years to deal with, so I've
used 5 if statements.

So here's the thing. Faced with up to say, 30 books or so per cycle, and 5
cycles, I don't think that using if statements would be the way to go. I'm
thinking of using an array. But as of yet, I have not played with arrays in VB.
(In a previous life, as a COBOL programmer, I used to play with them. But that
was at least 8 years ago...). So I was hoping that perhaps someone might be
able to provide some guidance here. And if an array *is* the way to go, perhaps
a little help in setting my array up. An array for each cycle perhaps?

In COBOL I could set something up like:

"101201301401501"
"102202302402 "
"103203303403 "
"105204306404 "
"106205307405 "

where the 1st column of 3 would be cycle 1, the 2nd column of 3 would be cycle
2, and so on. I don't even know if I can do this in VB, much less how. My help
file doesn't seem to be much help in this regard.

Hmmm... While I've been reading thru the array related posts in this ng (back
to mid 2003), I got to thinking. Perhaps just a table of the meter book numbers
that exist might be the way to go. Open the table, read the book number, and do
my import. Hmmm...

Oh yeah, I almost forgot. I'm using Access 2000.

Thanks in advance, I look forward to your response.

Tom

Tom,

What is the structure of the "old" data files (tables)?

The data is from a Data General DBAM file structure, maintained by a
process called INFOS. (Didn't help you any, did it?)

I've already extracted all my data, into one text file for each table
I want to create. It's comma delimited, with *everything* enclosed in
quotes. I have one set of files for each book. (See last answer.)
Plus, I actually have two identical databases. One was "live" and the
other "history". So I *actually* have to cycle thru 100+
books...TWICE.

And what format (text files, dbf,...)?

Already answered.

What is the structure of the new Access table?
Why?


Have you thought of using an Append Query? Maybe to a temp table that you
could do some massaging of data, then appending to the main table? Or even
directly to the main table?

You could import the data to a second mdb, do the massaging, then append to
the "real" mdb.

Much less involved that using arrays.... there are wizards to help import
the "old" data tables.

I didn't mention arrays to massage the old data. I do *NOT* need to
play with my data. I've already taken care of that. I have all my
import spec files created. I was [merely] looking for an easy way of
cycling thru all the meter books, which is my lowest breakdown
(grouping) of my data.

4 cycles * 25+ meter books per cycle = 100+ meter books to cycle thru.

 

[Guest]

I have many data files containing data from our old system. I have 12 filenames
to deal with (1 file per table). I have 5 cycles (we sell utilities) each
containing multiple [meter] books, except for the last (5), which has only 1
book. Cycle 1 has 28 books, and the other 3 have varying numbers of books.

For the most part, the book numbers increment by 1 from the first book, o.e.,
101, 102, 103, etc. But there are a couple of exceptions. There are several
books which don't exist, so the numbers are skipped.

I've been working on a much smaller app from our old system to work out the
'kinks' in cycling thru multiple years and tables. I figure it'll be an easy
conversion to cycle thru my books and tables. (No separate years to deal with.)
But in the code I'm working with now, I only have 5 years to deal with, so I've
used 5 if statements.

So here's the thing. Faced with up to say, 30 books or so per cycle, and 5
cycles, I don't think that using if statements would be the way to go. I'm
thinking of using an array. But as of yet, I have not played with arrays in VB.
(In a previous life, as a COBOL programmer, I used to play with them. But that
was at least 8 years ago...). So I was hoping that perhaps someone might be
able to provide some guidance here. And if an array *is* the way to go, perhaps
a little help in setting my array up. An array for each cycle perhaps?

In COBOL I could set something up like:

"101201301401501"
"102202302402 "
"103203303403 "
"105204306404 "
"106205307405 "

where the 1st column of 3 would be cycle 1, the 2nd column of 3 would be cycle
2, and so on. I don't even know if I can do this in VB, much less how. My help
file doesn't seem to be much help in this regard.

Hmmm... While I've been reading thru the array related posts in this ng (back
to mid 2003), I got to thinking. Perhaps just a table of the meter book numbers
that exist might be the way to go. Open the table, read the book number, and do
my import. Hmmm...

Oh yeah, I almost forgot. I'm using Access 2000.

Thanks in advance, I look forward to your response.

Tom

Tom,

What is the structure of the "old" data files (tables)?

The data is from a Data General DBAM file structure, maintained by a
process called INFOS. (Didn't help you any, did it?)

I've already extracted all my data, into one text file for each table
I want to create. It's comma delimited, with *everything* enclosed in
quotes. I have one set of files for each book. (See last answer.)
Plus, I actually have two identical databases. One was "live" and the
other "history". So I *actually* have to cycle thru 100+
books...TWICE.

And what format (text files, dbf,...)?

Already answered.

What is the structure of the new Access table?
Why?


Have you thought of using an Append Query? Maybe to a temp table that you
could do some massaging of data, then appending to the main table? Or even
directly to the main table?

You could import the data to a second mdb, do the massaging, then append to
the "real" mdb.

Much less involved that using arrays.... there are wizards to help import
the "old" data tables.

I didn't mention arrays to massage the old data. I do *NOT* need to
play with my data. I've already taken care of that. I have all my
import spec files created. I was [merely] looking for an easy way of
cycling thru all the meter books, which is my lowest breakdown
(grouping) of my data.

4 cycles * 25+ meter books per cycle = 100+ meter books to cycle thru.

HTH

Tom,

I asked about the Access table structure because I was wondering if the data
was in a "flat file" format and if you were going to convert it to a
relational model. When I used "massaging", I meant that if you had a seperate
file for each year, maybe you were going to add a new field for Book/Cycle
year; once a (text) file was in an Access table, you could run an update
query to add the year to the records.

When importing a text file, you have the option to specify what data type
the the import field is. I think using the Import command is easier than
writing a sub to do the importing/ data type conversion, although I've done
both.

Anyway, it was the way I handled it when I to convert about 50 DBF files
where the data had been exported as text.

Good luck...