[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Downloading CSV file from webservice

Posted on 2012-04-12
5
Medium Priority
?
1,936 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 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 2000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

While working on Silverlight and WCF application, I faced one issue where fault exception occurred at WCF operation contract is not getting propagated to Silverlight client. So after searching net I came to know that it was behavior by default for s…
Here I am going to explain creating proxies at runtime for WCF Service. So basically we use to generate proxies using Add Service Reference and then giving the Url of the WCF service then generate proxy files at client side. Ok, what if something ge…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

649 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