Click here to Skip to main content
14,921,476 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
OleDbCommand cmd = new OleDbCommand();

                        cmd.Connection = con;
                        // set your file name in the below query
                        cmd.CommandText = "SELECT * FROM " + csv ;
     
                        //Open Oledb Connection to read CSV file 
                        con.Open();

                        //Create one datatable to store data from CSV file
                        DataTable dt = new DataTable();

                        // Load Data into the datatable 
                        dt.Load(cmd.ExecuteReader());
                        con.Close();

                        foreach (DataRow row in dt.Rows)
                        {
                            lstData.Add(new Tablelist() { process = row["process"] + "", Event = row["Event"] + "", status = row["status"] + "" });
                            
                        }

                        // assign the lstData to the gridview..
                        GridViewShow.DataSource = lstData;
                        GridViewShow.DataBind();

This code allowed me to read 3 csv files which is 0730.csv, 0830.csv,0930.csv and show all in gridview with this 3 columns : process, event, status...
can I actually show it with this output format
0730|0830|0930|
pass|pass|pass|

1)i need to show this table ,
2)pass is the total count of how many pass,
3)which is read from the "status column" of this 3 csv :0730.csv, 0830.csv,0930.csv
Posted
Updated 1-Jan-14 18:04pm
v2
Comments
ZurdoDev 1-Jan-14 21:38pm
   
I don't understand your question or where you are stuck.
acing wei 1-Jan-14 22:03pm
   
my code above which is read from 3 csv files :0730.csv, 0830.csv,0930.csv..merge in one table giving me this table

process|event|status|
abcdef|1234556|pass/fail|


what i want the output ,read from the "status column" above ,count the total pass and fail, give this table according to csv that read.
0730|0830|0930|
pass|pass|pass|

pass = how many pass..
Karthik_Mahalingam 2-Jan-14 1:27am
   
acing, will continue here..
you need only the pass count ?
acing wei 2-Jan-14 1:42am
   
and also the fail count...

Hi try like this...
C#
foreach (DataRow row in dt.Rows)
        {
            lstData.Add(new Tablelist() { FileName=csv,  process = row["process"] + "", Event = row["Event"] + "", status = row["status"] + "" });

        }
    }
        DataTable dtoutput = new DataTable();
       var columnNames =  lstData.Select(k => k.FileName).Distinct();
       foreach (string columnName in columnNames)       
           dtoutput.Columns.Add(columnName, typeof(string));
       DataRow newrowPassCount = dtoutput.NewRow();
         foreach (string columnName in columnNames) {
            int count = lstData.Where(k=> k.FileName == columnName).Count(k => k.status == "Pass");
            newrowPassCount[columnName] = count; 
        }
         dtoutput.Rows.Add(newrowPassCount);

         DataRow newrowFailCount = dtoutput.NewRow();
         foreach (string columnName in columnNames)
         {
             int count = lstData.Where(k => k.FileName == columnName).Count(k => k.status == "Fail");
             newrowFailCount[columnName] = count;
         }
         dtoutput.Rows.Add(newrowFailCount);

         gridview.datasource = dtoutput;
         gridview.databind();


add a new property FileName in the class

C#
class Tablelist
{
    public string process { get; set; }
    public string Event { get; set; }
    public string status { get; set; }
    public string FileName { get; set; }
}
   
Comments
acing wei 2-Jan-14 3:51am
   
it display 0 in the column..
Karthik_Mahalingam 2-Jan-14 3:56am
   
which column?
Karthik_Mahalingam 2-Jan-14 3:57am
   
FileName=csv.Replace(".csv","").Replace(".CSV","");
acing wei 2-Jan-14 3:58am
   
all column...i debug and see the value from list data didn't go into the int count
Karthik_Mahalingam 2-Jan-14 4:07am
   
check the case for "pass"
acing wei 2-Jan-14 4:16am
   
yes it works...thank you .....
Karthik_Mahalingam 2-Jan-14 4:26am
   
welcome acing :)
acing wei 2-Jan-14 5:04am
   
ohya... if want to change to "chart" what should i put as x and y VAlue? i try but mine cannot work
Karthik_Mahalingam 2-Jan-14 5:10am
   
you r working on gridview know..
y suddenly chart came :)
Karthik_Mahalingam 2-Jan-14 5:11am
   
i am not so expert in charts.
you can post a new question.
might be others who are expert in chart can guide you..
acing wei 2-Jan-14 5:28am
   
Alright.its okay...thank you... :)
Karthik_Mahalingam 2-Jan-14 5:35am
   
:)
acing wei 2-Jan-14 22:14pm
   
oh no..after i checked with my table the pass and the fail count is wrong...help..
Karthik_Mahalingam 2-Jan-14 22:59pm
   
post your code..
Karthik_Mahalingam 2-Jan-14 23:18pm
   
1)make sure all the csv file names are (.csv, .CSV only)
2) check the case for ( Pass , Fail )
apply break point and debug it...
acing wei 2-Jan-14 23:38pm
   
i found something for eg: I read 0730.csv,o830.csv,and 0930.csv the first 0730.csv pass and rework count is correct, 0830 and 0930 will be wrong...
when i read eg:o830.csv, 0930.csv and 1030.csv , the 0830.csv will be correct..only first looping count is correct...i use break point to check only first loop count is correct after 2nd and 3rd loop the total count will become wrong.
Karthik_Mahalingam 3-Jan-14 0:25am
   
try this
k.status.Trim() =="PASS"
acing wei 3-Jan-14 1:02am
   
tried.. still same..only first csv pass/fail count correct..
Karthik_Mahalingam 3-Jan-14 1:12am
   
how many rows are there in lstData ?
acing wei 3-Jan-14 1:18am
   
3 rows.. i know why the 2nd and 3rd file when i read the pass and fail count become wrong..they actually sum up the previous file pass/fail eg: i read 0730.csv,o830.csv,and 0930.csv ..pass and fail in o830.csv,and 0930.csv will be wrong because o830.csv sum up the count of 0730.csv..and 0930.csv sum up both 0730.csv,o830.csv ass /fail....it never clear the value..
Karthik_Mahalingam 3-Jan-14 1:28am
   
no no, pls check this lstData.Count()
acing wei 3-Jan-14 1:41am
   
i debug and put break point..
int count = lstData.Where(k => k.FileName == columnName).Count(k => k.status == "PASS");
the lstdata.count=368 how they get 368?
Karthik_Mahalingam 3-Jan-14 1:50am
   
yes it will looping on the datatables..
acing wei 3-Jan-14 1:48am
   
oh, thats the row count that read from table list...but my row i count it manually between 0730 & 0830 total 262.. the lstdata. count counted it 368
Karthik_Mahalingam 3-Jan-14 1:50am
   
lstdata count is common...
we are iterating the lstdata.. it wont change..
acing wei 3-Jan-14 1:58am
   
so what should i do now? it keep taking previous file pass/fail count.
Karthik_Mahalingam 3-Jan-14 2:04am
   
ok wait for some time, i wil have my lunch and be back in few mins...
acing wei 6-Jan-14 1:53am
   
mr karthik, can explain me how to get a simple chart from my new tablelist? i have solved my table problem yesterday..
Karthik_Mahalingam 6-Jan-14 2:50am
   
what kind of chart.
for chart you can post a new question.
might be some experts on chart will answer you the exact mean.
acing wei 6-Jan-14 3:15am
   
I post before but nobody help me....i research google but i can't find what i need..a column chart using chart binding.
Karthik_Mahalingam 6-Jan-14 3:20am
   
you re-post it by adding proper and sufficient information , so that the user wil be able to understand your task easily and will help you for sure,
if they didn't understand the question, they wont consider your question..
acing wei 13-Jan-14 3:23am
   
mr karthik thanks for your suggestion , i did it for my chart, i got another question..
dtoutput.Rows.Add(newrowPassCount);
this code that time you suggest me created a new row...i don't want the value inside the row to be overwrite everytime it loops,after second time of looping i want the value to appear in a new row, can you suggest me?
Karthik_Mahalingam 13-Jan-14 3:48am
   
it will diplay only 2 rows
one is for pass count
another is for fail count..
acing wei 13-Jan-14 4:11am
   
yes correct,for example i got another file , in a second looping can i create another row of pass and fail count, instead of just overwrite...now become 4 rows, then after third loop it become 6 rows..i try using
foreach (DataRow row in dtoutput.Rows)
but it can't work...please?
acing wei 13-Jan-14 5:52am
   
I try couple of times using loop but I can't get the idea hw to do the looping ..
Karthik_Mahalingam 13-Jan-14 5:54am
   
post your full code
acing wei 13-Jan-14 6:22am
   
The code is the solution above that you teach me... I want it to create a new row after every looping..the value of the newrowpasscount and newrowfailcount will be created in a new row ..keep adding new row..
acing wei 13-Jan-14 6:23am
   
The value of newrowpasscount and newrow failcount will be different value in every row that created..
acing wei 13-Jan-14 21:26pm
   
foreach (DataRow row in dt.Rows)
{
lstData.Add(new Tablelist() { FileName=csv, process = row["process"] + "", Event = row["Event"] + "", status = row["status"] + "" });

}
}
DataTable dtoutput = new DataTable();
var columnNames = lstData.Select(k => k.FileName).Distinct();
foreach (string columnName in columnNames)
dtoutput.Columns.Add(columnName, typeof(string));
DataRow newrowPassCount = dtoutput.NewRow();
foreach (string columnName in columnNames) {
int count = lstData.Where(k=> k.FileName == columnName).Count(k => k.status == "Pass");
newrowPassCount[columnName] = count;
}
dtoutput.Rows.Add(newrowPassCount);

Karthik_Mahalingam 13-Jan-14 21:37pm
   
this code is fine.. it will add one row of data to the datatable...
acing wei 13-Jan-14 22:11pm
   
yes is fine creating a new row with (newrowpasscount) value..but now i need more row each time it loops it will give me (newrowpasscount) to show in another new row.. i use this but cannot work..
foreach (DataRow row in dtoutput.Rows)
{
dtoutput.Rows.Add(newrowPassCount);
}
Karthik_Mahalingam 14-Jan-14 6:27am
   
you have teamviewer ?
acing wei 14-Jan-14 6:33am
   
No , I really need your help to guide me...because my teacher not good in c#.net
Karthik_Mahalingam 14-Jan-14 6:35am
   
pls install team viewer in your machine..
and give me the id..
acing wei 14-Jan-14 6:53am
   
sorry but I can't because is a school project..and im not allowed to do installation in the machine..can just help me here?
Karthik_Mahalingam 14-Jan-14 11:36am
   
u can send the full code of that page only and screen shot to my mail id mtp.karthik@gmail.com
acing wei 14-Jan-14 20:09pm
   
I sent
Karthik_Mahalingam 14-Jan-14 22:55pm
   
ok i will check later... now i am in office, i cant check now..
acing wei 15-Jan-14 3:05am
   
Thank you :)
acing wei 15-Jan-14 9:26am
   
if you are not busy later..please help me see mine, thanks :)
Karthik_Mahalingam 15-Jan-14 9:37am
   
still i am in office, i will go to home in half hour after that i will see your mail..
acing wei 16-Jan-14 22:48pm
   
Hi have you check for me?
Karthik_Mahalingam 16-Jan-14 22:58pm
   
in office now, tomo leave for me, your issue will be resolved.. dont worry...
Loop for one DataTable and count that column store in a Variable.
Then go for next, count the same column and store in another variable and so on for other DataTables.

Then sum all the variable counts. You now have the total count.

Create a DataTable and make columns as you need and show these data under appropriate columns.
Comments
acing wei 1-Jan-14 23:31pm
   
yes, when everytime looping i need to store it, how to store a variable.. how to use count and sum in a datatable?
   
Show me what you have tried, if there are any problems, then I will try to rectify it.
   
I can't understand. Can you please explain what are trying to achieve? I am getting confused.
acing wei 14-Jan-14 20:13pm
   
i want to achieve a new row of newrowpasscount everytime it loops..because a newrowpasscount will giving me different value everytime it loops..i need new row to put my newrowpasscount ..

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900