Download AWS DynamoDB data to Excel with CSV format-CodePudding

I want to download AWS DynamoDB data to Excel to allow me to work with the data locally. However, I have not been to get the data in a perfect CSV format.

What I have done: I use a Node.js application, which runs in AWS Lambda service to connect to the DynamoDB database. In addition, I can query the data from DynamoDB and then convert it to a CSV format, as detailed below:

const AWS = require("aws-sdk");

AWS.config.update({ region: "us-east-1"})
const dynamo = new AWS.DynamoDB.DocumentClient({apiversion: "2012-08-10"});


exports.handler = async (event, context) => {
    
  let body;
  const headers = {
      "Content-Type": "text/csv",
      'Content-disposition': 'attachment; filename=testing.csv'
  };
  
 var params = {
     KeyConditionExpression: 'dataId = :id',
     ExpressionAttributeValues: {
         ':id': event.pathParameters.id,
     },
     TableName: "Table1",
 };
 body = await dynamo.query(params).promise();
        
 //-----------------------------------
 // convert json to csv
 const items = body.Items
 const replacer = (key, value) => value === null ? '' : value
 const header = Object.keys(items[0])
 let csv = [header.join(','), 
            ...items.map(row => header.map(fieldName => JSON.stringify(row[fieldName], replacer)).join(','))
            ].join('\r\n')

body = JSON.stringify(csv);
  return {
     body,
     headers,
  };
};

The above solution works, but the output is not perfect; a sample is shown below (Note that there are three columns: relativeHumidity, waterTemperature, and airTemperature):

"relativeHumidity,waterTemperature,airTemperature\r\n26.123206154221034,21.716873058693757,23.859491598934557\r\n26.966163183232673,18.09642888420125,21.47952617547989\r\n33.79030978475366,18.995791668472204,17.451627574004128\r\n40.6641803491319,19.89060168145951,17.61247262137161"

However, I want an output that looks as shown below:

relativeHumidity,waterTemperature,airTemperature 26.123206154221034,21.716873058693757,23.859491598934557 26.966163183232673,18.09642888420125,21.47952617547989 33.79030978475366,18.995791668472204,17.451627574004128 40.6641803491319,19.89060168145951,17.61247262137161

I would appreciate any guide on how to achieve this. Note that I have tried this, but the data is being exported to S3 in json format.

CodePudding user response：

let inputString =  `"relativeHumidity,waterTemperature,airTemperature\r\n26.123206154221034,21.716873058693757,23.859491598934557\r\n26.966163183232673,18.09642888420125,21.47952617547989\r\n33.79030978475366,18.995791668472204,17.451627574004128\r\n40.6641803491319,19.89060168145951,17.61247262137161"`;
let result = inputString.split("\n");
result.forEach( val => {
  console.log(val);
})

CodePudding user response：

Since AWS Lambda requires to return an object that is compatible with JSON.stringify, I am switching to Express, Node.js, and body-parser, which simplify the solution to the problem.

const bodyParser = require("body-parser");
const AWS = require("aws-sdk");
AWS.config.update({ region: "us-east-1" });
const dynamo = new AWS.DynamoDB.DocumentClient({ apiversion: "2012-08-10" });

const app = express();
const port = 3000;
app.use(bodyParser.json());

app.get("/", async (req, res) => {
  let body;
  res.setHeader(
    "Content-disposition",
    "attachment; filename=shifts-report.csv"
  );
  res.set("Content-Type", "text/csv");

  let csv;
  var params = {
    KeyConditionExpression: "deviceNameDate = :dnd",
    ExpressionAttributeValues: {
      ":dnd": "Factory1/Section1/Container1/2022-11-16",
    },
    ProjectionExpression: "waterTemperature, airTemperature, relativeHumidity",
    TableName: "factory1-section1-container1",
  };
  body = await dynamo.query(params).promise();

  // convert json to csv
  const items = body.Items;
  const replacer = (key, value) => (value === null ? "" : value);
  const header = Object.keys(items[0]);
  csv = [
    header.join(","),
    ...items.map((row) =>
      header
        .map((fieldName) => JSON.stringify(row[fieldName], replacer))
        .join(",")
    ),
  ].join("\n");

  res.send(csv);
});

app.listen(port, () => {
  console.log(`App started on port ${port}`);
});