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?
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?
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
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)
thank you @valentin.tassel do you have any sample python code to transform the MRN news JSON content into CSV?