How to count column in dataset

2012-03-23

Question

_{Friday, March 23, 2012 8:40 AM}

Hi, my application read excel file into dataset.

The excel file is actually look like a report. So the total column for each row is different.

Does anyone has idea how to count total column for a particular row?

Help Please!!

All replies (15)

_{Friday, March 23, 2012 8:52 AM}

Count or sum?

The count is simply DataTable.rows.Count

The sum can be done using the DataTable.Compute

All kind of samples on this official page

http://msdn.microsoft.com/en-us/library/system.data.datatable.compute.aspx

Success
Cor

_{Friday, March 23, 2012 8:58 AM}

you can use the Count method of the ItemArray for any row.

Replace the index for the Rows with whatever row you are looking for:

myDataSet.Tables(0).Rows(0).ItemArray.Count

_{Friday, March 23, 2012 9:03 AM}

Hi Lighert, is count actually.

For example the first row in the dataset might be 3 columns, the second rows is 5 column. How do i count each column in each row so that i can loop.

I try with below code,

 For Each currentRow As System.Data.DataRow In dsExcel.Tables(0).Rows

                For Each currentColumn As DataColumn In currentRow

                    MessageBox.Show(currentRow(currentColumn.ColumnName).ToString())

                Next

            Next

but it give me error "Expression is of type 'System.Data.DataRow', which is not a collection type" at variable currentRow.

Do you have any idea?

_{Friday, March 23, 2012 9:09 AM}

that's a lot of messageboxes you'll be closing :)

do you merely want the Count or do you want ColumnNames also?

again, using the code I posted above

For Each currentRow As System.Data.DataRow In dsExcel.Tables(0).Rows
    Debug.Writeline(currentRow.ItemArray.Count)
       
 Next

_{Friday, March 23, 2012 9:15 AM}

Hi jwavila,

It prompt me the error below Count' is not a member of 'System.Array'.

I want to count the total column of the row and then i will loop dsExcel.tables(0).rows(0) (iCount).

Do you have any idea?

_{Friday, March 23, 2012 9:21 AM}

I don't get that error

did you use the code exactly as it's written?

please post your updated code

_{Friday, March 23, 2012 9:27 AM}

Hi jwavila, here is my code

_{Friday, March 23, 2012 9:29 AM}

Count is an Integer

change iCount to an Integer datatype

_{Friday, March 23, 2012 9:31 AM}

Opps, my mistake.. Still the same TT

_{Friday, March 23, 2012 9:40 AM}

I don't know what to tell you

I'm loading data from a SQLCE database into a DataSet in a test app.

Then iterating through the rows exactly as you show, and I'm not getting that error. Everything works fine

I can't even change things to reproduce it

maybe somebody else can see the problem - it's 2:30 AM here

_{Friday, March 23, 2012 9:50 AM}

hi jwavila,

You prompt help is appreciated. i will try other solution, if i got it i will post here.

Have a sweet dream ^^

_{Friday, March 23, 2012 10:51 AM}

Johnson,

Every spreadsheet is converted to a datatable. Which is not a spreadsheets.

It exist from a collection of columns and a collection of rows; which get their information from the columns.

In the datatable the quantity of columns is always the same where it is not important if the items are used with data or are simply empty.

dim countOfColumns = ds.Excel.Tables(0).Columns.Count

Success
Cor

_{Friday, March 23, 2012 4:13 PM}

I think the discussion is about a structured data file that's been opened in Excel and, generally speaking, the first row would define the record type. Perhaps a loop through a 'SELECT DISTINCT COLUMN(0)' query would offer the solution to this delimma, then 'SELECT DISTINCT * FROM [mytable] WHERE COLUMN(0) = ' @distinctrecordtype loop to define how many rows are present by picking the max number of column entries for each distinctrecordtype.

for i = 0 to tbDistinctColumns - 1

initDistinctRecordType = cstr(tblDistinctColumns.rows(i).items(0))

for each item in tblDistinctColumns.rows(i).items

if not isnull(item) and j > maxJ then

maxJ = j

j = j + 1

initDistinctRecordType & "Has " & FORMAT(maxJ, "0000") & " Columns"

Debug.Print initDistinctRecordType

next i

Then, the result would have some reasonable bearing on column count, ie. DistinctRecordType(i, j) where i would represent the recordtype and j the

Dan

_{Friday, March 23, 2012 8:30 PM}

Let's assume you want to get the number of populated cells in a row in an Excel spreadsheet.

You would select a cell, generally at the end of the row you are working with, and type in:

=Count(

At that time, you'll be able to select the range you want to count the populated rows in. After selecting the range of cells you want to count, Type in the closing ) and hit return. For whatever range you selected, the cell with the formula will count the number of cells that have some type of content.

Similarly, you can type in =sum( and select a range of cells, put in the closing bracket, and hit return to populate that cell with the sum total of what is in that range of cells. You can go to the bottom of the sheet, under the column of counts or sums and do an =count( myrange ) or =Sum(myrange) to get a total sum of the entire form.

Actually, you can go to another sheet, type in =( and navigate back to your form, select a total, and add the closing bracket to put that sum or count into another sheet.

You can name individual cells and/or ranges of cells as tables instead of just doing sheet1 or mysheet name, and open workbook and specify which 'table' to open instead of relying on sheet numbers and column and row numbers to pull contents.

A form or set of forms can be referenced to a single table that will update and be updated by cells in the working form. Excel is very versitile.

Dan Kirk

_{Monday, March 26, 2012 2:48 AM}

geez. . . .

var nonNullCellCountPerRowArray = dt.Rows.OfType<DataRow>()
                  .Select(row => 
                       row.ItemArray.Where(i => i != DBNull.Value)
                            .Count()
                          ).ToArray();

in c# cause I don't do vb for free.

gimme some slamming techno!!!!

שתף באמצעות

How to count column in dataset

Question

All replies (15)

משאבים נוספים