Convert HTML table to CSV using Html Agility Pack

Question

Convert HTML table to CSV using Html Agility Pack

c# csv html-agility-pack linq

Question

I am wanting to exact all elements from a table with id = statsTable, and want all the data which I can then read into a csv.

Here is what I have so far:

// Create a request for the URL. 
WebRequest request = WebRequest.Create("http://www.pgatour.com/stats/stat.120.html");
Console.WriteLine("Requesting data from: http://www.pgatour.com/stats/stat.120.html");

// If required by the server, set the credentials.
request.Credentials = CredentialCache.DefaultCredentials;

WebResponse response = request.GetResponse();

using (Stream stream = response.GetResponseStream())
{
    StreamReader reader = new StreamReader(stream);

    // covert html to string
    String responseString = reader.ReadToEnd();

    HtmlDocument doc = new HtmlDocument();

    doc.LoadHtml(responseString);

    var desktopFolder = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
    var fullFileName = Path.Combine(desktopFolder, "GolfStats.csv");

    using (var PlayerFile = new StreamWriter(fullFileName))
    {
        PlayerFile.WriteLine("Data downloaded: " + DateTime.Now);

        var myTable = doc.DocumentNode
                        .Descendants("table")
                        .Where(table => table.Attributes.Contains("id"))
                        .SingleOrDefault(table => table.Attributes["id"].Value == "statsTable");

        var myTableValues = myTable.Descendants("td");

        foreach (var tdV in myTableValues)
        {
            PlayerFile.WriteLine(tdV.InnerText);
            Console.WriteLine(tdV.InnerText);
        }

        PlayerFile.Flush();
    }
}

The problem is my csv is simply listing the data in a single column, aswell as picking up an ad which is placed in the table (see url in the webRequest). If you can help me output the data in a table format this would be superb!

Matt D. Webb

View more on Stack Overflow

Accepted Answer

You create a new line for each table cell. To change it so that each table row has a seperate line replace

var myTableValues = myTable.Descendants("td");
foreach (var tdV in myTableValues)
{
    PlayerFile.WriteLine(tdV.InnerText);
    Console.WriteLine(tdV.InnerText);
}

with

var myTableRows = myTable.Descendants("tr").Where(tr => tr.Attributes.Contains("id"));
foreach (var tr in myTableRows)
{
    string line = string.Join(";", tr.Descendants("td").Select(td => td.InnerText));
    PlayerFile.WriteLine(line);
    Console.WriteLine(line);
}

The .Where(tr => tr.Attributes.Contains("id")) filters out the ad since the table row with the ad has no id while all player rows have.

Convert HTML table to CSV using Html Agility Pack

Question

Accepted Answer

Performance

Expression Evaluator

Related