How to convert excel file to JSON with nested array in nodejs

Question

I am trying to convert my excel file to JSON. Basically, I am able to do it but I did not get my expected result. I am trying to add a nested array to my JSON file.

My basic excel sheet.

Here I have tried to convert it into JSON files using convert-excel-to-json npm, and I did it successfully. But I want to add another field whose name is images. On the images field, there will be id and src. The images filed will show in an array of objects.

app.post("/upload-excel", async (req, res) => {
            const file = req.files.file;
            const filename = file.name;
            const worksheetsArray = xlsx.parse(filename); // parses a file

            const excelData = excelToJson({
                sourceFile: filename,
                sheets: [{
                    // Excel Sheet Name
                    name: worksheetsArray[0].name,

                    // Header Row -> be skipped and will not be present at our result object.
                    header: {
                        rows: 1
                    },

                    // Mapping columns to keys
                    columnToKey: {
                        A: '_id',
                        B: 'name',
                        C: 'address',
                        D: 'age'
                    }
                }]
            });
             const withImages = excelData.Customers.map((customer) => {
                return {
                    images: [
                        { id: customer.image_id, src: customer.image_src }
                    ],
                    ...customer,
                };
            });
            console.log(withImages);
        });

The above code output is:

{
  Customers: [
    { _id: 1, name: 'Jack Smith', address: 'Massachusetts', age: 23 },
    { _id: 2, name: 'Adam Johnson', address: 'New York', age: 27 },
    {
      _id: 3,
      name: 'Katherin Carter',
      address: 'Washington DC',
      age: 26
    },
    { _id: 4, name: 'Jack London', address: 'Nevada', age: 33 },
    { _id: 5, name: 'Jason Bourne', address: 'California', age: 36 }
  ]
}

Below JSON is My expected output. I think it's not possible by the npm which I am using. if you could suggest to me how can achieve my expected result I will be very helpful.

{
  Customers: [
    { _id: 1, name: 'Jack Smith',images:[{id:1, src:"sadasd.com"}], address: 'Massachusetts', age: 23 },
    { _id: 2, name: 'Adam Johnson', address: 'New York',images:[{id:1, src:"sadasd.com"}] age: 27 },
    {
      _id: 3,
      name: 'Katherin Carter',
      address: 'Washington DC',
      age: 26,
images:[{id:1, src:"sadasd.com"}, {id:1, src:"sadasd.com"}],
 
    { _id: 4, name: 'Jack London', address: 'Nevada',images:[{id:1, src:"sadasd.com"}], {id:1, src:"sadasd.com"}], age: 33 },
    { _id: 5, name: 'Jason Bourne',images:[{id:1, src:"sadasd.com"}, address: 'California', age: 36 }
  ]
}

You'll likely need to map the json output from the sheet on your own to add fields like that. It is hard to come up with a specific answer as we don't know where the image data is coming from. — ionizer
– ionizer, Commented Mar 17, 2022 at 15:37
You should add the images field after you have the current output, using map: ` excelData = excelData.map(row => row.image = _functionToGetYourImagesArray() ) ` — Luis Gonzalez
– Luis Gonzalez, Commented Mar 17, 2022 at 15:37

Disco · Accepted Answer · 2022-03-17 16:16:49Z

1

You can use map to change the items in the array and add more information and then use spread to add the existing fields as well.

const withImages = excelData.Customers.map((customer) => {
  const imgIds = customer.image_id.split(",");
  const imgSrcs = customer.image_src.split(",");
  return {
    images: imgIds.map((id, i) => {
      return { id, src: imgSrcs[i] };
    }),
    ...customer,
  };
});

edited Mar 17, 2022 at 16:16

answered Mar 17, 2022 at 15:39

Disco

1,6641 gold badge8 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Fathe Over a year ago

Thank you, I am able to set only one image, if I have multiple images on my excel file how can I add multiple objects inside the images array?

Disco Over a year ago

You can add whatever you want in the map method. I all depends on how you get the images. As @Luiz Gonzalez suggests above that you could use a function to get the images. If you share the code that you use to get the image I can try to update my answer

Fathe Over a year ago

I have updated my question. I don't have any image get function and I am using your provided code to add images array of objects. Also you can see the image I have added the extra columns image_id and image_src, I separate the images with a coma.

Disco Over a year ago

Updated my answer, its not the best code I have ever written but show do the job. I have not tested the code so it might be some minor issues with it. Remember to add the new columns as well in columnToKey

Collectives™ on Stack Overflow

How to convert excel file to JSON with nested array in nodejs

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related