Question 103418 in Azure Question posted in
The official documentation to get you started can be found here.

CSV data format goes wrong while exporting it in an HTTP response with Azure function

I am trying to export csv data in an HTTP response and to achieve this I am using Azure function. Now the certain CSV cell values has comma(,) in information and when the CSV is exported the data is completely messed up i.e., the commas in information are also considered and information after that is considered as another data item . Can anyone help to fix this problem?

Here is snippet code

# convert to csv data
def get_csv_data(all_rows):
    result = ""
    keys = sorted(list(set([key for row in all_rows for key in row.keys()])))
    result += ",".join(keys) + "n"
    for row in all_rows:
        cur_data = [str(row[key]) if key in row else "" for key in keys]
        csv_row = ",".join(cur_data) + "n"
        result += csv_row
    return result

def main(req: func.HttpRequest) -> func.HttpResponse:
    all_rows = [
        {
            'Name': 'XYZ',
            'DOB': 'Feb 20, 1987',
            'Address': 'Hno: 12, Gautam Vihar, Truc'
        },
        {
            'Name': 'MNO',
            'DOB': 'Feb 20, 1987',
            'Address': 'Hno: 12, Gauri Kalash, Mlf'
        },
        {
            'Name': 'ABC',
            'DOB': 'Sep 10, 1997',
            'Address': 'Hno: 11, Ganesh Nagar, Elf'
        }
    ]
 
    logging.info('Python HTTP trigger function processed a request.')
    
    csv_data = get_csv_data(all_rows)

    return func.HttpResponse(
            body = csv_data,
            status_code = 200,
            headers = {
                "Content-Disposition": f"attachement; filename={file_name}"
            }
    )

Please help to fix this issue.

Tags:

2

Answers


  1. 0

    Enclosing each field that you have converted to string using str(row[key]), within double quotes gave the desired result. The following is the complete function code that is working for me.

    import logging

import azure.functions as func

def get_csv_data(all_rows):
    result = ""
    keys = sorted(list(set([key for row in all_rows for key in row.keys()])))
    result += ",".join(keys) + "n"
    for row in all_rows:
        cur_data = [f'"{str(row[key])}"' if key in row else "" for key in keys]
        csv_row = ",".join(cur_data) + "n"
        result += csv_row
    return result

def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')

    all_rows = [
        {
            'Name': 'XYZ',
            'DOB': 'Feb 20, 1987',
            'Address': 'Hno: 12, Gautam Vihar, Truc'
        },
        {
            'Name': 'MNO',
            'DOB': 'Feb 20, 1987',
            'Address': 'Hno: 12, Gauri Kalash, Mlf'
        },
        {
            'Name': 'ABC',
            'DOB': 'Sep 10, 1997',
            'Address': 'Hno: 11, Ganesh Nagar, Elf'
        }
    ]

    
    csv_data = get_csv_data(all_rows)
 
    return func.HttpResponse(
            body = csv_data,
            status_code = 200,
            headers = {
                "Content-Disposition": f"attachement; filename=<Filename>.csv"            
                }
    )

    Results:

    enter image description here

    Login or Signup to reply.
  2. 0

    You don’t need to try and encode the CSV yourself, Python’s standard lib has the CSV module which will do this (correctly!) for you, and it can even take a list of dicts and convert the dict keys to fieldnames:

    import csv
import io

all_rows = [
    {"Name": "XYZ", "DOB": "Feb 20, 1987", "Address": "Hno: 12, Gautam Vihar, Truc"},
    {"Name": "MNO", "DOB": "Feb 20, 1987", "Address": "Hno: 12, Gauri Kalash, Mlf"},
    {"Name": "ABC", "DOB": "Sep 10, 1997", "Address": "Hno: 11, Ganesh Nagar, Elf"},
]

f = io.StringIO()
writer = csv.DictWriter(f, fieldnames=all_rows[0])
writer.writeheader()
writer.writerows(all_rows)

csv_str = f.getvalue()

print(csv_str)

...
    body=csv_str
    ...

    That print shows the correct CSV encoding. Fields with a comma in the data are "quoted" (to be interpreted as actual data, and not field delimiters):

    Name,DOB,Address
XYZ,"Feb 20, 1987","Hno: 12, Gautam Vihar, Truc"
MNO,"Feb 20, 1987","Hno: 12, Gauri Kalash, Mlf"
ABC,"Sep 10, 1997","Hno: 11, Ganesh Nagar, Elf"

    The DictWriter takes a file to write to (StringIO buffer in this case), and needs to know the field names. It can interpret the keys of a dict as the field names, so I passed the first row of your data). If your dict keys vary from row to row, you’ll need to select which dict you use to initialize the writer, and also consider the extrasaction= and restval= params.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search