Click here to Skip to main content
13,201,934 members (72,764 online)
Rate this:
Please Sign up or sign in to vote.
Hello Community.

I have a specific Problem with Datamanipulation in DataTabel/Set.
First I start with the framework.

Data Sources: A Big MS EXCEL (2003) worksheet with. (Call it Source.xls)
My Program, how should manipulate and analyses some Data.
Output should be another Excel ...

The Problem:
After reading in all the Data from the Source.xls into my DataTabel (called myTestTable). I would like to filter it with SQL or something.

myExcelConnecter_YieldetValue = new MyExcelConnector();
DataTable myTestTable = new DataTable();
myTestTable = (myExcelConnecter_YieldetValue.getExcelData("PathToSource.xls", "SELECT * FROM [ABC$A2:FL3000]'"));

into myTestTable

I would like to filter some Columns and then query some Cells (with SQL).

1)So first i tryed change the Import SQL
"SELECT * FROM [ABC$A2:FL3000]'"
"SELECT * FROM [ABC$A2:FL3000] WHERE A$ ='name'"

But the Syntax didn't work (and googeling for "Get Data from Excel with SQL/ADB/OleDB/..." did not help me.

2) Query the DataTable:
The Same Here I only can filter for Rows with .Select but i need to filter the Columns and then search (with SQL) for an Value in a specific Cell.
The SQL would look like this.
Select name, staat, city from myTestTable where name Like 'Testera'; 

But i have no idea how to do this in a DataTable Object.

3)The i Tried to change the DataTable in an foreach-loop.
But you can't change the Table while he is in the loop.
First i Want to copy only the tables i need to a seperate DataTable.
foreach (DataColumn myCol in myTestTable.Columns) 
   if (myCol.ColumnName.ToString() == "name")

But i get the Error (Column 'name' already belongs to another DataTable)

Then i want to delete all columns i don't use (i won't want to have)
myExcelConnecter_YieldetValue = new MyExcelConnector();
DataTable myTestTable = new DataTable();
myTestTable = (myExcelConnecter_YieldetValue.getExcelData("PathToSource.xls", "SELECT * FROM [ABC$A2:FL3000]'"));
List<String> myKillerCol = new List<string>();
foreach (DataColumn myCol in myTestTable.Columns) 
   if (myCol.ColumnName.ToString() != "name")
foreach (String myStringrunner in myKillerCol) 

That worked BUT the performance was evil. I have to do this like 200 times and one time takes 30-40sec.

4) I write all the Data to a Database where i know how to select an manipulate some Stuff. But this is only an Option if you can't help me.
Or if you say the performance of Option 4) is the Best.

Thx for reading all this and i am open for any help/tips/advise/...
Posted 14-Jan-13 23:17pm
Updated 14-Jan-13 23:41pm
digimanus 15-Jan-13 6:17am
DataView object is what you need. You add the DataTable to it, you can FILTER, SORT, any thing you want.

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

THX digimanus for that nice tip.
But i used a LINQ query to get over my problem.

the trick is to set the DataTableObject.AsEnumerable().
Then you can work with LINQ trow it.

DataTable myTestTable = new DataTable();
myTestTable = myMsSqlDB.GetBEX_ACT_TestCases(specificationDataTab); //This is the function,how i fill up my table
var query = from r in myTestTable.AsEnumerable()
            where r.Field<string>("Vorname") == "Peter"
            select new
               Name = r.Field<string>("Vorname"), // (" ") is the ColoumnName in the DataTable
               BirthDate = r.Field<string>("BirthDate"),
            foreach (var item in query) 
               Console.WriteLine(item.Name" ->> " + item.BirthDate);

Greetings Ben

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web01 | 2.8.171020.1 | Last Updated 16 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100