how to read a csv file from a url?

Question

Im trying to create a web service which gets to a URL e.g. www.domain.co.uk/prices.csv and then reads the csv file. Is this possible and how? Ideally without downloading the csv file?

Deucalion

Without downloading the CSV file? How do you expect to read it? Or do you just mean read a portion of it without having to download the whole thing first. – Contredanse

Do you want to do that client side or server side? – Maltese

Downloading doesn't mean you have to save the file to disk if that is what you think. That being said, you can't read the file without downloading it. – Engracia

Answer 1

You could use:

public string GetCSV(string url)
{
    HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url);
    HttpWebResponse resp = (HttpWebResponse)req.GetResponse();

    StreamReader sr = new StreamReader(resp.GetResponseStream());
    string results = sr.ReadToEnd();
    sr.Close();

    return results;
} 

And then to split it:

public static void SplitCSV()
{
    List<string> splitted = new List<string>();
    string fileList = getCSV("http://www.google.com");
    string[] tempStr;

    tempStr = fileList.Split(',');

    foreach (string item in tempStr)
    {
        if (!string.IsNullOrWhiteSpace(item))
        {
            splitted.Add(item);
        }
    }
}

Though there are plenty of CSV parsers out there and i would advise against rolling your own. FileHelpers is a good one.

Sapling

If you're reading the whole file into a string anyway and do not need the additional flexibility of WebRequest you can just use WebClient.DownloadString. – Razzledazzle

If you use VisualBasic.IO.TextFieldParser you can use something that's already built into .NET by just adding a reference to Microsoft.VisualBasic. After that use this instead of the stream reader. using (TextFieldParser parser = new TextFieldParser(response.GetResponseStream())) – Sonnier

Answer 2

In your Web Service you could use the WebClient class to download the file, something like this ( I have not put any exception handling, not any using or Close/Dispose calls, just wanted to give the idea you can use and refine/improve... )

using System.Net;

WebClient webClient = new WebClient();
webClient.DownloadFile("http://www.domain.co.uk/prices.csv");

then you can do anything you like with it once the file content is available in the execution flow of your service.

if you have to return it to the client as return value of the web service call you can either return a DataSet or any other data structure you prefer.

Lefthand

hmmmm so if i wanted to put the csv into a datatable, would i do datatable table = webClient – Deucalion

Answer 3

Sebastien Lorion's CSV Reader has a constructor that takes a Stream.

If you decided to use this, your example would become:

void GetCSVFromRemoteUrl(string url)
{
    HttpWebRequest request = WebRequest.Create(url) as HttpWebRequest;
    HttpWebResponse response = request.GetResponse() as HttpWebResponse;

    using (CsvReader csvReader = new CsvReader(response.GetResponseStream(), true))
    {
        int fieldCount = csvReader.FieldCount;
        string[] headers = csvReader.GetFieldHeaders();

        while (csvReader.ReadNextRecord())
        {
            //Do work with CSV file data here
        }
    }

}

The ever popular FileHelpers also allows you to read directly from a stream.

Giacobo

Answer 4

The documentation for WebRequest has an example that uses streams. Using a stream allows you to parse the document without storing it all in memory

Maltese