?
Solved

Reading CSV TXT C#

Posted on 2009-02-19
23
Medium Priority
?
1,273 Views
Last Modified: 2013-12-17
I have a Comma Delimited TXT file which i need to read in C#.

Here is a line from the file.

,LAPTOP,Desktop PC,0,1,0

In need to read the value in the 4th column (after Desktop PC).
How can i separate the values and just get the one i need, so that i can read it and then update the value.
0
Comment
Question by:AVONFRS
  • 12
  • 10
23 Comments
 
LVL 39

Expert Comment

by:abel
ID: 23680830
If it is comma delimited without quotes, an easy method is using regular expressions. Coming up..
0
 
LVL 9

Expert Comment

by:masheik
ID: 23680866
0
 
LVL 39

Expert Comment

by:abel
ID: 23680906
This should do it:

            Regex re = new Regex("^(,[^,]*){2},([^,])*.*");
            string thirdRow = re.Replace(",LAPTOP,Desktop PC,0,1,0", "$1");


The variable thirdRow contains the string "0" now. If it happens that the line does not start with a comma (sometimes yes, sometimes no), you can add [^,]* in the beginning:

            Regex re = new Regex("^[^,]*(,[^,]*){2},([^,])*.*");

HTH,
-- Abel --
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:AVONFRS
ID: 23681096
How do i use that regular expression to update the row.

I have that bit already in there, how do i replace the first 0 to say a 2?
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 23682775
Also how do i retrieve that value (the one after desktop PC?)
0
 
LVL 39

Expert Comment

by:abel
ID: 23683829
> Also how do i retrieve that value (the one after desktop PC?)

That's what the code shows: retrieving of that value.

You can also do the opposite. The replace function is excellent to replace only what's inside the brackets. We'll have to change the expression a bit though, and now that we're replacing and working with strings, it might be better to change the approach slightly. Instead of replace I'd suggest using Split, which is quite a bit easier to follow and manipulate than regular expressions. Like so:

// The source data
string source = ",LAPTOP,Desktop PC,0,1,0";
 
// Separated in columns into an array of strings
string[] columns = source.Split(new char[]{','});
 
// do something with contents of fourthCol
string fourthCol = columns[3];   // first column idx=0, but first col is empty in ex.
 
// replace the fourth column with a new value
columns[3] = "new value";
 
// Combine the result back into one string, comma separated, now containing the change
string result = String.Join(",", columns);

Open in new window

0
 
LVL 1

Author Comment

by:AVONFRS
ID: 23692981
Thank you very very much for your help.

How can i replace the new string in the text file with the old string in the text file. StreamWriter doesnt seem to have an option for updating or replacing a single line.
0
 
LVL 39

Expert Comment

by:abel
ID: 23693058
There are many, many ways of doing so. Can you post a larger snippet of your code so that I can add the necessary lines for you based on the already used methods?
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 23693258

// The source data
                        string source = document;
 
                        // Separated in columns into an array of strings
                        string[] columns = source.Split(new char[] { ',' });
 
                        // do something with contents of fourthCol
                        string fourthCol = columns[3];   // first column idx=0, but first col is empty in ex.
 
                        int curval = Convert.ToInt32(fourthCol);
 
                        if (curval >= 1)
                        {
                            int newsum = curval - 1;
 
                            // replace the fourth column with a new value
                            columns[3] = newsum.ToString(); ;
 
                            // Combine the result back into one string, comma separated, now containing the change
                            string result = String.Join(",", columns);
 
                            StreamWriter updaterecord = new StreamWriter(@"\Program Files\SmartDeviceProject1\products.txt", true);
 
THE UPDATE LINE BIT NEEDS TO GO HERE TO REPLACE THE DOCUMENT STRING AT THE TOP OF THE PAGE. 

Open in new window

0
 
LVL 39

Expert Comment

by:abel
ID: 23704756
You are opening a StreamWriter for Append (the "true" at the end), did you mean to add the lines to the file? From your text I get it that you want to update the first line of the original file, which is what I did below. Guessing that you opened with a StreamReader, the whole code will look something like in the code snippet below.

A few notes on things I changed / altered:
  • I added a "using" statement around the StreamReader / StreamWriter. This is considered best-practice by Microsoft and reliefs you of a tedious try/catch/finally block. The "using" makes sure that the object is closed, even when an error is raised. In a using-codeblock, you do not need to call the .Close() method. We do so, however, because we need to move the files around:
  • I added a method for updating a file. There're many methods, this is one of them, which ensures integrity and never fails with a half-finished or corrupt file:
    • Reading the file with one handler
    • Writing the file to a file with the same name + ".new" with another handler
    • Closing both files
    • Removing the old file
    • Renaming the new file
  • I used a test file with a different name. If the file is relative to the execution directory, you do not need a full path. Also, it is recommended to use forward slashes on any Win32 system, it makes it easier to program (and under the hood microsoft uses forward slashes anyway).
  • If you work with relative files and add those non-project files to your project, make sure to set the property "Copy To Output Directory" = "Copy Always", otherwise you have to do that manually when debugging.
  • I put the filename in a local constant: FILENAME, for easier coding.
Hope this piece of code helps you a bit further. I'll be around for any questions ;)

const string FILENAME = "Data/Q24157837.csv";
 
// The source data
using (StreamReader sr = new StreamReader(FILENAME))
{
    string source = sr.ReadLine();
 
 
    // Separated in columns into an array of strings
    string[] columns = source.Split(new char[] { ',' });
 
    // do something with contents of fourthCol
    string fourthCol = columns[3];   // first column idx=0, but first col is empty in ex.
    int curval = Convert.ToInt32(fourthCol);
 
 
    if (curval >= 1)
    {
        int newsum = curval - 1;
 
        // replace the fourth column with a new value
        columns[3] = newsum.ToString(); ;
 
        // Combine the result back into one string, comma separated, now containing the change
        string result = String.Join(",", columns);
 
        // open file for writing (other name)
        using (StreamWriter sw = new StreamWriter(FILENAME + ".new"))
        {
            sw.WriteLine(result);
            while (!sr.EndOfStream)
            {
                sw.WriteLine(sr.ReadLine());
            }
 
            // we have safely written now. Yet we should rename the .new file:
            // first close the files, otherwise rename will fail
            sr.Close();
            sw.Close();
 
            // now remove the old and rename the new. This is one of the
            // safest methods for dealing with files
            File.Delete(FILENAME);
            File.Move(FILENAME + ".new", FILENAME);
        }
    }
}

Open in new window

0
 
LVL 1

Author Comment

by:AVONFRS
ID: 23731405
Thanks for that, but the line i need to update is not always on the first line, it could be on the 50th line.

Instead of using sr.ReadLine source, can i change the source string to document
0
 
LVL 39

Expert Comment

by:abel
ID: 23731765
> can i change the source string to document

You can of course change everything to the way you like it, variable names are just placeholders.

>  StreamWriter doesnt seem to have an option for updating or replacing a single line.
This is correct, there isn't any. If you want to update one line (or any number of lines) you should loop through all the lines, update them, and loop through all the lines again to write them. You can do that in one loop by opening a different file for writing (just as shown above). By the end of the loop you do the .Close and the Move.
0
 
LVL 39

Accepted Solution

by:
abel earned 2000 total points
ID: 23731897
Here's sample code that changes every line where "curval >= 1" with the curval being one lower. I changed the Convert-line into a TryParse line, because sometimes (in my testdata) the value could not be interpreted as an integer and raised an exception. TryParse does not raise an exception but just stores zero.

private void btnQ24157837_Click(object sender, EventArgs e)
{
    const string FILENAME = "Data/Q24157837.csv";
 
    // The source data
    using (StreamReader sr = new StreamReader(FILENAME))
    {
        // the target file (temp)
        using (StreamWriter sw = new StreamWriter(FILENAME + ".new"))
        {
 
            while (!sr.EndOfStream)
            {
                string source = sr.ReadLine();
                string result = source;             // sometimes it changes, sometimes not
 
                // Separated in columns into an array of strings
                string[] columns = source.Split(new char[] { ',' });
 
                // Get value of fourth column
                string fourthCol = columns[3];   // first column idx=0, but first col is empty in ex.
                int curval = 0;
                Int32.TryParse(fourthCol, out curval);  // save way if empty
 
 
                if (curval >= 1)
                {
                    int newsum = curval - 1;
 
                    // replace the fourth column with a new value
                    columns[3] = newsum.ToString(); ;
 
                    // Combine the result back into one string, comma separated, now containing the change
                    result = String.Join(",", columns);
 
                }
                // write result
                sw.WriteLine(result);
            }
            // we have safely written now. Yet we should rename the .new file:
            // first close the files, otherwise rename will fail
            sr.Close();
            sw.Close();
 
            // now remove the old and rename the new. This is one of the
            // safest methods for dealing with files
            File.Delete(FILENAME);
            File.Move(FILENAME + ".new", FILENAME);
 
        }
    }
}

Open in new window

0
 
LVL 39

Expert Comment

by:abel
ID: 23731907
Oops, change the line

     private void btnQ24157837_Click(object sender, EventArgs e)

into the line you used for your function. I.e.:

     public void replaceLines()

0
 
LVL 1

Author Comment

by:AVONFRS
ID: 23732080
How can i modify the code, because currently it only reads the first line, and the line i need may not be on the first line.

Im confused.

Attached is all the code for the function but now im confused, because at first it searches through the text file and finds the line i need. Attaches the line to the document string.

But then with the new code, it goes and reads the document again but only the first line.

Help!
StreamReader reader = new StreamReader(@"\Program Files\SmartDeviceProject1\products.txt");
 
            string document;
            string searchval = textBox1.Text;
            int FirstChr;
                      
            while ((document = reader.ReadLine()) != null)
            {
                FirstChr = document.IndexOf(searchval);
                if (FirstChr == -1)
                {
                    MessageBox.Show("Boo");
                }
                else
                {
 
                    const string FILENAME = (@"\Program Files\SmartDeviceProject1\products.txt");
 
                    if (tabno == 0)
                    {
                        // The source data
                        using (StreamReader sr = new StreamReader(FILENAME))
                        {
                            string source = sr.ReadLine();
 
                            // Separated in columns into an array of strings
                            string[] columns = source.Split(new char[] { ',' });
 
                            // do something with contents of fourthCol
                            string fourthCol = columns[3];   // first column idx=0, but first col is empty in ex.
 
                            int curval = Convert.ToInt32(fourthCol);
 
                            if (curval >= 0)
                            {
                                int newsum = curval + 1;
 
                                // replace the fourth column with a new value
                                columns[3] = newsum.ToString(); ;
 
                                // Combine the result back into one string, comma separated, now containing the change
                                string result = String.Join(",", columns);
 
                                // open file for writing (other name)
                                using (StreamWriter sw = new StreamWriter(FILENAME + ".new"))
                                {
                                    sw.WriteLine(result);
                                    while (!sr.EndOfStream)
                                    {
                                        sw.WriteLine(sr.ReadLine());
                                    }
 
                                    // we have safely written now. Yet we should rename the .new file:
                                    // first close the files, otherwise rename will fail
                                    sr.Close();
                                    sw.Close();
 
                                    // now remove the old and rename the new. This is one of the
                                    // safest methods for dealing with files
                                    File.Delete(FILENAME);
                                    File.Move(FILENAME + ".new", FILENAME);
                                }
                            }
                        }                            

Open in new window

0
 
LVL 39

Expert Comment

by:abel
ID: 23732101
I just pasted in new code for you, maybe you missed it (we were crossing posts). It does what you ask: it reads all lines and changes all lines (which are part of the condition) and saves all lines.
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 23732227
Getting the 'int' does not contain a definition for 'TryParse' error message on this line

Int32.TryParse(fourthCol, out curval);
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 23732975
I have sorted the above problem.

But the write section, is increasing the value by 1 on every single row, i only need it to update on one row. The row which is in the 'document' string.
0
 
LVL 39

Expert Comment

by:abel
ID: 23733019
> The row which is in the 'document' string.

Then add that to your comparison. Now you are going through all rows and when the curval >= 1 you update it. Do something like this:

if(curval >= 1 && source == document)
{
    ....



0
 
LVL 39

Expert Comment

by:abel
ID: 23733037
> Getting the 'int' does not contain a definition for 'TryParse' error message on this line

I don't understand that, it doesn't seem like an official error to me, what is the exact text? Make sure you use Int32.TryParse as a class method.

Ah, you solved it. Good! :)
0
 
LVL 1

Author Closing Comment

by:AVONFRS
ID: 31548735
Thank you very much for putting up with all of my questions and problems!
0
 
LVL 1

Author Comment

by:AVONFRS
ID: 23734170
That is all working great. Fantastic.

Just one small problem . I cant delete the file. I dont know if its because it is in use by the program, or if its because it is on a Pocket PC Device.

If i move the old file to a different folder, i cant run the functions again, because it thinks the old file is in use.
0
 
LVL 39

Expert Comment

by:abel
ID: 23734301
Glad it worked out for you :)

To find out who's holding a handle to your file, you can use the handle tools of SysInternals or the process monitor (use "find" to find the right handle). You can see the owner of the handle and you can either release the handle or do something else (like searching for the bug in the program that owns it but shouldn't): http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question