以CSV格式下载AWS DynamoDB数据到Excel

Question

我想将AWS DynamoDB数据下载到Excel中，以允许我在本地使用数据。然而，我还没有得到一个完美的CSV格式的数据。

我所做的:我使用一个Node.js应用程序，它运行在AWS Lambda服务中连接到DynamoDB数据库。此外，我可以从DynamoDB查询数据，然后将其转换为CSV格式，如下所示:

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('rn')
body = JSON.stringify(csv);
return {
body,
headers,
};
};

上述解决方案有效，但输出并不完美;下面显示了一个示例(注意有三列:相对湿度、水温和空气温度):

"relativeHumidity,水温,失效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">

但是，我想要如下所示的输出:

relativeHumidity,水温,失效airTemperature26.123206154221034, 21.716873058693757, 23.85949159893455726.966163183232673, 18.09642888420125, 21.4795261754798933.79030978475366, 18.995791668472204, 17.451627574004128

40.6641803491319, 19.89060168145951, 17.61247262137161我将感谢如何实现这一点的任何指导。请注意，我已经尝试过了，但是数据以json格式导出到S3。

<代码>

Answer 1

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}`);
});

Answer 2

因为AWS Lambda需要返回一个与JSON兼容的对象。我将切换到Express, Node.js和body-parser，它们简化了问题的解决方案。

PP_4