Groovyolly
asked on
Efficient Datatable value manipulation
Hello Experts,
I'm developing an application in Visual Studio 2008 to parse and coalesce multiple laboratory results from *.csv into one single DataTable. Resultant tables tend to contain 1000+ records over >30 columns. The user is intended to first view the data in a DataGrid and then manipulate select columns accordingly using a set of functions (generally removing erroneous characters such as "<", ">"). The data is then refreshed to the DataGrid.
My current method of is slow and inefficient, taking ~45 seconds per column, per 1000 records. Is there a better way to manipulate data within datatables? I realise manipulating the underlying data prior to visualisation would be better, however the user must see the data first to choose effective treatments.
Any help would be appreciated.
Olly
I'm developing an application in Visual Studio 2008 to parse and coalesce multiple laboratory results from *.csv into one single DataTable. Resultant tables tend to contain 1000+ records over >30 columns. The user is intended to first view the data in a DataGrid and then manipulate select columns accordingly using a set of functions (generally removing erroneous characters such as "<", ">"). The data is then refreshed to the DataGrid.
My current method of is slow and inefficient, taking ~45 seconds per column, per 1000 records. Is there a better way to manipulate data within datatables? I realise manipulating the underlying data prior to visualisation would be better, however the user must see the data first to choose effective treatments.
Any help would be appreciated.
Olly
Function ConvertNo(ByVal Factor As Decimal, ByVal colNam As String, ByVal chrX As String)
Dim inVal = Nothing
For Each dRow As DataRow In tab_Data.Rows
inVal = dRow(colNam)
If Not IsDBNull(inVal) Then
inVal = Val(Regex.Replace(inVal, chrX, "")) * Factor
dRow(colNam) = inVal
End If
Next dRow
Return inVal
End Function
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
first thing i noticed is that you are looping for all the rows to update just one column whether there are invalid characters or not
what you should be doing is find all the rows first for a column where there are invalid characters
DataRow [] rows = dt.Select ("ColumnName like '%" + strOne + "'% or ColumnName Like '% + strTwo + "%'");
if (rows != null)
{
for (int i = 0; i < rows.Lenght; i++)
{
// update that column value
}
}
dt.AcceptChanges();
what you should be doing is find all the rows first for a column where there are invalid characters
DataRow [] rows = dt.Select ("ColumnName like '%" + strOne + "'% or ColumnName Like '% + strTwo + "%'");
if (rows != null)
{
for (int i = 0; i < rows.Lenght; i++)
{
// update that column value
}
}
dt.AcceptChanges();
ASKER
This is a better solution fundamentally, however I'm having some trouble applying it to my situation.
I'm getting a 'Missing operand after 'Mod' operator' error with the following code:
I'm getting a 'Missing operand after 'Mod' operator' error with the following code:
Dim drSelect As DataRow() = tab_Data.Select("ColumnName like '%" + colNam + "'%")
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sadly this returns a 'Cannot find column [ColumnName]'. I've tried switching the expression around a little, with no effect.
Does the datatable contain a colulm named COLUMNNAME -
'Cannot find column [ColumnName]'
you need to replace the name COLUMNNAME with the name of the column in the datatable on which you want to do the operations
'Cannot find column [ColumnName]'
you need to replace the name COLUMNNAME with the name of the column in the datatable on which you want to do the operations
ASKER
No column called 'ColumnName' in there - the datatable columns are all added according to the parsed input.
With respect to replacing ColumnName with my column value, in the statement tab_Data.Select("ColumnNam e like '%" + colNam + "%'") colName represents the column name passed into the function, so (using a column called "A", for example) the function effectively reads tab_Data.Select("ColumnNam e Like '%A%'"). To replace ColumnName directly would result in ("A Like '%A%'"), which doesn't make sense.
Using a watch applied to the bracketed portion of the statement, I've determined that the statement is reading ("ColumnName Like '%A%'"). I'm wondering whether ColumnName is a valid reference to the column title in the Select function as it doesn't appear to be accepted by the function.
With respect to replacing ColumnName with my column value, in the statement tab_Data.Select("ColumnNam
Using a watch applied to the bracketed portion of the statement, I've determined that the statement is reading ("ColumnName Like '%A%'"). I'm wondering whether ColumnName is a valid reference to the column title in the Select function as it doesn't appear to be accepted by the function.
even if you pass the column name and the value you want to compare to a function
you can use my solution like this
-- i am assuming that you know the column name in the datatable on which the operations are to be done
private void ReplaceSomething(string columnName, string ColumnValue)
{
DataRows [] rows = tab_Data.Select(columnName + " like '%" + ColumnValue + "%'")
}
you can use my solution like this
-- i am assuming that you know the column name in the datatable on which the operations are to be done
private void ReplaceSomething(string columnName, string ColumnValue)
{
DataRows [] rows = tab_Data.Select(columnName
}
ASKER
Seems the problem was that my filter string contains a hyphen special character, so colNam needed to be surrounded by square brackets. The column name was being truncated at the hyphen, causing an error (despite this, the string actually looked fine when watched).
For reference I've included the corrected code.
I've used MasterPage's suggestion in the replace string too.
Thanks to both of you for your time and patience.
For reference I've included the corrected code.
I've used MasterPage's suggestion in the replace string too.
Thanks to both of you for your time and patience.
Dim drSelect As DataRow() = tab_Data.Select("[" & colNam & "] Like '%" + chrX + "%'")
ASKER
That speeds things up a little, however, when multiple columns are selected the processing time is still considerable.
I'm curious if there's a different approach to the standard compare - replace loop available to DataTables. Something more along the lines of a Find - Replace which would be more efficient.