A Guide to Convert Excel to CSV Using AWS Lambda and Amazon S3

Overview

In the digital transformation era, businesses and individuals generate vast amounts of data, often stored in various formats. Excel files are a common choice for data manipulation and analysis. However, the CSV (Comma-Separated Values) format is often more versatile and lightweight regarding large-scale data processing or integration with other systems. In this blog post, we will explore an efficient and automated solution for converting Excel files to CSV using AWS Lambda and Amazon S3.

Pioneers in Cloud Consulting & Migration Services

Reduced infrastructural costs
Accelerated application deployment

Get Started

Introduction

Traditionally, converting Excel files to CSV may involve manual processes, consuming time and resources better utilized elsewhere. However, we can automate this task with cloud computing and serverless architecture, ensuring a seamless and consistent data processing pipeline.

AWS Lambda, a serverless computing service, provides an ideal platform for executing code without the need to manage servers. When combined with Amazon S3, a scalable and durable object storage service, we can create a robust system that automatically converts Excel files to CSV upon arrival in an Amazon S3 bucket.

In the following sections, we’ll guide you through the steps to set up this automation, empowering you to focus on deriving insights from your data rather than spending time on manual conversion tasks. Let’s dive into the world of serverless computing and witness the simplicity and efficiency it brings to data processing workflows.

Pre-requisites

AWS Account:

You must have an active AWS account to utilize AWS Lambda and Amazon S3 services. You can sign up on the AWS website and follow the account creation process if you don’t have one.

AWS CLI or AWS Management Console Access:

Ensure you can access the AWS Command Line Interface (CLI) or the AWS Management Console. Familiarity with AWS CLI commands can benefit certain configurations, but most tasks can be performed through the AWS Management Console.

Amazon S3 Bucket:

Create an Amazon S3 bucket where your Excel files are stored. Note the bucket name, as you’ll need it during setup.

AWS IAM Role:

Create an AWS IAM (Identity and Access Management) role that Lambda can assume to execute actions on your behalf. Attach the AWSLambdaExecute policy to this role and add permissions allowing access to your Amazon S3 bucket (s3:GetObject, s3:PutObject, etc.) or give the s3 full access permissions.

Steps to Create an AWS Lambda Function

Navigate to the AWS Lambda service in the AWS Management Console.
Click “Create function” and choose “Author from scratch.”
Provide a name, select the runtime (Python, for example), and choose the role created in Step 1.
Click on “Create function.”

Write AWS Lambda Function Code:

import os
import csv
import openpyxl
import tempfile
import boto3
 
def lambda_handler(event, context):
    print("Received event:", event)  # Print the entire event for debugging
 
    try:
        # Extracting S3 bucket and object key
        bucket_name = "xlsx-csv-new"
        file_key = "Digital_marketing.xlsx"
    except KeyError as e:
        error_message = f"Error extracting S3 information: {e}"
        print(error_message)
        return {"error": error_message}
 
    # Check if the file is an Excel file
    if not file_key.lower().endswith('.xlsx'):
        error_message = f"Skipping non-Excel file: {file_key}"
        print(error_message)
        return {"error": error_message}
 
    # Temporary directory to store files
    temp_dir = tempfile.mkdtemp()
 
    try:
        # Download the Excel file from S3
        s3 = boto3.client('s3')
        excel_file_path = os.path.join(temp_dir, 'input.xlsx')
        s3.download_file(bucket_name, file_key, excel_file_path)
 
        # Convert Excel to CSV
        csv_file_name = f"{os.path.splitext(os.path.basename(file_key))[0]}-output.csv"
        csv_file_path = os.path.join(temp_dir, csv_file_name)
        convert_excel_to_csv(excel_file_path, csv_file_path)
 
        # Upload the output CSV file to S3
        s3.upload_file(csv_file_path, bucket_name, csv_file_name)
 
        # Clean up temporary directory
        cleanup_temp_directory(temp_dir)
 
        # Return the output CSV file name in the Lambda response
        return {"output_csv_file": csv_file_name}
 
    except Exception as e:
        error_message = f"Error processing the file: {e}"
        print(error_message)
        return {"error": error_message}
 
def convert_excel_to_csv(excel_file_path, csv_file_path):
    workbook = openpyxl.load_workbook(excel_file_path)
    sheet = workbook.active
 
    with open(csv_file_path, 'w', newline='') as csv_file:
        csv_writer = csv.writer(csv_file)
        for row in sheet.iter_rows():
            csv_writer.writerow([cell.value for cell in row])
 
def cleanup_temp_directory(temp_dir):
    for file_name in os.listdir(temp_dir):
        file_path = os.path.join(temp_dir, file_name)
        try:
            if os.path.isfile(file_path):
                os.unlink(file_path)
        except Exception as e:
            error_message = f"Error cleaning up file {file_path}: {e}"
            print(error_message)
 
    try:
        os.rmdir(temp_dir)
    except Exception as e:
        error_message = f"Error cleaning up temp directory {temp_dir}: {e}"
        print(error_message)

import os

import csv

import openpyxl

import tempfile

import boto3

def lambda_handler(event, context):

print("Received event:", event) # Print the entire event for debugging

try:

# Extracting S3 bucket and object key

bucket_name = "xlsx-csv-new"

file_key = "Digital_marketing.xlsx"

except KeyError as e:

error_message = f"Error extracting S3 information: {e}"

print(error_message)

return {"error": error_message}

# Check if the file is an Excel file

if not file_key.lower().endswith('.xlsx'):

error_message = f"Skipping non-Excel file: {file_key}"

print(error_message)

return {"error": error_message}

# Temporary directory to store files

temp_dir = tempfile.mkdtemp()

try:

# Download the Excel file from S3

s3 = boto3.client('s3')

excel_file_path = os.path.join(temp_dir, 'input.xlsx')

s3.download_file(bucket_name, file_key, excel_file_path)

# Convert Excel to CSV

csv_file_name = f"{os.path.splitext(os.path.basename(file_key))[0]}-output.csv"

csv_file_path = os.path.join(temp_dir, csv_file_name)

convert_excel_to_csv(excel_file_path, csv_file_path)

# Upload the output CSV file to S3

s3.upload_file(csv_file_path, bucket_name, csv_file_name)

# Clean up temporary directory

cleanup_temp_directory(temp_dir)

# Return the output CSV file name in the Lambda response

return {"output_csv_file": csv_file_name}

except Exception as e:

error_message = f"Error processing the file: {e}"

print(error_message)

return {"error": error_message}

def convert_excel_to_csv(excel_file_path, csv_file_path):

workbook = openpyxl.load_workbook(excel_file_path)

sheet = workbook.active

with open(csv_file_path, 'w', newline='') as csv_file:

csv_writer = csv.writer(csv_file)

for row in sheet.iter_rows():

csv_writer.writerow([cell.value for cell in row])

def cleanup_temp_directory(temp_dir):

for file_name in os.listdir(temp_dir):

file_path = os.path.join(temp_dir, file_name)

try:

if os.path.isfile(file_path):

os.unlink(file_path)

except Exception as e:

error_message = f"Error cleaning up file {file_path}: {e}"

print(error_message)

try:

os.rmdir(temp_dir)

except Exception as e:

error_message = f"Error cleaning up temp directory {temp_dir}: {e}"

print(error_message)

Configure the AWS Lambda Trigger

Add an Amazon S3 trigger to the AWS Lambda function by clicking “Add trigger” in the AWS Lambda Designer. Select your Amazon S3 bucket and set the event type (e.g., ObjectCreated).

Save the AWS Lambda function.

Conclusion

In conclusion, the integration of AWS Lambda and Amazon S3 for automating Excel to CSV conversion marks a significant leap forward in data processing efficiency. By leveraging the serverless architecture of AWS Lambda, businesses, and individuals can effortlessly handle diverse data formats without the need for manual intervention. This automation expedites the conversion process and ensures a consistent and reliable workflow, allowing users to focus on deriving valuable insights from their data rather than getting bogged down by tedious file manipulation tasks.

While this approach presents a robust solution for many scenarios, it’s essential to consider the specific requirements of your use case. Depending on the complexity of your Excel files, additional script customization may be necessary. Moreover, users should be mindful of potential costs associated with Lambda execution and Amazon S3 storage, particularly in high-volume scenarios. Despite these considerations, scalability, ease of maintenance, and the ability to tailor the solution to unique needs make AWS Lambda and Amazon S3 indispensable for automating Excel to CSV conversions in the ever-evolving data management landscape.

Drop a query if you have any questions regarding AWS Lambda or Amazon S3 and we will get back to you quickly.

Making IT Networks Enterprise-ready – Cloud Management Services

Accelerated cloud migration
End-to-end view of the cloud environment

Get Started

About CloudThat

CloudThat is an award-winning company and the first in India to offer cloud training and consulting services worldwide. As a Microsoft Solutions Partner, AWS Advanced Tier Training Partner, and Google Cloud Platform Partner, CloudThat has empowered over 850,000 professionals through 600+ cloud certifications winning global recognition for its training excellence including 20 MCT Trainers in Microsoft’s Global Top 100 and an impressive 12 awards in the last 8 years. CloudThat specializes in Cloud Migration, Data Platforms, DevOps, IoT, and cutting-edge technologies like Gen AI & AI/ML. It has delivered over 500 consulting projects for 250+ organizations in 30+ countries as it continues to empower professionals and enterprises to thrive in the digital-first world.

FAQs

1. How do I handle errors during the conversion process?

ANS: – The example script assumes a straightforward conversion. To handle errors, consider implementing error-checking mechanisms within the script. Amazon CloudWatch Logs can capture and monitor errors during AWS Lambda execution, providing insights for debugging and improvement.

2. Are there any limitations to using AWS Lambda for this task?

ANS: – While AWS Lambda is a powerful solution, it’s important to consider its limitations. For instance, there are execution time and memory constraints. If your Excel files are exceptionally large or the conversion process is time-intensive, you may need to adjust Lambda settings or explore alternative solutions.