excel-to-prisma

excel-to-prisma is a package that parses Excel data and converts it into a data object that can be used in Prisma ORM.

Installation

1npm i excel-to-prisma

Usage

excel-to-prisma works as if it reads the parent table and concatenates the child tables.

Initializing excel-to-prisma

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:

1import { ExcelToPrisma } from "excel-to-prisma";
2
3const excelToPrisma = new ExcelToPrisma({
4  // ==========
5  // your xlsx file
6  // ==========
7  filePath: "./data.xlsx",
8
9  // ==========
10  // primary key Delimiter
11  // ex) userId -> Id, user_id -> _id
12  // ==========
13  pkDelimiterString: "Id",
14
15  // ==========
16  // oneToOneOrMany Option
17  //  - split: String to separate multiple values
18  //    - ex) 1|2
19  // ==========
20  oneToOneOrManyOptions: {
21    split: "|",
22  },
23});
24await excelToPrisma.initialize();

Read single table

When parsing the single table, write it as follows:

1await excelToPrisma.readSheet({
2  name: "banner",
3  rowNameIndex: 2,
4  startRowIndex: 3,
5});

If there is a oneToOneOrMany value, set it as follows:

1await excelToPrisma.readSheet({
2  name: "banner",
3  rowNameIndex: 2,
4  startRowIndex: 3,
5  // ==========
6  // oneToOneOrMany Options
7  //  - key: one or many key name
8  //  - option: one | many
9  //     - one -> single object
10  //     - many -> array
11  //  - operation: connect | set | disconnect
12  // ==========
13  oneToOneOrManyOptions: [
14    { key: "infoProductTag", option: "many", operation: "connect" },
15    { key: "infoProductTag", option: "one", operation: "set" },
16  ],
17});

One to Many Create

When linking to a parent table in a one to many relationship, you would write:

The oneToManyCreate method is executing the oneToOneOrManyConnect method

1await excelToPrisma.oneToManyCreate({
2  name: "post",
3  fk: "userId",
4  rowNameIndex: 2,
5  startRowIndex: 3,
6  // Optional: oneToOneOrManyOptions Options
7});

Subtables in relationship

When connecting child tables in relationship, write as follows:

1await excelToPrisma
2  .readSheet({ name: "user", rowNameIndex: 2, startRowIndex: 3 })
3  .then(async (sheetOption) => {
4    await excelToPrisma
5      .oneToManyCreate({
6        name: "product",
7        fk: "userId",
8        many: sheetOption.name,
9        rowNameIndex: 2,
10        startRowIndex: 3,
11        oneToOneOrManyOptions: [
12          { key: "infoProductTag", option: "many", operation: "connect" },
13          { key: "infoProductTag", option: "one", operation: "set" },
14        ],
15      })
16      .then(async (sheetOption) => {
17        await excelToPrisma
18          .oneToManyCreate({
19            name: "productComment",
20            fk: "productId",
21            many: sheetOption.name,
22            rowNameIndex: 2,
23            startRowIndex: 3,
24          })
25          .then(async (sheetOption) => {
26            await excelToPrisma.oneToManyCreate({
27              name: "productCommentHistory",
28              fk: "productCommentId",
29              many: sheetOption.name,
30              rowNameIndex: 2,
31              startRowIndex: 3,
32            });
33          });
34      });
35  });

Axios usage and example code

1import { ExcelToPrisma } from "excel-to-prisma";
2import axios from "axios";
3
4async function main() {
5  // parse excel to prisma
6  const excelToPrisma = new ExcelToPrisma({
7    filePath: "./data.xlsx",
8    pkDelimiterString: "Id",
9    oneToOneOrManyOptions: {
10      split: "|",
11    },
12  });
13  await excelToPrisma.initialize();
14  await excelToPrisma
15    .readSheet({ name: "user", rowNameIndex: 2, startRowIndex: 3 })
16    .then(async (sheetOption) => {
17      await excelToPrisma
18        .oneToManyCreate({
19          name: "product",
20          fk: "userId",
21          many: sheetOption.name,
22          rowNameIndex: 2,
23          startRowIndex: 3,
24          oneToOneOrManyOptions: [
25            { key: "infoProductTag", option: "many", operation: "connect" },
26            { key: "infoProductTag", option: "one", operation: "set" },
27          ],
28        })
29        .then(async (sheetOption) => {
30          await excelToPrisma
31            .oneToManyCreate({
32              name: "productComment",
33              fk: "productId",
34              many: sheetOption.name,
35              rowNameIndex: 2,
36              startRowIndex: 3,
37            })
38            .then(async (sheetOption) => {
39              await excelToPrisma.oneToManyCreate({
40                name: "productCommentHistory",
41                fk: "productCommentId",
42                many: sheetOption.name,
43                rowNameIndex: 2,
44                startRowIndex: 3,
45              });
46            });
47        });
48    });
49  const data = JSON.stringify(excelToPrisma.getData()); // stringified data
50
51  // axios post to prisma
52  for (let i = 0; i < JSON.parse(data).length; i++) {
53    await axios
54      .post("homepage URL", JSON.parse(data)[i], {
55        headers: {
56          "Content-Type": "application/json",
57        },
58      })
59      .then((res) => {
60        console.log(res.data);
61      })
62      .catch((err) => {
63        console.error(err);
64      });
65  }
66}
67
68main().catch((err) => console.error(err));

API Reference

Refer to the code comments for detailed API usage and method descriptions.

Example

• Excel

• Test Code

• Output JSON

Contact

Seog Donggeun - seogdonggeun@gmail.com

Project Link: excel-to-prisma