Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL Excel DataTable
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.
 
Input:
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]'"
to
"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")
   {
      myCOpy.Columns.Add(myCol);
   }
}
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")
   {
      myKillerCol.Add(myCol.ColumnName.ToString());
   }
}
foreach (String myStringrunner in myKillerCol) 
{
   myTestTable.Columns.Remove(myStringrunner);
}
 
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/...
Greetings
Ben
Posted 15-Jan-13 0:17am
BenKoe130
Edited 15-Jan-13 0:41am
v2
Comments
digimanus at 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
good
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.
 
Example:
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);
            }</string></string></string>
 
Greetings Ben
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 190
1 Jochen Arndt 155
2 PIEBALDconsult 150
3 Afzaal Ahmad Zeeshan 120
4 DamithSL 115
0 OriginalGriff 5,695
1 DamithSL 4,591
2 Maciej Los 4,012
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,190


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 16 Jan 2013
Copyright © CodeProject, 1999-2014
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