Read 'date' n Sum from a text file n compare it to user's input

[star_trek]

hii all,

this is one text file i want to extract data from

Datum/Uhrzeit,Sta.,Bez.,Unit,TBId,Batch,OrderNr,Mat1,Total1,Mat2,Total2,Mat3,Total3,Mat4,Total4,Mat5,Total5,Mat6,Total6,Summe
41521.880937(04.09.13 21:08:33),TB01,TB01,005,300,9721,      ,2,27473.31,0,0.00,0,0.00,3,1790.40,0,0.00,0,0.00,29263.71
41521.922606(04.09.13 22:08:33),TB01,TB01,005,300,9741,      ,2,27528.53,0,0.00,0,0.00,3,1795.30,0,0.00,0,0.00,29323.83
41521.964274(04.09.13 23:08:33),TB01,TB01,005,300,9760,      ,2,27580.88,0,0.00,0,0.00,3,1799.97,0,0.00,0,0.00,29380.84
41522.005942(05.09.13 00:08:33),TB01,TB01,005,300,9780,      ,2,27636.00,0,0.00,0,0.00,3,1804.86,0,0.00,0,0.00,29440.86
41522.047610(05.09.13 01:08:33),TB01,TB01,005,300,9800,      ,2,27691.12,0,0.00,0,0.00,3,1809.75,0,0.00,0,0.00,29500.87

I am unable to extract date and Summe from this file

04.09.13 21:08:33     29263.71
04.09.13 22:08:33     29323.83
04.09.13 23:08:33     29380.84
05.09.13 00:08:33     29440.86
05.09.13 01:08:33     29500.87

Compare the dates with user input and shows the difference

I am new to C#.
What i have tried till now is i have taken these datas as comma(,) seperated values. So i have 20 different columns with all the values but i don't know how to extract date from the first column and take the value of Summe of that same column.

Any help would me great.

[Fenrir()]

I assume you're reading the data into an array. You know that your date will be in column 1 and your Summe would be in your last column so on each readline of the file you only retrieve these two values based on their index in the array.

[star_trek]

What i have first thought of is to try database related functions. i have used postgresql to extract two columns only first one n last one. But did't get the extracted values after that.
The way u are saying is that i have to make a array which stores the value to each n every line then have to give index like a[14]to a[31] for dates n same as for summe.

[star_trek]

I have 20 different files like this and have around 500 lines in each one of them.

[Fenrir()]

When are you reading the data from the files? when a user inputs data you want to go get values from the file and compare?

[kiail]

Ok, from what I'm seeing here I think you might mean you want to get the date and sum from each row, yes? Columns are up and down, rows are horizontal.
Now to help you with the actual issue at hand. You have a csv file with 20 different columns. I would create an OpenFileDialog to choose the file or code the file name and location into the program(I don't recommend this because if you use it on a different computer, it might not be there)...(OpenFileDialog is cool to me because you can pick whichever file from where ever). I'd open the file for read access with something like:

   output = new FileStream(strFileName,
                    FileMode.Open, FileAccess.Read);

                fileReader = new StreamReader(output);

Then from there you'd need some variable(s) to hold the records.
I would then loop through the records and break into the fields...
Look into using a Split and understand what you're doing with it, it's very useful with CSV, in fact it's necessary. Here is a small example of a simple split that I have used. It does not however account for your 20 fields, only two.

 while (fileReader.EndOfStream != true)
                {
                    //read a record
                    strInputLine = fileReader.ReadLine();

                    //split the record
                    fields = strInputLine.Split(',');

                    //add to the listbox
                    listBoxInput.Items.Add(fields[0] + ", " + fields[1]);
                }

In my code I'm displaying the fields in a listbox...you could do whatever you want with them. You could simply add your fields together by converting them into whatever data type you want and summing them... I've left some code out so I'm not just giving you the answer, and also remember to add a try/catch/finally...and make sure to close your reader/stream. I hope this helps.

[kiail]

Oh and depending on a few things, you might want to change how you add the fields to be displayed...use stringbuilder if you plan to change them, or use a string if not. There are other reasons to use stringbuilder too though...so definitely look into it. If you need more help, just let us know what you're doing with it and what you've tried so far.

[star_trek]

Yes whenever the user is entering date i want to take sum value from the data of those date n want to calculate the total consumption between those dates.

I have sucessfully extracted the data of date n sum from the table.
i have seperated them by ',' . But the real problem is still date is not in correct format.

this is the result i got after seprating the data in columns

41521.880937(04.09.13 21:08:33)     29263.71
41521.922606(04.09.13 22:08:33)     29323.83
41521.964274(04.09.13 23:08:33)     29380.84
41522.005942(05.09.13 00:08:33)     29440.86
41522.047610(05.09.13 01:08:33)     29500.87

now i am taking input date time from the user n then want to compare it with dates in the data. But it is not getting compared from this data that i have in result.

[star_trek]

As i have told earlier also i have 20 files which have data in these formats.
user is choosing a type n entering dates(from n to).According to the type of data choosen by the user i have to select a file from these 20 files then search for dates in these files.

I have tried it with postgresql also.
I have created a table for each file and put the data of these files seperately in those tables. but still its about extracting date from 41521.880937(04.09.13 21:08:33).

[Fenrir()]

So you're looking for something like this?

DECLARE @Val VARCHAR(100) = '41521.880937(04.09.13 21:08:33)'
SELECT CAST(REPLACE(SUBSTRING(@Val,CHARINDEX('(',@Val,0)+1,(CHARINDEX(')',@Val,0)-CHARINDEX('(',@Val,0)-1)),'.','-') AS DATETIME) AS Date

Wrote this very quickly so feel free to play around with it this code assumes the date will be in the above mentioned format.

[star_trek]

I didn't understand what u have written n where to play with this code.
In which language u have written this code?????
Explain please...

[Fenrir()]

Sorry i was assuming you were using MS SQL here is the C# version

string test = "41521.880937(04.09.13 21:08:33)";
DateTime date = Convert.ToDateTime(test.Substring(test.IndexOf("(")+1, (test.IndexOf(")") - test.IndexOf("(")-1)).Replace(".","-"));

output : 4/9/2013 9:08:33 PM

[star_trek]

This is what i have tried

for (int y = 1; y < nofrows; y++)
            {
                line = sReader.ReadLine();
                tileNo = line.Split(',');
             //   string s = tileNo[0];
             //  Console.WriteLine(s , tileNo[0]);

                string test = tileNo[0];     //string builder required
               DateTime date = Convert.ToDateTime(test.Substring(test.IndexOf("(") + 1, (test.IndexOf(")") - test.IndexOf("(") - 1)).Replace(".", "-"));
                Console.WriteLine(date);
            }

its giving an error as "argument out of range exception was unhandled"
I have started from row 2 because first row is heading "Datum/Uhrzeit".
when i have tried it using string s it has shown the results.
But while using your code it shows error while executing.
Do i need a String Builder function over here or some other way is there????

[star_trek]

Got the solution...

      for (int y = 1; y < nofrows; y++)
                {

                    line = sReader.ReadLine();
                    tileNo = line.Split(',');
                    string s = tileNo[0];
                    try
                    {
                        string x = s.Substring(s.IndexOf("(") + 1, (s.IndexOf(")") - s.IndexOf("(") - 1)).Replace(".", "-");
                        DateTime datum = Convert.ToDateTime(x);
                        Console.WriteLine(datum);
                    } 
                    catch(Exception df)
                    {

                    }
                } 
                sReader.Close();

        }

[Fenrir()]

Glad we could be of assistance.

[star_trek]

just one more thing

sReader = new StreamReader(path);

            DateTime datum;
            for (int y = 1; y < nofrows; y++)
            {
                line = sReader.ReadLine();
                tileNo = line.Split(',');
                string s = tileNo[0];
                try
                {
                    string x = s.Substring(s.IndexOf("(") + 1, (s.IndexOf(")") - s.IndexOf("(") - 1)).Replace(".", "-");
                     datum = Convert.ToDateTime(x);
                Console.WriteLine(datum);
                } 
                catch(Exception df)
                {
                    Console.WriteLine(df);
                }
                k = tileNo[19];
                Console.WriteLine(k);
            } 
            sReader.Close();

BY this code i got date values in "datum" and sum value in "k".
I want to take input from the user as 'from_date' n 'to_date' and want to compare them with the data i have in datum n show the sum in between those two dates.
when i have tried it gives error as u cannot use datum as it is a unassigned variable.
how can i compare them???

[Fenrir()]

The variable datum is only assigned inside your try{} so that's where you need to do your comparisons

[Fenrir()]

decimal Summe = 0;     // <-- outside loop
for (int y = 1; y < nofrows; y++)
{
  line = sReader.ReadLine();
  tileNo = line.Split(',');
  string unparsedDate = tileNo[0];
  DateTime datum = Convert.ToDateTime(unparsedDate.Substring(unparsedDate.IndexOf("(") + 1, (unparsedDate.IndexOf(")") - unparsedDate.IndexOf("(") - 1)).Replace(".", "-"));
  if (datum > DateStart && datum < DateEnd)
  {
    Summe = Summe+Convert.ToDecimal(tileNo[tileNo.Length - 1]);
  }
}

This will get the total for all summe's in the file for the dateStart and DateEnd provided.