Moving data from dataset to Array

[IneedaSmoke]

Hey all, trying to figure out how to move some data in a dataset to an array. I found this online:

' pseudo(code)

' For x As Integer = 0 To ds.Tables(0).Rows.Count - 1

'MyArray(x, 0) = ds.Tables(0).Rows(x)(0)

' MyArray(x, 1) = ds.Tables(0).Rows(x)(1)

' MyArray(x, 2) = ds.Tables(0).Rows(x)(2)

' Next


But haven't been able to get it to work. I get a "Too many arguments to 'Public override Default Property Item(index As Integer) As Object"

Is there something wrong with the syntax? I'm new to this stuff and I haven't really found a great resource for figuring this out yet. Any information would be helpful TIA

 

[IneedaSmoke]

i'm dumb. Noticed that i declared it as an arraylist. I'm actually gonna try to mess around with object array as my table has mixed types.

 

[psmocko]

Instead of walking through the columns in the loop, take a look at the table row itemarray property.....you could actually just copy this to an object array if needed.

 

[cjard]

Hey all, trying to figure out how to move some data in a dataset to an array.

Now why would you want to do a (silly) thing like that?

 

[IneedaSmoke]

Ha, i'm probably taking the long route ...I'm just not very familiar with all of this.

This is what I want to do:

1. I get 2 data files, one in excel and one in CSV format
2. I pull data into SQLserver Express
3. Pull data into vb
4. Compare the data from the 2 files looking to match up the values in both with each other. Then create 2 tables where one has all the matched up values and the other has all of the non matched items.

Note that when matching there are many (most) matched items are identicle. When I tried different joins in SQL, I couldn't figure out how to handle the excess Cartesian product results, so I thought I could run a few for loops and break up the data that way. I can control the data a lot better I think.

Where i'm at now:

I have most of this in place right now. I just need to get some logic corrected and it will work.

I'm new to this and I'm not even in the place where I know what the right questions are. It wouldn't surprise me if there was some way to do this in ado or SQL. XML only means xtra large, medium and large to me at this point

If you have any suggestions I could google up, i'de really appreciate it. In the short time ive been programming (weeks LOL), i've learned a lot already and am looking for more ideas.

 

[cjard]

Then create 2 tables where one has all the matched up values and the other has all of the non matched items

And what is the final output form?

How do you compare a CSV and an Excel file? What are the criteria?

 

[psmocko]

I would need more information on the data you are trying to compare. eg: One key field? combinations of field values? Yes you could probably get it done via SQL if you have the data in SQL.

If you really want to write it in a program, you could cut out a step by just reading the values from the excel and CSV files into tables within your program via the excel object interfaces provided by microsoft.

 

[cjard]

I'd use the Jet driver for both, load one into a dictionary, iterate the other file testing if it's in the dictionary.. if it is, remove it from the dict and write it to the "in both" list, if not write to the "a not B list" and at the end, the dictionary contaisn the "b not a " list

take about.. um.. an hour to write

 

[IneedaSmoke]

Good stuff everyone, thanks for your posts. I'll look up the Jet driver. One of the benefits of not knowing anything is that you learn everything lol.

The output will eventually be something I put in an e-mail for someone else. So I guess it could be in excel or text or any file they can open with programs integrated in the OS or MSOffice.

There are basically 3 fields to compare; one string and 2 numbers. I am using SQL because I was working with SQL initially and didn't think to pull the data in directly to VB. This is partly laziness on my part and partly the result of familiarity as I had done this before in my previous program. Though in hindsight I should learn how to do that for both of these programs as the other program uses excel and text files.

I have some familiarity with excel and so I've been trying to leverage some of that knowledge while I learn more VB and SQL. I can see where I want to migrate a lot of stuff to purely VB but i'm working at a snails pace now as it is. I'll revisit these other programs as I learn key skills and migrate functionality directly into the program. I know the stuff i've done so far is pretty much garbage but it's 'good enough for jazz' so to speak.

cjard, I basically used your logic in this program. As I mentioned, I was having some trouble with the logic. It appears though that the logic was fine but one of my data sources was using a rounding function that was causing problems. I included math.round(my array(integerCounter1,field) to clean that data. I dealing with financial products so there shouldn't be any values past 2 decimal spots. In one of the data sources, I was receiving odd numbers such as 7 zeros then a number to the right of the decimal. Rounding cleared this up and most of the logic I had in place worked.

Specifically I used 2 arrays, in two for loops, twice. I compared array a with b and vice versa with results sent to end result arrays. Basically what SQL does but with the suggestion you gave in sending the output to new arrays (a not b and b not a).

I consolidated those two new arrays into a third one and outputed it to SQL. I can copy that to an e-mail.

Everyones posts were very helpful!

My next project will include ASP so i'll be putting some questions there I predict

After that I have a project that I have to integrate excel in my program so after that one, I'll revisit these first 2 programs and make the changes psmocko has suggested.

My boss has been great about all of this and each program has taught me key skills. The first introduced me to ADO and how to work with VB (i've never programmed VB before), this one taught me arrays, next will teach me some ASP and the one after Excel integration.

Thanks again!

 

[cjard]

The output will eventually be something I put in an e-mail for someone else. So I guess it could be in excel or text or any file they can open with programs integrated in the OS or MSOffice.

I wrote a library that can turn a datatable into an excel file, if you want it.. say..

There are basically 3 fields to compare; one string and 2 numbers.

Those would be the dictionary key then

Specifically I used 2 arrays, in two for loops, twice.

Noooo....

Read up on hash tables.. If you have 1000 entries in one array and 1000 entries in another, you need to do 2 million comparisons to get the union and the minus

Uisng hash tabvles it will take 2000 operations rather than 2 million

Basically what SQL does but with the suggestion you gave in sending the output to new arrays (a not b and b not a).

I just thought of an efficient way of reusing the data containers.. you can use more arrays, but really, ditch arrays.. They are too inflexible for modern use, and i use collections about 10 times more often.

Note that using arrays is not what SQL does because it's too inefficient.. Databases have to be quick, and and op like this would be hash join rather then nested loops

My next project will include ASP so i'll be putting some questions there I predict

Not here specifically.. THis is VB area of the forums, there is a dedicated ASP area

The first introduced me to ADO and how to work with VB (i've never programmed VB before), this one taught me arrays, next will teach me some ASP and the one after Excel integration.

Thanks again!

ADO and VB are different top ADO.NET and VB.NET.. be careful how you use the terms
Now that you learned arrays, dump them.. read up on collections, Dictionary(Of ...,...), List(Of ...). Everything has its place, but typically in a environment where collections have to grow dynamically, array is not so useful