formatting json output from lambda that reads csv from S3

Question

I have a lambda that reads a csv from a bucket:

import boto3
import csv
import json

BUCKET_NAME = 'olaptrader-products'
OBJECT_NAME = 'bats.csv'
LAMBDA_LOCAL_TMP_FILE = '/tmp/bats.csv'

def lambda_handler(event, context):
    s3 = boto3.client('s3')
    s3.download_file(BUCKET_NAME, OBJECT_NAME, LAMBDA_LOCAL_TMP_FILE)
    
    with open(LAMBDA_LOCAL_TMP_FILE,'r') as infile:
        reader = list(csv.reader(infile))
    
    return {
        'body': json.dumps(reader)
    }

The csv in my bucket looks like this:

Ticker  Exchange    Date        Open    High    Low    Close    Volume
6A      BATS        12/2/2021   0.9     0.95    0.83    0.95    1200
6B      BATS        12/3/2021   1       1.3     0.9     1.2     1500
6C      BATS        12/4/2021   1.2     1.3     1.1     1.1     1300

My output from the api gateway that calls this lambda looks like this:

[["Ticker", "Exchange", "Date", "Open", "High", "Low", "Close", "Volume"], ["6A", "BATS", "12/2/2021", "0.9", "0.95", "0.83", "0.95", "1200"], ["6B", "BATS", "12/3/2021", "1", "1.3", "0.9", "1.2", "1500"], ["6C", "BATS", "12/4/2021", "1.2", "1.3", "1.1", "1.1", "1300"]]

However, I want the output to look like this:

[{"Ticker":"6A","Exchange":"BATS","Date":"12/2/2021","Open":0.9,"High":0.95,"Low":0.83,"Close":0.95,"volume":1200},{"Ticker":"6B","Exchange":"BATS","Date":"12/3/2021","Open":1,"High":1.3,"Low":0.9,"Close":1.2,"volume":1500},{"Ticker":"6C","Exchange":"BATS","Date":"12/4/2021","Open":1.2,"High":1.3,"Low":1.1,"Close":1.1,"volume":1300}]

Can anyone advise me how to change the lambda around to achieve this?

Also, I dont know why this 'LAMBDA_LOCAL_TMP_FILE" is there. I dont need this. (got from some other site). Can you tell me how to remove that and still have the lambda work? Thanks alot.

See [Creating a dictionary from a csv file](https://stackoverflow.com/questions/6740918/creating-a-dictionary-from-a-csv-file). — jarmod, Mar 28 '23 at 00:16

score 1 · Accepted Answer · answered Mar 28 '23 at 00:29

1

you can try with csv.DictReader function

import boto3
import csv
import json

BUCKET_NAME = 'olaptrader-products'
OBJECT_NAME = 'bats.csv'
LAMBDA_LOCAL_TMP_FILE = '/tmp/bats.csv'

def lambda_handler(event, context):
    s3 = boto3.client('s3')
    s3.download_file(BUCKET_NAME, OBJECT_NAME, LAMBDA_LOCAL_TMP_FILE)
    
    with open(LAMBDA_LOCAL_TMP_FILE,'r') as infile:
        reader = csv.DictReader(infile)
        rows = list(reader)
    
    return {
        'body': json.dumps(rows)
    }

answered Mar 28 '23 at 00:29

Milton Jesus

36
6

Thanks. that worked great. I know it's kind of a separate question, but any idea how I remove these references to "LAMBDA_LOCAL_TMP_FILE" and still have this work? – bob Mar 28 '23 at 01:07
@bob We are welcome, the variable LAMBDA_LOCAL_TMP_FILE refers about the filename where you want to put on. You can change this variable to FILE_NAME – Milton Jesus Mar 28 '23 at 16:41

formatting json output from lambda that reads csv from S3

1 Answers1