How to export REST API to CSV using c# or Python

Contents hide

1 Introduction to export REST API to CSV

2 Requirements to export REST API to CSV

2.1 Export REST API to CSV using C#

2.2 Export REST API to CSV using Python

3 Using ODBC DSN in Connection String

3.1 Configure DSN for REST API Connection

3.2 Using ODBC DSN in C# Code

3.3 Using ODBC DSN in Python Code

4 ZappySys JSON /REST API Driver Query Examples

5 ZappySys XML / SOAP Driver Query Examples

6 Conclusion

7 References

7.1 More from ZappySys Blog

Introduction to export REST API to CSV

Export REST API to CSV is in some cases necessary to process the data because many tools can handle CSV files. In this new article, we will show different ways to export the data. The first example will do it using C#. The second example with use Python. Let’s take a look at these examples.

Requirements to export REST API to CSV

First of all, you will need ZappySys ODBC PowerPack installed.
Secondly, you will require Visual Studio installed for the C# example.
Finally, it is necessary Python installed for the Python example.

Export REST API to CSV using C#

C# is a pretty popular programing language. In the first example, we will show how to display the REST API information to a CSV file named. Let’s take a look at the code:

First of all, we will connect to REST API using a connection to the following Data Path:

https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json

1	https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json

using (OdbcConnection conn = 
      new OdbcConnection("Driver={ZappySys JSON Driver};DataPath='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json';"))

1 2	using (OdbcConnection conn = new OdbcConnection("Driver={ZappySys JSON Driver};DataPath='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json';"))

Secondly, we create a connection using a file stream. We will save the results to a file in the c:\sql\sample.csv:

C#

FileStream fs = new FileStream("C:\\sql\\sample.txt", FileMode.Create); StreamWriter writer = new StreamWriter(fs); StringBuilder output = new StringBuilder();

1
2
3

FileStream fs = new FileStream("C:\\sql\\sample.txt", FileMode.Create);
StreamWriter writer = new StreamWriter(fs);
StringBuilder output = new StringBuilder();
Also, we will add a query to REST API. Note that with the ZappySys ODBC PowerPack, you can do a simple SQL query to get REST API data. This is pretty simple and intuitive:

C#

OdbcCommand cmd = new OdbcCommand( @"SELECT CustomerID,CompanyName FROM value", conn);

1
2

OdbcCommand cmd = new OdbcCommand(
@"SELECT CustomerID,CompanyName FROM value", conn);
In addition, we will read the data and close the connections:

C#

conn.Close(); writer.Close(); fs.Close();

1
2
3

conn.Close();
writer.Close();
fs.Close();

The complete code will be the following:

using System;
using System.IO;
using System.Data.Odbc;

public class Program
{
    public static void Main()
    {
        var outpath = @"C:\temp\sample.txt";

        using (var conn = new OdbcConnection("Driver={ZappySys JSON Driver};DataPath='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json';"))
        {
            conn.Open();
            var cmd = new OdbcCommand(@"SELECT CustomerID,CompanyName FROM $ WITH(Filter='$.value[*]')", conn);
            
            //Increases the timeout duration from the default 30 seconds, which may be insufficient in certain scenarios.
            cmd.CommandTimeout=600; // 600-seconds
            
            var rdr = cmd.ExecuteReader();

            using (var fs = new FileStream(outpath, FileMode.Create))
            {
                using (var writer = new StreamWriter(fs))
                {
                    //write file header
                    writer.WriteLine("CustomerID,CompanyName");

                    while (rdr.Read())
                    {
                        //write file row
                        writer.WriteLine("{0},{1}", rdr["CustomerID"], rdr["CompanyName"]);
                    }
                    conn.Close(); //close connection
                    writer.Close();
                    fs.Close();
                }
            }
        }

        //Read from file and display the content
        Console.Write(File.ReadAllText(outpath));

        Console.WriteLine("\r\n===== Press any key to end the program =====\r\n");
        Console.Read();
    }
}

using System;

using System.IO;

using System.Data.Odbc;

public class Program

{

public static void Main()

{

var outpath = @"C:\temp\sample.txt";

using (var conn = new OdbcConnection("Driver={ZappySys JSON Driver};DataPath='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json';"))

{

conn.Open();

var cmd = new OdbcCommand(@"SELECT CustomerID,CompanyName FROM $ WITH(Filter='$.value[*]')", conn);

//Increases the timeout duration from the default 30 seconds, which may be insufficient in certain scenarios.

cmd.CommandTimeout=600; // 600-seconds

var rdr = cmd.ExecuteReader();

using (var fs = new FileStream(outpath, FileMode.Create))

{

using (var writer = new StreamWriter(fs))

{

//write file header

writer.WriteLine("CustomerID,CompanyName");

while (rdr.Read())

{

//write file row

writer.WriteLine("{0},{1}", rdr["CustomerID"], rdr["CompanyName"]);

}

conn.Close(); //close connection

writer.Close();

fs.Close();

}

//Read from file and display the content

Console.Write(File.ReadAllText(outpath));

Console.WriteLine("\r\n===== Press any key to end the program =====\r\n");

Console.Read();

}

Finally, you will be able to see the file created:
REST API into CSV

Export REST API to CSV using Python

Python is another really popular programming language. The popularity is growing a lot. In this example, we will learn how to Export REST API to CSV using Python.

First of all, you will need to install Pip if not included in Python. Pip is Package Installer.
For instructions about the installation, refer to this link.
Secondly, you will also need the pyodbc. The pyodbc allows connecting to ODBC using Python. To install it go to the scripts folder of Python where Python is installed and run this command:pip install pyodbc.

Once that pyodbc is installed, we will run the following code:Full Code

import csv
import pyodbc

conn = pyodbc.connect(
    r'DRIVER={ZappySys JSON Driver};'
    )
cursor = conn.cursor()	
rows = cursor.execute("SELECT CustomerID,CompanyName FROM value WHERE COUNTRY='Germany' WITH (SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json')") 
with open(r'C:\sql\cus2.csv', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow([x[0] for x in cursor.description])  
    for row in rows:
        writer.writerow(row)

import csv

import pyodbc

conn = pyodbc.connect(

r'DRIVER={ZappySys JSON Driver};'

)

cursor = conn.cursor()

rows = cursor.execute("SELECT CustomerID,CompanyName FROM value WHERE COUNTRY='Germany' WITH (SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json')")

with open(r'C:\sql\cus2.csv', 'w', newline='') as csvfile:

writer = csv.writer(csvfile)

writer.writerow([x[0] for x in cursor.description])

for row in rows:

writer.writerow(row)

Now lets’s understand parts of above code. We have used the csv and pyodbc modules in the code:

Python

import csv import pyodbc

1
2

import csv
import pyodbc
Also, we connect to the ZappySys ODBC Driver:

Python

conn = pyodbc.connect( r'DRIVER={ZappySys JSON Driver};' )

1
2
3

conn = pyodbc.connect(
r'DRIVER={ZappySys JSON Driver};'
)

In addition, we have used a cursor to get the rows and send a SQL query to get data from the REST API:

cursor = conn.cursor() 
rows = cursor.execute("SELECT CustomerID,CompanyName FROM value 
WHERE Country='Germany' WITH 
(SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json')")

cursor = conn.cursor()

rows = cursor.execute("SELECT CustomerID,CompanyName FROM value

WHERE Country='Germany' WITH

(SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json')")

Following code is to open the CSV file stream:

Python

with open(r'C:\sql\customer.csv', 'w', newline='') as csvfile:

1

with open(r'C:\sql\customer.csv', 'w', newline='') as csvfile:
Finally, we will write the data from REST API into the CSV file:

C#

writer = csv.writer(csvfile) writer.writerow([x[0] for x in cursor.description]) for row in rows: writer.writerow(row)

1
2
3
4

writer = csv.writer(csvfile)
writer.writerow([x[0] for x in cursor.description])
for row in rows:
writer.writerow(row)
To conclude, if everything is OK, you will be able to see the created CSV file:
Python code REST API

Using ODBC DSN in Connection String

So far we have seen DSN less connection string approach for ODBC Driver but now lets look at another way to use ODBC Driver in your C# or Python code. You can define many settings on DSN Datasource rather than setting in the ConnectionString.

Configure DSN for REST API Connection

First of all, we will access the following URL:

https://services.odata.org/V3/Northwind/Northwind.svc/Orders?$format=json

1

https://services.odata.org/V3/Northwind/Northwind.svc/Orders?$format=json
Secondly, in the windows start menu, Search for “ODBC” open the ODBC Data Sources.
Also, in the ODBC Administrator, press Add and select the ZappySys JSON
Driver:
Add ODBC JSON
Finally, specify the URL of step 1 and save the configuration:
URL ODBC

Using ODBC DSN in C# Code

Now to use ODBC DSN you created simply change our previous C# Code as below (Just one line)

using (var conn = new OdbcConnection("DSN=Your-DSN-Name-Goes-Here"))
{
    conn.Open();
    ...........
    ...........
    ...........

using (var conn = new OdbcConnection("DSN=Your-DSN-Name-Goes-Here"))

{

conn.Open();

...........

Using ODBC DSN in C# code to call REST API

Using ODBC DSN in Python Code

Now to use ODBC DSN you created simply change our previous C# Code as below (Just one line)

conn = pyodbc.connect(
    r'DSN=Your-DSN-name-Goes-Here;'
    )
	
	...........
	...........
	...........

conn = pyodbc.connect(

r'DSN=Your-DSN-name-Goes-Here;'

)

...........

ZappySys JSON /REST API Driver Query Examples

Reading from XML files or API can be done using the same way as previous sections except you have to use ZappySys XML Driver. Read help file here to see json query examples.

ZappySys XML / SOAP Driver Query Examples

Reading from XML files or API can be done using the same way as previous sections except you have to use ZappySys XML Driver. Read help file here to see xml query examples.

Conclusion

To conclude, in this article, we show how to access REST API using C# and Python. We used the ZappySys ODBC PowerPack that allows accessing to REST API data and JSON files using SQL queries. It is possible to create simple SQL queries and access the data. It is also possible to access to XML files and Web API with this tool. If you liked this tool you can test the ZappySys ODBC PowerPack here.

References

Finally, if you want to read more about this topic, refer to these links: