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 a leading provider of Cloud Training and Consulting services with a global presence in India, the USA, Asia, Europe, and Africa. Specializing in AWS, Microsoft Azure, GCP, VMware, Databricks, and more, the company serves mid-market and enterprise clients, offering comprehensive expertise in Cloud Migration, Data Platforms, DevOps, IoT, AI/ML, and more.

CloudThat is recognized as a top-tier partner with AWS and Microsoft, including the prestigious ‘Think Big’ partner award from AWS and the Microsoft Superstars FY 2023 award in Asia & India. Having trained 650k+ professionals in 500+ cloud certifications and completed 300+ consulting projects globally, CloudThat is an official AWS Advanced Consulting Partner, AWS Training Partner, AWS Migration Partner, AWS Data and Analytics Partner, AWS DevOps Competency Partner, Amazon QuickSight Service Delivery Partner, Amazon EKS Service Delivery Partner, Microsoft Gold Partner, AWS Microsoft Workload Partners, Amazon EC2 Service Delivery Partner, and many more.

To get started, go through our Consultancy page and Managed Services Package, CloudThat’s offerings.

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.