Exporting API output response to CSV

I am not an expert in Python but I used it to call data from an API. I got a code 200 and printed part of the data but I am not able to export/ save/ write the output (to CSV). Can anyone assist?

This is my code:

import requests
headers = {
    'Accept': 'text/csv',
    'Authorization': 'Bearer ...'
}

response = requests.get('https://feeds.preqin.com/api/investor/pe', headers=headers)
print response
output = response.content

And here is how the data (should be CSV, correct?) looks like: enter image description here

I managed to save it as txt but the output is not usable/ importable (e.g. to Excel). I used the following code:

text_file = open("output.txt", "w")
n = text_file.write(output)
text_file.close()

Thank you and best regards,

CodePudding user response：

Your content uses pipes | as a separator. CSVs use , commas (that's why they're called Comma Separated Values).

You can simply replace your data's pipes with commas. However, this may be problematic if the data you have itself uses commas.

output = response.content.replace("|", ",")

As comments have suggested, you could also use pandas:

import pandas as pd
from StringIO import StringIO

# Get your output normally...
output = response.content

df = pd.read_csv(StringIO(output), sep="|")

# Saving to .CSV
df.to_csv(r"C:\output.csv")

# Saving to .XLSX
df.to_excel(r"C:\output.xlsx")