pysftp read csv file has an etra quote

Question

I am trying to read a csv file into an array however when I read it I get this extra quote instead of a row of values.

    with pysftp.Connection(host, username=username, password=pwd) as sftp:
        with sftp.cd('/DailyFiles'):
            files = sftp.listdir()
            for f in files:
                if f.startswith('POS_ROBO_'):
                    with sftp.open(f, 'r') as fl:
                    # csv_file = sftp.open(f)
                    # csvreal = pd.read_csv(csv_file)

                    # Data from SSH is binary, we need to decode to string
                        content = [line for line in fl]
                        break

Here is what is what content looks like:

['"Date","AccountVendor","AccountNumber","AccountType","BalanceType","SecurityType","SecuritySubType","ShortClass","LongClass","Symbol","CUSIP","Description","QTY","Price","Amount"\r\n', '"2021-07-01","???TD","942735452","1","SE","C","","Frgn Stk","Foreign Stock","SPDW","TD:17:","SPDR INDEX SHARES FUNDS PORTFO LIO DEVLPD ETF","11.00","36.91","406.01"\r\n']

It looks like you have two lines of data, a header and values. Do you want the header to be part of the array or just the data? — tdelaney, Aug 12 '21 at 14:08

score 2 · Answer 1 · answered Aug 12 '21 at 14:19

This is just a standard CSV file of two lines. Python is using the outer ' character to denote the strings and the inner " are part of the data. They are the standard way that a CSV file quotes a single cell so that a comma in a cell is not confused with a comma used as the cell separator. You can use csv to convert to an array

import csv

content = [
    '"Date","AccountVendor","AccountNumber","AccountType","BalanceType","SecurityType","SecuritySubType","ShortClass","LongClass","Symbol","CUSIP","Description","QTY","Price","Amount"\r\n',
    '"2021-07-01","???TD","942735452","1","SE","C","","Frgn Stk","Foreign Stock","SPDW","TD:17:","SPDR INDEX SHARES FUNDS PORTFO LIO DEVLPD ETF","11.00","36.91","406.01"\r\n']

array = csv.reader(content)

for row in array:
    print(row)

Result

['Date', 'AccountVendor', 'AccountNumber', 'AccountType', 'BalanceType', 'SecurityType', 'SecuritySubType', 'ShortClass', 'LongClass', 'Symbol', 'CUSIP', 'Description', 'QTY', 'Price', 'Amount']
['2021-07-01', '???TD', '942735452', '1', 'SE', 'C', '', 'Frgn Stk', 'Foreign Stock', 'SPDW', 'TD:17:', 'SPDR INDEX SHARES FUNDS PORTFO LIO DEVLPD ETF', '11.00', '36.91', '406.01']

What's wrong with using "csv reader"? You need it, if you want to read a *CSV* file. Use it like `csv.reader(fl)`. Your question has nothing to with with pysftp. You would get the same result, were you reading a local file the same way. — Martin Prikryl, Aug 12 '21 at 14:30
I didn't down vote, so not sure. You don't want to use `csv.reader`? Then this is definately not the answer! — tdelaney, Aug 12 '21 at 14:33
I wrote this answer without the ftp code to keep it generic. I would also be reasonable to skip building `content` at all and doing `array = list(csv.reader(fl))`. — tdelaney, Aug 12 '21 at 14:35

pysftp read csv file has an etra quote

1 Answers1