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