?
Solved

Downloading CSV file from webservice

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

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
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.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 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