{"id":4250,"date":"2018-06-30T03:02:59","date_gmt":"2018-06-30T03:02:59","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=4250"},"modified":"2024-02-06T12:11:43","modified_gmt":"2024-02-06T12:11:43","slug":"how-to-export-rest-api-to-csv","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/how-to-export-rest-api-to-csv\/","title":{"rendered":"How to export REST API to CSV using c# or Python"},"content":{"rendered":"

Introduction to export REST API to CSV<\/h2>\n

\"Logo<\/a>Export REST API to CSV is in some cases necessary to process the data because\u00a0many tools can handle CSV files. In this new article, we will show different ways\u00a0to export the data. The first example will do it using C#. The second example\u00a0with use Python. Let’s take a look at these examples.<\/p>\n

<\/h2>\n

Requirements to export REST API to CSV<\/h2>\n
    \n
  1. First of all, you will need\u00a0ZappySys ODBC PowerPack<\/a> installed.<\/li>\n
  2. Secondly, you will require Visual Studio installed for the C# example.<\/li>\n
  3. Finally, it is necessary Python installed for the Python example.<\/li>\n<\/ol>\n

    Export REST API to CSV using C#<\/h3>\n

    C# is a pretty popular programing language. In the first example, we will\u00a0show how to display the REST API information to a CSV file named. Let’s take a\u00a0look at the code:<\/p>\n

      \n
    1. First of all, we will connect to REST API using a connection to the\u00a0following Data Path:\n
      https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?$format=json\r\n<\/pre>\n
      using (OdbcConnection conn = \r\n      new OdbcConnection(\"Driver={ZappySys JSON Driver};DataPath='https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?$format=json';\"))\r\n<\/pre>\n<\/li>\n
    2. Secondly, we create a connection using a file stream. We will save the\u00a0results to a file in the c:\\sql\\sample.csv:\n
      FileStream fs = new FileStream(\"C:\\\\sql\\\\sample.txt\", FileMode.Create);\r\nStreamWriter writer = new StreamWriter(fs);\r\nStringBuilder output = new StringBuilder();<\/pre>\n<\/li>\n
    3. Also, we will add a query to REST API. Note that with the ZappySys ODBC\u00a0PowerPack, you can do a simple SQL query to get REST API data. This is\u00a0pretty simple and intuitive:\n
      OdbcCommand cmd = new OdbcCommand(\r\n     @\"SELECT  CustomerID,CompanyName FROM value\", conn);\r\n<\/pre>\n<\/li>\n
    4. In addition, we will read the data and close the connections:\n
      conn.Close();  \r\nwriter.Close(); \r\nfs.Close();<\/pre>\n<\/li>\n
    5. The complete code will be the following:\n
      using System;\r\nusing System.IO;\r\nusing System.Data.Odbc;\r\n\r\npublic class Program\r\n{\r\n    public static void Main()\r\n    {\r\n        var outpath = @\"C:\\temp\\sample.txt\";\r\n\r\n        using (var conn = new OdbcConnection(\"Driver={ZappySys JSON Driver};DataPath='https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?$format=json';\"))\r\n        {\r\n            conn.Open();\r\n            var cmd = new OdbcCommand(@\"SELECT CustomerID,CompanyName FROM $ WITH(Filter='$.value[*]')\", conn);\r\n            \r\n            \/\/Increases the timeout duration from the default 30 seconds, which may be insufficient in certain scenarios.\r\n            cmd.CommandTimeout=600; \/\/ 600-seconds\r\n            \r\n            var rdr = cmd.ExecuteReader();\r\n\r\n            using (var fs = new FileStream(outpath, FileMode.Create))\r\n            {\r\n                using (var writer = new StreamWriter(fs))\r\n                {\r\n                    \/\/write file header\r\n                    writer.WriteLine(\"CustomerID,CompanyName\");\r\n\r\n                    while (rdr.Read())\r\n                    {\r\n                        \/\/write file row\r\n                        writer.WriteLine(\"{0},{1}\", rdr[\"CustomerID\"], rdr[\"CompanyName\"]);\r\n                    }\r\n                    conn.Close(); \/\/close connection\r\n                    writer.Close();\r\n                    fs.Close();\r\n                }\r\n            }\r\n        }\r\n\r\n        \/\/Read from file and display the content\r\n        Console.Write(File.ReadAllText(outpath));\r\n\r\n        Console.WriteLine(\"\\r\\n===== Press any key to end the program =====\\r\\n\");\r\n        Console.Read();\r\n    }\r\n}<\/pre>\n
       <\/li>\n
    6. Finally, you will be able to see the file created:\n
      \"Export<\/a>
      REST API into CSV<\/p><\/div><\/li>\n<\/ol>\n
      Export REST API to CSV using Python<\/h3>\n
      Python is another really popular programming language. The popularity is\u00a0growing a lot. In this example, we will learn how to Export REST API to CSV\u00a0using Python.<\/p>\n
        \n
      1. First of all, you will need to install Pip if not included in Python. Pip is Package Installer.
        \nFor instructions about the installation, refer to\u00a0        this link.<\/a><\/li>\n
      2. Secondly, you will also need the pyodbc. The pyodbc allows connecting to\u00a0ODBC using Python. To install it go to the scripts folder of Python where\u00a0Python is installed and run this command:pip install pyodbc.<\/li>\n
      3. Once that pyodbc is installed, we will run the following code:Full Code<\/strong>\n
        import csv\r\nimport pyodbc\r\n\r\nconn = pyodbc.connect(\r\n    r'DRIVER={ZappySys JSON Driver};'\r\n    )\r\ncursor = conn.cursor()\t\r\nrows = cursor.execute(\"SELECT CustomerID,CompanyName FROM value WHERE COUNTRY='Germany' WITH (SRC='https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?$format=json')\") \r\nwith open(r'C:\\sql\\cus2.csv', 'w', newline='') as csvfile:\r\n    writer = csv.writer(csvfile)\r\n    writer.writerow([x[0] for x in cursor.description])  \r\n    for row in rows:\r\n        writer.writerow(row)<\/pre>\n<\/li>\n
      4. Now lets’s understand parts of above code. We have used the csv and pyodbc modules in the code:\n
        import csv\r\nimport pyodbc<\/pre>\n<\/li>\n
      5. Also, we connect to the ZappySys ODBC Driver<\/a>:\n
        conn = pyodbc.connect(\r\nr'DRIVER={ZappySys JSON Driver};'\r\n)<\/pre>\n<\/li>\n
      6. In addition,\u00a0we have used a cursor to get the rows and send a SQL query to get data\u00a0from the REST API:\n
        cursor = conn.cursor() \r\nrows = cursor.execute(\"SELECT CustomerID,CompanyName FROM value \r\nWHERE Country='Germany' WITH \r\n(SRC='https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?$format=json')\")<\/pre>\n<\/li>\n
      7. Following code is to open the CSV file stream:\n
        with open(r'C:\\sql\\customer.csv', 'w', newline='') as csvfile:<\/pre>\n<\/li>\n
      8. Finally, we will write the data from REST API into the CSV file:\n
        writer = csv.writer(csvfile)\r\nwriter.writerow([x[0] for x in cursor.description]) \r\nfor row in rows:\r\nwriter.writerow(row)<\/pre>\n<\/li>\n
      9. To conclude, if everything is OK, you will be able to see the created CSV file:\n
        \"REST<\/a>
        Python code REST API<\/p><\/div>\n
        <\/div>\n<\/li>\n<\/ol>\n
        <\/h2>\n
        Using ODBC DSN in Connection String<\/h2>\n
        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.<\/p>\n
        Configure DSN for REST API Connection<\/h3>\n
          \n
        1. First of all, we will access the following URL:\n
          https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Orders?$format=json<\/pre>\n<\/li>\n
        2. Secondly, in the windows start menu, Search for \u201cODBC\u201d open the ODBC Data Sources.<\/li>\n
        3. Also, in the ODBC Administrator, press Add and select the ZappySys JSON
          \nDriver:<\/p>\n
        4. Finally, specify the URL of step 1 and save the configuration:\n
          \"REST<\/a>
          URL ODBC<\/p><\/div><\/li>\n<\/ol>\n
          Using ODBC DSN in C# Code<\/h3>\n
          Now to use ODBC DSN you created simply change our previous C# Code as below (Just one line)<\/p>\n
          using (var conn = new OdbcConnection(\"DSN=Your-DSN-Name-Goes-Here\"))\r\n{\r\n    conn.Open();\r\n    ...........\r\n    ...........\r\n    ...........\r\n<\/pre>\n
          \"Using<\/a>
          Using ODBC DSN in C# code to call REST API<\/p><\/div>\n
          Using ODBC DSN in Python Code<\/h3>\n
          Now to use ODBC DSN you created simply change our previous C# Code as below (Just one line)<\/p>\n
          conn = pyodbc.connect(\r\n    r'DSN=Your-DSN-name-Goes-Here;'\r\n    )\r\n\t\r\n\t...........\r\n\t...........\r\n\t...........<\/pre>\n
           <\/p>\n
          ZappySys JSON \/REST API Driver Query Examples<\/span><\/h2>\n
          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\u00a0see json query examples<\/a>.<\/p>\n
          ZappySys\u00a0XML \/ SOAP Driver Query Examples<\/span><\/h2>\n
          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\u00a0see xml query examples<\/a>.<\/p>\n
          <\/h2>\n
          Conclusion<\/h2>\n
          To conclude,\u00a0in this article, we show how to access REST API using C# and Python. We\u00a0used the ZappySys ODBC PowerPack that allows accessing to REST API data and JSON\u00a0files using SQL queries. It is possible to create simple SQL queries and\u00a0access the data. It is also possible to access to XML files and Web API with\u00a0this tool. If you liked this tool you can test the\u00a0ZappySys ODBC PowerPack here<\/a>.<\/p>\n
          References<\/h2>\n
          Finally, if you want to read more about this topic, refer to these links:<\/p>\n