excel-to-prisma is a package that parses Excel data and converts it into a data object that can be used in Prisma ORM.
npm i excel-to-prismaexcel-to-prisma works as if it reads the parent table and concatenates the child tables.
First, import and create an instance of excel-to-prisma with the path to your Excel file and the worksheet name you intend to work with:
import { ExcelToPrisma } from "excel-to-prisma";
const excelToPrisma = new ExcelToPrisma({
// ==========
// your xlsx file
// ==========
filePath: "./data.xlsx",
// ==========
// primary key Delimiter
// ex) userId -> Id, user_id -> _id
// ==========
pkDelimiterString: "Id",
// ==========
// oneToOneOrMany Option
// - split: String to separate multiple values
// - ex) 1|2
// ==========
oneToOneOrManyOptions: {
split: "|",
},
});
await excelToPrisma.initialize();When parsing the single table, write it as follows:
await excelToPrisma.readSheet({
name: "banner",
rowNameIndex: 2,
startRowIndex: 3,
});If there is a oneToOneOrMany value, set it as follows:
await excelToPrisma.readSheet({
name: "banner",
rowNameIndex: 2,
startRowIndex: 3,
// ==========
// oneToOneOrMany Options
// - key: one or many key name
// - option: one | many
// - one -> single object
// - many -> array
// - operation: connect | set | disconnect
// ==========
oneToOneOrManyOptions: [
{ key: "infoProductTag", option: "many", operation: "connect" },
{ key: "infoProductTag", option: "one", operation: "set" },
],
});When linking to a parent table in a one to many relationship, you would write:
The oneToManyCreate method is executing the oneToOneOrManyConnect method
await excelToPrisma.oneToManyCreate({
name: "post",
fk: "userId",
rowNameIndex: 2,
startRowIndex: 3,
// Optional: oneToOneOrManyOptions Options
});When connecting child tables in relationship, write as follows:
await excelToPrisma
.readSheet({ name: "user", rowNameIndex: 2, startRowIndex: 3 })
.then(async (sheetOption) => {
await excelToPrisma
.oneToManyCreate({
name: "product",
fk: "userId",
many: sheetOption.name,
rowNameIndex: 2,
startRowIndex: 3,
oneToOneOrManyOptions: [
{ key: "infoProductTag", option: "many", operation: "connect" },
{ key: "infoProductTag", option: "one", operation: "set" },
],
})
.then(async (sheetOption) => {
await excelToPrisma
.oneToManyCreate({
name: "productComment",
fk: "productId",
many: sheetOption.name,
rowNameIndex: 2,
startRowIndex: 3,
})
.then(async (sheetOption) => {
await excelToPrisma.oneToManyCreate({
name: "productCommentHistory",
fk: "productCommentId",
many: sheetOption.name,
rowNameIndex: 2,
startRowIndex: 3,
});
});
});
});import { ExcelToPrisma } from "excel-to-prisma";
import axios from "axios";
async function main() {
// parse excel to prisma
const excelToPrisma = new ExcelToPrisma({
filePath: "./data.xlsx",
pkDelimiterString: "Id",
oneToOneOrManyOptions: {
split: "|",
},
});
await excelToPrisma.initialize();
await excelToPrisma
.readSheet({ name: "user", rowNameIndex: 2, startRowIndex: 3 })
.then(async (sheetOption) => {
await excelToPrisma
.oneToManyCreate({
name: "product",
fk: "userId",
many: sheetOption.name,
rowNameIndex: 2,
startRowIndex: 3,
oneToOneOrManyOptions: [
{ key: "infoProductTag", option: "many", operation: "connect" },
{ key: "infoProductTag", option: "one", operation: "set" },
],
})
.then(async (sheetOption) => {
await excelToPrisma
.oneToManyCreate({
name: "productComment",
fk: "productId",
many: sheetOption.name,
rowNameIndex: 2,
startRowIndex: 3,
})
.then(async (sheetOption) => {
await excelToPrisma.oneToManyCreate({
name: "productCommentHistory",
fk: "productCommentId",
many: sheetOption.name,
rowNameIndex: 2,
startRowIndex: 3,
});
});
});
});
const data = JSON.stringify(excelToPrisma.getData()); // stringified data
// axios post to prisma
for (let i = 0; i < JSON.parse(data).length; i++) {
await axios
.post("homepage URL", JSON.parse(data)[i], {
headers: {
"Content-Type": "application/json",
},
})
.then((res) => {
console.log(res.data);
})
.catch((err) => {
console.error(err);
});
}
}
main().catch((err) => console.error(err));Refer to the code comments for detailed API usage and method descriptions.
Seog Donggeun - seogdonggeun@gmail.com
Project Link: excel-to-prisma