Solved

Downloading CSV file from webservice

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 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