Solved

Downloading CSV file from webservice

Posted on 2012-04-12
5
1,659 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
  • 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 49

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

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.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

806 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