Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Efficient Datatable value manipulation

Posted on 2008-10-16
10
Medium Priority
?
982 Views
Last Modified: 2012-05-05
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
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

Open in new window

0
Comment
Question by:Groovyolly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 2

Assisted Solution

by:MasterPage
MasterPage earned 400 total points
ID: 22737563
Regex.Replace can be slow.  Is chrX a regular expression?  If not (that is, if you're just doing simple character or string replacement), String.Replace should be considerably faster.

inVal = Val(Regex.Replace(inVal, chrX, "")) * Factor

becomes

inVal = Val(inVal.Replace(chrX, "")) * Factor

0
 

Author Comment

by:Groovyolly
ID: 22737726
Cheers for that.

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.
 

0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22737736
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();
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Groovyolly
ID: 22737822
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:
Dim drSelect As DataRow() = tab_Data.Select("ColumnName like '%" + colNam + "'%")

Open in new window

0
 
LVL 26

Accepted Solution

by:
Anurag Thakur earned 1600 total points
ID: 22737850
the quote is wrong
it should be like this
Dim drSelect As DataRow() = tab_Data.Select("ColumnName like '%" + colNam + "%'")
0
 

Author Comment

by:Groovyolly
ID: 22737888
Sadly this returns a 'Cannot find column [ColumnName]'. I've tried switching the expression around a little, with no effect.
0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22746851
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
0
 

Author Comment

by:Groovyolly
ID: 22750744
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("ColumnName 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("ColumnName 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.
0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22752471
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 + "%'")
}
0
 

Author Comment

by:Groovyolly
ID: 22754621
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.
Dim drSelect As DataRow() = tab_Data.Select("[" & colNam & "] Like '%" + chrX + "%'")

Open in new window

0

Featured Post

DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question