Convert MRN news JSON content to CSV

lim.yongyik

I am planning to download MRN news via Refinitiv SFTP. i need to convert it into CSV. Can you provide a sample using Python on how to do this?

raksina.samasiri

Hi @lim.yongyik ,

Have you tried looking at this question in the Stack Overflow, is this what you're looking for? https://stackoverflow.com/questions/1871524/how-can-i-convert-json-to-csv

valentin.tassel

Hi @lim.yongyik you can try this script:

SFTP script to download files in the MRN archive

import paramiko

import json

import spicy

# SFTP connection information

hostname = 'archive.news.refinitiv.com'

port = 22

username = [YOUR MACHINE ID]

password = [YOUR PASSWORD]

# Establish an SSH transport

transport = paramiko.Transport((hostname, port))

transport.connect(username=username, password=password)

# Create an SFTP client

sftp = paramiko.SFTPClient.from_transport(transport)

# List files in a remote directory

remote_directory = '/News/RTRS/Daily/JSON'

file_list = sftp.listdir(remote_directory)

print("Remote directory contents:", file_list)

# Download a file from the remote directory

remote_filename = 'STORY.RTRS.2023-08-27.REC.JSON.txt.gz' (This is an example)

local_filename = r'[YOUR LOCAL PATH]'.format(remote_filename)

sftp.get(f"{remote_directory}/{remote_filename}", local_filename)

print(f"Downloaded {remote_filename} as {local_filename}")

# Close the SFTP connection

sftp.close()

# Close the SSH transport

transport.close()

To unzip the gz archive

import gzip

import shutil

with gzip.open(local_filename, 'rb') as f_in:

with open(local_filename[:-3], 'wb') as f_out:

shutil.copyfileobj(f_in, f_out)

lim.yongyik

thank you @valentin.tassel do you have any sample python code to transform the MRN news JSON content into CSV?