Hey there! Since you already know the basics of Lambda and API Gateway, let's walk through exactly how to build this flow—no file storage needed, just direct Excel → CSV/JSON conversion and processing.

1. Set Up Your API Gateway Endpoint #

First, let's configure the API to accept file uploads:

• Create a REST API (or HTTP API if you prefer—REST is more intuitive for beginners here) in API Gateway.
• Add a POST method to your resource (e.g., /upload-excel).
• Choose Lambda Proxy Integration—this lets API Gateway pass the entire request (including the uploaded file) directly to your Lambda function without extra mapping.
• Link it to your Node.js Lambda function, then deploy the API to a stage (e.g., prod).

Important: Make sure your API accepts the multipart/form-data content type—you can enable this in the method request settings under "Content Type".

2. Prepare Your Node.js Lambda Function #

Next, let's set up the Lambda to handle the file input. We'll use the xlsx library (a popular Excel parser for Node.js):

• Initialize a Node.js project locally: npm init -y
• Install dependencies: npm install xlsx aws-sdk (include aws-sdk if you're using AWS services like DynamoDB for persistence)
• Zip your code + node_modules folder (or use Lambda Layers if you want to reuse dependencies across functions) and upload it to Lambda, or use the Lambda console's code editor (though zipping is more reliable for dependencies).

3. Parse the Excel File & Convert to CSV/JSON #

Here's the core code to decode the uploaded file, parse it, and convert it:

const XLSX = require('xlsx');

exports.handler = async (event) => {
  try {
    // 1. Extract the base64-encoded Excel file from the API Gateway request
    const fileContent = event.body;
    const isBase64Encoded = event.isBase64Encoded;
    
    // 2. Decode the base64 content to a buffer
    const buffer = isBase64Encoded ? Buffer.from(fileContent, 'base64') : Buffer.from(fileContent);
    
    // 3. Parse the Excel file
    const workbook = XLSX.read(buffer, { type: 'buffer' });
    
    // 4. Get the first worksheet (adjust if you need a specific sheet)
    const firstSheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[firstSheetName];
    
    // 5. Convert to JSON or CSV
    const jsonData = XLSX.utils.sheet_to_json(worksheet);
    // const csvData = XLSX.utils.sheet_to_csv(worksheet); // uncomment if you need CSV instead
    
    console.log('Parsed Excel data:', jsonData);
    
    // --- Your processing & persistence logic here ---
    await processAndPersistData(jsonData);
    
    return {
      statusCode: 200,
      body: JSON.stringify({ message: 'File processed successfully', data: jsonData })
    };
  } catch (error) {
    console.error('Error processing file:', error);
    return {
      statusCode: 500,
      body: JSON.stringify({ error: 'Failed to process file', details: error.message })
    };
  }
};

// Example: Persist data to DynamoDB
async function processAndPersistData(data) {
  const { DynamoDB } = require('aws-sdk');
  const dynamoDb = new DynamoDB.DocumentClient();
  
  for (const item of data) {
    await dynamoDb.put({
      TableName: 'YourTableName', // Replace with your DynamoDB table name
      Item: item
    }).promise();
  }
}

4. Configure Permissions #

Don't forget to set up the right IAM permissions:

• Your Lambda execution role needs permission to interact with your persistence service (e.g., dynamodb:PutItem for DynamoDB). Add this via the IAM console or inline policy.
• API Gateway needs permission to invoke your Lambda function—this is usually set automatically when you link the Lambda to the API, but double-check in the Lambda's "Configuration" > "Triggers" tab.

5. Test the Flow #

Use a tool like Postman or curl to test the endpoint:

• Set the request method to POST and use your API's deployed URL (e.g., https://abc123.execute-api.us-east-1.amazonaws.com/prod/upload-excel).
• Set the body to form-data, add a key (e.g., file), select "File" as the type, and upload your Excel file.
• Check the response and verify that data is being persisted correctly (e.g., look in DynamoDB or check Lambda logs in CloudWatch).

Troubleshooting Tips #

• If you get a "dependency not found" error, make sure you zipped your node_modules folder along with your code, or use Lambda Layers for xlsx.
• Check CloudWatch Logs for Lambda to see exactly where the error occurs—this is your best friend for debugging!
• Ensure your Excel file doesn't have merged cells or complex formatting that might break the parser (the xlsx library handles most standard formats, but weird layouts can cause issues).

内容的提问来源于stack exchange，提问作者Nisman