juanjoDiaz / json2csv

Flexible conversion between JSON and CSV
https://juanjodiaz.github.io/json2csv/
MIT License
306 stars 32 forks source link

Data not rendering in excel if previous column contain a # #60

Open CodingXD opened 1 year ago

CodingXD commented 1 year ago

Issue

If a column contained a # somewhere, it would not render the remaining characters and the next columns' data.

To reproduce

Used dataset

        const res = [
          {
            id: "468fe976-c50b-45a3-a7f0-2e7fa21bc3ea",
            brand: "Acute",
            campaign: "Campaign #1",
            round: 1,
            username: "Fairy",
            createdAt: "2023-10-17T06:41:08.474Z",
            visits: 0,
            timeSpend: "0 seconds",
          },
        ];

Used Code

        const parser = new AsyncParser({
          fields: [
            {
              label: "ID",
              value: "id",
            },
            {
              label: "Brand",
              value: "brand",
            },
            {
              label: "Campaign",
              value: "campaign",
            },
            {
              label: "Round",
              value: "round",
            },
            {
              label: "Username",
              value: "username",
            },
            {
              label: "Visits",
              value: "visits",
            },
            {
              label: "Time Spend",
              value: "timeSpend",
            },
            {
              label: "Created On",
              value: "createdAt",
            },
          ],
        });
        const csv = await parser.parse(res).promise();

        reply.header("Content-disposition", "attachment; filename=data.csv");
        reply.header("Content-Type", "text/csv");
        return reply.send(csv);

In my dataset, there is # in the campaign, so the subsequent columns' data won't render. image If I take out the #, then everything renders image

Any reason why this is happening?

P.S I'm viewing the CSV in Microsoft Excel

Versions

@json2csv/node: 7.0.3 nodejs: 18

juanjoDiaz commented 1 year ago

Hi @CodingXD ,

I run your code and the generated CSV is correct:

"ID","Brand","Campaign","Round","Username","Visits","Time Spend","Created On"
"468fe976-c50b-45a3-a7f0-2e7fa21bc3ea","Acute","Campaign #1",1,"Fairy",0,"0 seconds","2023-10-17T06:41:08.474Z"

So, the issue is on the way that excel process CSV. You can try using the StringExcel formatter to workaround this if all you care is for it to look good on excel.

CodingXD commented 1 year ago

I tried with StringExcel formatter too, but that also failed.

        const parser = new AsyncParser({
          fields: [
            {
              label: "ID",
              value: "id",
            },
            {
              label: "Brand",
              value: "brand",
            },
            {
              label: "Campaign",
              value: "campaign",
            },
            {
              label: "Round",
              value: "round",
            },
            {
              label: "Username",
              value: "username",
            },
            {
              label: "Visits",
              value: "visits",
            },
            {
              label: "Time Spend",
              value: "timeSpend",
            },
            {
              label: "Created On",
              value: "createdAt",
            },
          ],
          formatters: {
            string: stringExcel,
          },
        });

image