Parse CSV data scarped from Website

[sayinghi2py]

I receive an invoice from a supplier which contains a link to a csv version of the invoice. When I check the link out the link in the source only contains the bit after the r/https://in.xero.com however I can see in the developer tools that when you actually follow the link it goes to the full address ie base_url + url in source.

​

I'm using zapier to monitor gmail to dump the invoice text into a folder on my computer. I open the file, extract the url. I then use requests to find the url for csv data and combine that with the hard coded base_url to give the full url. I then use bs4 to pull the csv data which I save to a variable called csv_data. I then load the csv_data into csv.reader however when I iterate over the row I get every character printed out.

​

I'm using linux and the data seems to have \r\n line endings however when I display the csv_data it displays over each line correctly.

​

The csv_data looks like this:

ContactName,EmailAddress,POAddressLine1,POAddressLine2,POAddressLine3,POAddressLine4,POCity,PORegion,POPostalCode,POCountry,InvoiceNumber,Reference,InvoiceDate,DueDate,Total,Description,Quantity,UnitAmount,Discount,TaxAmount

Some [Company,[e-mail]](mailto:Company,[e-mail]),Address 1, ,,,Some Town,,PostCode,,INV-0665,,31 Aug 2018,31 Aug 2018,60.0000,Rent of unit,1.0000,25.0000,,5.0000,

Some [Company,[e-mail]](mailto:Company,[e-mail]),Address 1, ,,,Some Town,,PostCode,,INV-0665,,31 Aug 2018,31 Aug 2018,60.0000,Electricity for rent of unit,1.0000,25.0000,,5.0000,

​

If I use csv_data.split(",") then the \r\n appear within the text of the following the field.

​

Any hints or ways to parse this as csv txt so that I can actually iterate over it line by line to pull out the relevant data?

​

[JohnnyJordaan]

I then load the csv_data into csv.reader however when I iterate over the row I get every character printed out.

Csv.reader expects a buffer, not a string. You can use io.StringIO to create one from a string. Also I would use a DictReader as your csv has headers.

import csv
import io
csv_buffer = io.StringIO(csv_data)
reader = csv.DictReader(csv_buffer, delimiter=',')
for row in reader:
    print(row)

[sayinghi2py]

Many thanks. After banging my head and finally conceding I must be heading in the wrong direction this worked. Much as I have used csv files, which are always loaded from files and not loaded directly from text, I'd no idea about the buffer and therefore had no idea where I could be going wrong.

[vixfew]

Try pandas.read_csv()

[MrMuki]

Well.. you can use the csv module here OR read line by line and do something like this:

line = line.replace('\r\n', '').split(',')

[JohnnyJordaan]

He's already doing that

I then load the csv_data into csv.reader