Solved

Downloading CSV file from webservice

Posted on 2012-04-12
5
1,810 Views
Last Modified: 2012-04-21
I have been asked by a client to access information from a web service.

They have given me the following access code

WebRequest   request = WebRequest.Create( 	"http://www.odata.charities.govt.nz/GrpOrgLatestReturns?$filter=Donationskoha 	gt 1000000&$format=csv"); 
WebResponse   Response = request.GetResponse();

Open in new window


What is the best way to get this information into a datatable so I can display this in a datagrid?

Do I have to loop through each line and split the comma separated values?

How do I save this as a CSV file?
0
Comment
Question by:elmbrook
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 9

Expert Comment

by:wasiftoor
ID: 37840487
Just paste the URL in your browser. Save the text as .csv and open in excel.

http://www.odata.charities.govt.nz/GrpOrgLatestReturns?$filter=Donationskoha       gt 1000000&$format=csv


I just did that. File attached for reference.
GrpOrgLatestReturns.csv
0
 

Author Comment

by:elmbrook
ID: 37840506
I should have been more detailed.

I need to run this from a windows application where the user can set a number of filters to download the information.

I then need to display this in a datagridview so the user can see what they have downloaded.
0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 total points
ID: 37841290
How do I save this as a CSV file?
No, you would use a Streamreader to pull the data. You can browse for many examples on how to pull data from a webservice.

Do I have to loop through each line and split the comma separated values?
Yes you would.
However, the "dirty" work has been done by others. Indeed, this article by Sebastien Lorion I have bookmarked for the purpose:
A Fast CSV Reader

Note that he claims:
Basic data-binding is possible via the System.Data.IDataReader interface implemented by the reader.
I haven't tested this myself and would be interested to learn about your findings.

/gustav
0
 

Accepted Solution

by:
elmbrook earned 0 total points
ID: 37853806
Hi Cactus

I decided to use the oledb Jet Driver as it was an easy solutions.

Code is below.

Thanks for the help

 private void charities()
        {
            WebRequest request = WebRequest.Create("http://www.odata.charities.govt.nz/GrpOrgLatestReturns?$filter=Donationskoha 	gt 1000000&$format=csv");
            WebResponse Response = request.GetResponse();

            StreamReader str = new StreamReader(request.GetResponse().GetResponseStream());
            StreamWriter writer = new StreamWriter("c:\\temp\\tst1.txt", true);

            while (str.Peek() >= 0)
            {
                writer.WriteLine(str.ReadLine());

                //MessageBox.Show(str.ReadLine());

            }
            writer.Close();

            DataTable dt = GetDataTableFromCsv("c:\\temp\\tst1.txt", true);

            grd1.DataSource = dt;
          
        }

        private DataTable GetDataTableFromCsv(string path, bool isFirstRowHeader)
        {
            string header = isFirstRowHeader ? "Yes" : "No";

            string pathOnly = Path.GetDirectoryName(path);
            string fileName = Path.GetFileName(path);

            string sql = @"SELECT Name FROM [" + fileName + "]";

            using (OleDbConnection connection = new OleDbConnection(
                      @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly +
                      ";Extended Properties=\"Text;HDR=" + header + "\""))
            using (OleDbCommand command = new OleDbCommand(sql, connection))
            using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
            {
                DataTable dataTable = new DataTable();
                dataTable.Locale = CultureInfo.CurrentCulture;
                adapter.Fill(dataTable);
                return dataTable;
            }
        }

Open in new window

0
 

Author Closing Comment

by:elmbrook
ID: 37875112
Used another solution I found. Have to credit this articel http://stackoverflow.com/questions/1050112/how-to-read-a-csv-file-into-a-net-datatable
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question