1

I have a list that I generated with a text file. I'm going through the list and using StringBuilder so I can use the StringBuilder object to create a csv file. The file has a ^ when its starting on a new record. I need to be able to append a new line to the stringbuilder object when the List returns a string that has the ^ in it, but as you can see with my code below, I can never tell when to append the new line and I don't know how to fix it.

Here is one of the ways I tried and the issue shows in the comments

foreach (var a in lstData)
        {
            if (a.Contains("^"))
            {
                string strName = Regex.Replace(a, "<.*?>", string.Empty).Remove(0, 1);

                sbQ.Append(strName + ",");
            }
            else
            {
                sbQ.Append(a + ",");
            }
            if (a.Contains("^")) sbQ.AppendLine();

            // Doesn't work, always appendlines when it encounters '^'
            // should only append new line on next occurence

        }

and here is the other way.

        string strZ;
        string strX;
        foreach (var a in lstData)
        {
            if (a.Contains("^"))
            {
                strZ = Regex.Replace(a, "<.*?>", string.Empty).Remove(0, 1);
                sbQ.Append(strZ + ",");
                strX = strZ;
            }

            if (strZ == strX)
            {
                // Causes Error : Use of Unassigned local variable 'strX'
            }
        }

I've tried using a do/while statement by iterating through a copy of the List and didn't work either.

EDIT

The data in the text file looks like this..

^<a class= ........>Name</a>
value1
value2
value3
value4
etc....
^<a class=......>Name</a>
value1
value2
value3
value4
etc....
^<a class=......>Name</a>
etc...

What I am trying to accomplish

name, value1, value2, value3, value4, etc...
name, value1, value2, value3, value4, etc...

EDIT Results in csv.

enter image description here

Where you see the link, that had a ^ before it and when that gets reached then it should move on to line 2, then when it hits the ^ again to line 3 and so forth. Its a new record when it hits the ^

This is how it should look...

What I am trying to get it to look like

EDIT/UPDATE

Physician Name, (CPSO#)
Primary Practice Location
Disciplinary Info  &amp; Restrictions
^Aal Ali, Saleh Saif Salem A S Fares (#82358)
P O Box: 8313
Abu Dhabi 0000
United Arab Emirates
Phone: +971506117644
^Aalders, Ryan Francis (#103559)
Kingston General Hospital
Department of Family Medicine
76 Stuart Street
Kingston ON  K7L 2V7
Phone: (613) 533-9300
^Aarabi, Mehdi (#81281)
UHN Toronto Western Hospital
2nd Floor East Wing
399 Bathurst Street
Toronto ON  M5T 2S8
Phone: (416) 603-5641
^Aaron, Shawn David (#62311)
Ottawa General Hospital
501 Smyth Road
Ottawa ON  K1H 8L6
Phone: (613) 737-8899 Ext. 74729
Fax: (613) 739-6807
Improve this question
12
  • What is lstData? Is it the string that contains a,b^c,d? You want to split on ^ and then ,? Are there any considerations for escape characters? Is what you want just lstData.Split('^').Select(x => x.Split(',')).ToArray()? i.e. return an array of string arrays where each array contains an array of the comma separated fields? Commented Sep 7, 2014 at 0:15
  • @KirkWoll, Sorry, yes lstData is the textfile data I put into a List<string>. The text file data isn't comma separated, each value is on a new line. I'm taking the data and adding it to a string builder and separating it. Commented Sep 7, 2014 at 0:19
  • So you just want x.Split(^)? Sorry, I'd post that as an answer, but I'm still not 100% of your requirements. Commented Sep 7, 2014 at 0:21
  • @KirkWoll, I'll edit the question and be more specific... Commented Sep 7, 2014 at 0:23
  • What's wrong with string.Join? Commented Sep 7, 2014 at 0:52

4 Answers 4

Reset to default
2

As an alternative you could use LINQ to build the list. See the following: Make CSV from list of string in LINQ

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I searched through a lot of links and missed that one
1

You can make a helper method that add the newline when it hits a carrot. File.ReadLine will make break on newline then we can just replace out the carrot in the helper method and do the data scrubbing you want. Then use String.Join to combine all the results back with a comma.

private static void Main(string[] args)
{
    var text = File.ReadLines(@"YourFile.txt");

    var lines = ParseList(text);

    using (var file = File.AppendText(@"NewFile.csv"))
    {
        file.Write(String.Join(",", lines));
    }

    Console.ReadLine();
}

private static IEnumerable<string> ParseList(IEnumerable<string> lines)
{
    foreach (var line in lines)
    {
        var newline = Regex.Replace(line, @"<.*?>", String.Empty);
        if (newline.StartsWith("^"))
        {
            // change carrot to newline
            newline = Environment.NewLine + newline.Remove(0, 1);
        }
        yield return newline;
    }
}
Improve this answer

Comments

0

This code works, and will deal with ^'s appearing in the data as well (other than as the first character of a line)

var text = File.ReadAllText("filename.txt");
using(var file = File.AppendText("NewFile.txt"))
foreach (var csv in text.Split(new []{Environment.NewLine+"^", "\n^"}, StringSplitOptions.None)
    .Select(line => line.Split(new []{Environment.NewLine, "\n"}, StringSplitOptions.None))
    .Select(sublines => string.Join(",", sublines.Select(s => s.ToString()))))
{
    file.WriteLine(csv);
}

See this fiddle:

https://dotnetfiddle.net/uyHppL

And, here's a variation that doesn't use a foreach, which should be a little more efficient as it only writes to the file once.

var csv = string.Join(Environment.NewLine, text.Split(new []{Environment.NewLine+"^", "\n^"}, StringSplitOptions.None)
    .Select(line => line.Split(new []{Environment.NewLine, "\n"}, StringSplitOptions.None))
    .Select(sublines => string.Join(",", sublines.Select(s => s.ToString()))));

using(var file = File.AppendText("NewFile.txt"))
    file.WriteLine(csv);
Improve this answer

12 Comments

That was close, it is separating it at the ^, but then below it is the values and not appearing like the pic that I added to get it to look like, I know it wasn't tested :)
@Chris - What? I don't understand what you mean
@Chris - Since you aren't showing us what the actual data is, we can only guess, and you will have to make any changes that are different from what our guess would be. My code assumes each line ends in a \r\n, if it's only \n then it needs to change to that. Show us the ACTUAL csv in text form (not data in a spreadsheet) and confirm the line ending type.
I did show what the data looks like, its in the edits above. Noctis and emodendroket comments and solutions, put it to a csv file (its in the first pic where you see everything on one line), the 2nd pic is how I am trying to get it to look.
@Chris - you also have a problem of an embedded comma in your first column. So you'll have to put quotes around that column for Excel to load it into a single cell.
|
0

This seems to work : 

List<string> list = 
    new List<string>{"^one","two","^three","four","f1","f2","^five","six","s1","s2"};

StringBuilder sb = new StringBuilder();
foreach (var element in list)
{
    if (element.Contains("^")) {
        sb.Append("\r\n" + element.Replace("^",""));
    }
    else  {
        sb.Append("," + element);
    }
}
var result = sb.ToString();

It outputs:

one,two
three,four,f1,f2
five,six,s1,s2

You might want to have your html parsing in there

Improve this answer

3 Comments

When I run this, and have the result saved to a csv file, all the data is separated into cells but its all on line 1, and moving on two line 2, line 3, etc...when it hits the next ^
I'll edit and add a pic of the results when I open it...The ^ is a delimiter and when that gets hit then that's the start of a new record and should be on line 2, line 3 etc..one second
I just added a new pic of how I am trying to get it to look

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.