Move Data from table to Array

[Peter]

Is there a fast way to move data from DataTable into double array, or do I have to spin through every record and column?

I have five tables and I need to merge these tables column wise, each table will have to same amount of records but might have different amount of columns.

So I need the first record form table1 and table2 and table3 and table4 and table5 to be in record 1 in my merged array or table.
Second record form table1 and table2 and table3 and table4 and table5 to be in record 2 in my merged array or table.
....
....
....

the final array or table might have 2000 rows and 7000 columns.

Thanks
Peter

[Bruce Wood]

> So I need the first record form table1 and table2 and table3 and table4 and table5 to be in record 1 in my merged array or table.

Second record form table1 and table2 and table3 and table4 and table5 to be in record 2 in my merged array or table.
...

Where is your data coming from?

Can't you just specify this on the SELECT clause when you fetch the
data, e.g.:

SELECT * FROM TABLE1, TABLE2, TABLE3, TABLE4, TABLE5
WHERE TABLE1.KEY = TABLE2.KEY AND TABLE2.KEY = TABLE3.KEY AND ...

or something like that?

If not, I would propose that the time it takes you to populate your
DataTable / Array in memory will probably pale in comparison to the
time it takes to get the stuff out of the database in the first place.
The only way to know for sure is to write the simple-minded algorithm
(loops within loops) and then run your program through a profiler to
see where it's spending its time.

[Kevin Yu [MSFT]]

Hi Peter,

Like Bruce said, it's better to do the merge when you select from the data
source. If you really need to move them to an array, you have to go through
each row in the DataTable. The DataRow has an ItemArray property which
returns an array of object that contains the values for each column.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

[Peter]

"Kevin Yu [MSFT]" <v-****@online.mic rosoft.com> wrote in message
news:EL******** ******@TK2MSFTN GXA02.phx.gbl.. .

Hi Peter,

Like Bruce said, it's better to do the merge when you select from the data
source. If you really need to move them to an array, you have to go
through
each row in the DataTable. The DataRow has an ItemArray property which
returns an array of object that contains the values for each column.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Data is coming from a Excel spreadsheet
each worksheet within the spreadsheet can have variable number of columns
and the column names can be anything, so basically I have to merge each
spreadsheet to the main spreadsheet, each Row1 from each worksheet will be
in the Row1 of the main worksheet. But I can not use Excel because I might
end up with up to 7000 columns.

Somehow I have to get the all of the worksheets data moved into an array
very fast.

[Bruce Wood]

> Data is coming from a Excel spreadsheet

each worksheet within the spreadsheet can have variable number of columns
and the column names can be anything, so basically I have to merge each
spreadsheet to the main spreadsheet, each Row1 from each worksheet will be
in the Row1 of the main worksheet. But I can not use Excel because I might
end up with up to 7000 columns.

Somehow I have to get the all of the worksheets data moved into an array
very fast.

Well, everything is relative. In your case, I suspect that getting the
data out of the existing spreadsheets and writing it to wherever it has
to go will be (relatively) much slower than your merge operation.

The only way to know for sure is to take a shot at writing the merge
and, as I said, run your program under a profiler to see where it
spends most of its time. If the merge turns out to be a bottleneck,
then tune it, but I suspect that it won't be.

[Peter]

"Bruce Wood" <br*******@cana da.com> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.com.. .

Data is coming from a Excel spreadsheet
each worksheet within the spreadsheet can have variable number of columns
and the column names can be anything, so basically I have to merge each
spreadsheet to the main spreadsheet, each Row1 from each worksheet will
be
in the Row1 of the main worksheet. But I can not use Excel because I
might
end up with up to 7000 columns.

Somehow I have to get the all of the worksheets data moved into an array
very fast.

Well, everything is relative. In your case, I suspect that getting the
data out of the existing spreadsheets and writing it to wherever it has
to go will be (relatively) much slower than your merge operation.

The only way to know for sure is to take a shot at writing the merge
and, as I said, run your program under a profiler to see where it
spends most of its time. If the merge turns out to be a bottleneck,
then tune it, but I suspect that it won't be.

Retreiving data from the Excel spreadsheet is relatively very fast, but
merging data into the array takes abut 10 minutes, compared to about 10
secods to retreive the data from the spreadsheet.

Here's my merge code.

for(int n = 0; i < importData.Coun t; n++)
{
mergedTable = (DataTable)impo rtData.Item[n];

for(int i = 1; i < maxRecords; i++)
{
col = holdCol;
for(int c = 1; c < mergedTable.Col umns.Count - 1; i++)
{
val = Convert.ToDoubl e(mergedTable.R ows[i][c]);
dataArray[i - 1, col] = val;
col++; // next column
}
}
}

So I need code that will move data from a table into an array very fast.

[Kevin Yu [MSFT]]

Hi Peter,

From the code, I can see that you're combining all the records in the
DataTables like the following:

data from DataTable1
data from DataTable2
data from DataTable3
........

In this case, if the schema of the tables are the same, when filling the
DataTable, you can simply use three DataAdapters to fill the same
DataTable. For example, filling it 3 times, like

DA1.Fill(dt);
DA2.Fill(dt);
DA3.Fill(dt);

will put data from 3 tables to the same DataTable.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

[Peter]

Unfortunately the schema is different for each table, number of columns and
column names are different and on top of if the Master table will contain
only numbers, but the sheets might contain text columns which I have to
remove.
(Data comes from a spreadsheet supplied by a user)

So I think I need a routine that can move data from DataTable to an array
very fast.

"Kevin Yu [MSFT]" <v-****@online.mic rosoft.com> wrote in message
news:l%******** ********@TK2MSF TNGXA02.phx.gbl ...

Hi Peter,

From the code, I can see that you're combining all the records in the
DataTables like the following:

data from DataTable1
data from DataTable2
data from DataTable3
.......

In this case, if the schema of the tables are the same, when filling the
DataTable, you can simply use three DataAdapters to fill the same
DataTable. For example, filling it 3 times, like

DA1.Fill(dt);
DA2.Fill(dt);
DA3.Fill(dt);

will put data from 3 tables to the same DataTable.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

[Bruce Wood]

The only thing I see in your code that would account for the 10 minutes
is the Convert.ToDoubl e() call. In what form are you getting the data
from the Excel spreadsheets?

[Peter]

"Bruce Wood" <br*******@cana da.com> wrote in message news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

The only thing I see in your code that would account for the 10 minutes
is the Convert.ToDoubl e() call. In what form are you getting the data
from the Excel spreadsheets?

Here's sample data (delimited by spaces)

7/1/04 0:00 80 80.4143219 41.76826859 3620 85.99505615 4.173601627 722.7902832 674.1519775
7/1/04 1:00 80 80.41130066 41.76562119 3619.800049 85.99694824 4.63732338 777.8297119 730.9456177
7/1/04 2:00 80 79.91899872 41.76296997 3620 85.99486542 4.051301479 724.458313 654.1055908
7/1/04 3:00 80 80.48442078 41.76031876 3619.857178 85.99700165 4.282075405 720.6036987 700.1008301
7/1/04 4:00 80 80.36474609 41.75767136 3620 85.99711609 4.218424797 724.3544312 676.8447876
7/1/04 5:00 80 80.23025513 41.75502014 3620 85.99019623 4.297987461 723.5835571 691.6959839
7/1/04 6:00 80 80.43131256 41.75236893 3620 85.98691559 4.190071583 721.6431885 673.8577881