AWS Lambda writing arrays to CSV

Hello team,

Welcome to Lambda AWS premium support and I am going to be assisting you on this query today.

I understand you are running a python code [1] in lambda that is writing an array into a csv and then uploading it to S3. The array objects are in different datatypes. When you test the python locally [2], the csv is produced fine. However on lambda [3], You see a keyword "number:" being added to the row. You would like to know how can you get rid of this keyword while writing. You are welcome and encouraged to indicate if I have misunderstood the issue you are reporting as this will allow for us to be on the same page which is crucial when it comes to addressing your issue effectively.

I went ahead and test your code locally [4] and actually got the following output both locally and on Lambda [5]. From this I cannot determine as to why the code is writing the files differently on your end. I also noticed you recreate the headings "csv_file.writerow" even though the headings are already defined in "text=" and also the defined variable mentioned has the word Test1. Please let me know if the intention of your code is to rewrite the headings are to make use of "text=" and get the headings from it.

So as the local and Lambda one produce the same results when I tested on my end I re-approached the code you gave by making use of the Pandas module and removing the word "Test1" from the "text=". Here is my code that I used. [6] I tested this code locally by obviously removing the handler and return and ran it on Lambda and got the desired result you are looking for.

I strongly suggest as your local and Lambda are producing completely different results to my test using the same code you provided that you kindly please create a case in order to facilitate a detailed response as we need access to your account to understand why your Lambda and local code is running different even though when I tested the same code I get a different result.

Please also kindly note that code support falls out of scope of AWS premium support and falls on the customer side of the shared responsibility model and is done on a best effort basis. [7] Please also kindly note that any code provided by AWS engineers are not be used in production and is rather shown with the intent of demonstration. If you do require code support I do advice that you please make use of our professional services where developer support is provided. [8] [9]

Currently the reason I am asking that you please open a support case is to investigate the discrepancy of the code you provided being tested on your machine and my machine as well as on Lambda i.e. the code is producing different results when tested from two different Lambdas in two different accounts i.e your account and my account. As for the actual code again that part is out of scope.

As always, you are welcome to add any questions related to this re:Post.

[1]

import re
import csv
import boto3

text = "Test1 AccountNumber BeginBal TotCredits TotDebits EndBal 1234567890 $111,803.93 $31,500.34 -$31,510.34 $111,793.93"
    array = re.split(r'(\s)', text)
 
    f = open("/tmp/test_csv.csv","w+")
    csv_file =csv.writer(f, delimiter='|', quoting=0)
    csv_file.writerow(['AccountNumber', 'Beginning Balance', 'Total Credits', 'Total Debits', 'Ending Balance'])
    csv_file.writerow([array[7], array[8], array[9], array[10], array[11]])    
    f.close()
    
    s3client.upload_file("/tmp/test_csv.csv", bucket, 'final_report.csv')

[2]

AccountNumber|Beginning Balance|Total Credits|Total Debits|Ending Balance
1234567890|$111,803.93|$31,500.34|-$31,510.34|$111,793.93

[3]

AccountNumber|Beginning Balance|Total Credits|Total Debits|Ending Balance
number:|1234567890|$111,803.93|$31,500.34|-$31,510.34

[4]

import re
import csv

text = "Test1 AccountNumber BeginBal TotCredits TotDebits EndBal 1234567890 $111,803.93 $31,500.34 -$31,510.34 $111,793.93"

array = re.split(r'(\s)', text)

# Change file path to current directory
f = open("test_csv.csv", "w+")
csv_file = csv.writer(f, delimiter='|', quoting=0)
csv_file.writerow(['AccountNumber', 'Beginning Balance', 'Total Credits', 'Total Debits', 'Ending Balance'])
csv_file.writerow([array[7], array[8], array[9], array[10], array[11]])
f.close()

[5]

AccountNumber|Beginning Balance|Total Credits|Total Debits|Ending Balance
 |TotDebits| |EndBal| 

[6]

import pandas as pd
import boto3
from io import StringIO

def lambda_handler(event, context):
    # Replace these with your actual bucket name and region
    bucket = '<bucket_name>'
    region = '<region>'

    # Create an S3 client
    s3 = boto3.client('s3', region_name=region)

    # Updated text without 'Test1'
    text = "AccountNumber BeginBal TotCredits TotDebits EndBal 1234567890 $111,803.93 $31,500.34 -$31,510.34 $111,793.93"
    
    # Split the text into parts
    parts = text.split()
    
    # Extract headers and data dynamically
    headers = parts[0:5]  # First five parts are headers
    data = parts[5:]      # Remaining parts are data

    # Create a DataFrame using pandas
    df = pd.DataFrame([data], columns=headers)

    # Create a CSV buffer in memory
    csv_buffer = StringIO()
    
    # Write DataFrame to CSV format with '|' delimiter
    df.to_csv(csv_buffer, sep='|', index=False, header=True)

    # Upload the CSV file to S3
    s3.put_object(Bucket=bucket, Key='final_report.csv', Body=csv_buffer.getvalue())

    return {
        'statusCode': 200,
        'body': 'CSV file uploaded to S3 successfully!'
    }

[7] https://aws.amazon.com/premiumsupport/faqs/

[8] https://aws.amazon.com/professional-services/

[9] https://iq.aws.amazon.com/