Write different tab or files separated in .csv or Excel file when using StreamWriter in C#

Question

I have a console application that outputs the result into a .csv file. The console application calls several web services and outputs several data. The output contain URLs which differentiate one site from another other. The code works fine, but the output is in one big CSV file and it gets divided when it reaches the header then starts writing the data from the new site.

Here you have how the output is now from the CSV file:

ProjectTitle,PublishStatus,Type,NumberOfUsers, URL
Project one,published,Open,1,http://localhost/test1
Project two,expired,Closed,14,http://localhost/test1

ProjectTitle,PublishStatus,Type,NumberOfUsers,URL
Project one V2,expired,Closed,2,http://localhost/test2
Project two V2,Published,Open,3,http://localhost/test2

What I am trying to do its to either output the first set of data (depending on each URL) and then the second set of data in a different tab in Excel, or just create a new file for the new set of data. My code:

public static XmlDocument xml = new XmlDocument();

static void Main(string[] args)
{
    xml.Load("config.xml");
    test();            
}

private static void test()
{

List<string> url = new List<string>();
int count = xml.GetElementsByTagName("url").Count;

for (int i = 0; i < count; ++i)
{
    url.Add(xml.GetElementsByTagName("url")[i].InnerText);

    Url = url[i];               

}

string listFile = "ListOfSites";
string outCsvFile = string.Format(@"C:\\testFile\\{0}.csv", testFile + DateTime.Now.ToString("_yyyyMMdd HHmms"));


using (FileStream fs = new FileStream(outCsvFile, FileMode.Append, FileAccess.Write))                    
using (StreamWriter file = new StreamWriter(fs))

file.WriteLine("ProjectTitle,PublishStatus,Type,NumberOfSUsers,URL");
    foreach(WS.ProjectData proj in pr.Distinct(new ProjectEqualityComparer()))
        {
            file.WriteLine("{0},\"{1}\",{2},{3},{4}",
            proj.ProjectTitle,                               
            proj.PublishStatus,                               
            proj.type,
            proj.userIDs.Length.ToString(NumberFormatInfo.InvariantInfo),
            url[i].ToString());
        }
}

I don't think csv files can contain multiple tabs: http://stackoverflow.com/questions/9131744/is-it-possible-to-create-csv-file-with-multiple-tabs-in-command-prompt — Neal, Sep 21 '15 at 15:59
Yes you can write different files. Just use your existing code but change the file name and a new file will be created. — Neal, Sep 21 '15 at 16:02
@Neal, thanks for the suggestion, can you please help me with this? — A arancibia, Sep 22 '15 at 19:03
I'm not sure I understand how your code is supposed to work. How does the url list relate to pr? — Neal, Sep 23 '15 at 09:17
@Neal, its listed according to the projectTitle, and its shows the complete URL for that project. — A arancibia, Sep 23 '15 at 14:11
I can't really add anything that isn't in Glenn Ferrie's answer. Did you definitely put the curly braces in after the using statements, as they are in his answer? Without the curly braces using() will only apply to the first line after it. — Neal, Sep 23 '15 at 14:30

Glenn Ferrie · Answer 1 · 2015-09-21T16:55:30.567

This is a quick fix. There may be a more elegant way to accomplish this as well:

public static XmlDocument xml = new XmlDocument();

static void Main(string[] args)
{
    xml.Load("config.xml");

    // relocated from `test` method to `Main`
    List<string> url = new List<string>();
    int count = xml.GetElementsByTagName("url").Count;

    for (int i = 0; i < count; ++i)
    {
        url.Add(xml.GetElementsByTagName("url")[i].InnerText);

        Url = url[i];               
        test(Url);
    }

}

private static void test(string url)
{
    string listFile = "ListOfSites";
    string outCsvFile = string.Format(@"C:\\testFile\\{0}.csv", testFile + DateTime.Now.ToString("_yyyyMMdd HHmms"));


    using (FileStream fs = new FileStream(outCsvFile, FileMode.Append, FileAccess.Write))           
    {
        using (StreamWriter file = new StreamWriter(fs))
        {
            file.WriteLine("ProjectTitle,PublishStatus,Type,NumberOfSUsers,URL");
        foreach(WS.ProjectData proj in pr.Distinct(new ProjectEqualityComparer()))
            {
                file.WriteLine("{0},\"{1}\",{2},{3},{4}",
                proj.ProjectTitle,                               
                proj.PublishStatus,                               
                proj.type,
                proj.userIDs.Length.ToString(NumberFormatInfo.InvariantInfo),
                url);
            }
        }
    }         
}

NOTE: Please use braces in C# even if you don't NEED them. It makes your code more readable.

thank you for your reply. I have made the changes and I am still getting the same 1 file with all the data on it. — A arancibia, Sep 21 '15 at 16:56
I suggest you change your file-naming pattern to include a GUID instead of the time stamp. your code is probably running quickly and overwriting the same file more than once. — Glenn Ferrie, Sep 21 '15 at 17:00
Hi, I need the time stamps because the file will be generated each day, so in way to track all the info in the file instead of overwriting — A arancibia, Sep 21 '15 at 17:30
I have made the suggested changes, but still creating one file and I realized that inside its only writing the headers without any information now. — A arancibia, Sep 22 '15 at 15:53

score 1 · Answer 2 · answered Sep 21 '15 at 16:06

If you want to write an Excel file with different worksheets you can try and use the EpPlus library, it is open source and free.

Here is the nuget page

To generate more files, you have to decide how to name them and if the records for each file are random in the source list, you can generate different lists of output strings for different output files and use the File.AppendAllLines(filename, collection); to save the data in the different files. I don't recommend you to open and close the files for each row to write because it is a time and resource consuming operation.

Write different tab or files separated in .csv or Excel file when using StreamWriter in C#

2 Answers2