We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Reading CSV TXT C#

AVONFRS
AVONFRS asked
on
Medium Priority
1,307 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.
Comment
Watch Question

Top Expert 2009

Commented:
If it is comma delimited without quotes, an easy method is using regular expressions. Coming up..
masheikSoftware Engineer
CERTIFIED EXPERT

Commented:
Top Expert 2009

Commented:
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 --

Author

Commented:
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?

Author

Commented:
Also how do i retrieve that value (the one after desktop PC?)
Top Expert 2009

Commented:
> 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

Author

Commented:
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.
Top Expert 2009

Commented:
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?

Author

Commented:

// 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

Top Expert 2009

Commented:
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

Author

Commented:
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
Top Expert 2009

Commented:
> 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.
Top Expert 2009
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Top Expert 2009

Commented:
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()

Author

Commented:
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

Top Expert 2009

Commented:
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.

Author

Commented:
Getting the 'int' does not contain a definition for 'TryParse' error message on this line

Int32.TryParse(fourthCol, out curval);

Author

Commented:
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.
Top Expert 2009

Commented:
> 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)
{
    ....



Top Expert 2009

Commented:
> 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! :)

Author

Commented:
Thank you very much for putting up with all of my questions and problems!

Author

Commented:
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.
Top Expert 2009

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.