CODEWITHSUNDEEP
javascriptnode.jscsvutf-8character-encoding
adit
adit

Reputation: 33674

character encoding when transforming special characters in JSON into CSV (excel)

I am currently trying to populate a .csv file from a json using 

const createCsvWriter = require('csv-writer').createObjectCsvWriter;

i've read the default encoding they use is utf-8, some of my JSON has special characters, emoji, or chinese letters in it.. here's an example

[ { username: 'jung.y00n',
    name: '정 윤',
    bio:
     'ғʀᴇᴇʟᴀɴᴄᴇʀᴍᴏᴅᴇʟ\nᴄᴏɴᴛᴀᴄᴛ 📩 ᴅᴍ 사적/공구 연락 확인 못해요\n⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀\n🔜바이오리 슬림/브이 스틱 (10/14-16)',
    pic_url: 'C:/Users/Administrator/Downloadsjung.y00n.jpg' } ]

[ { username: 'yujing.lai',
    name: '勇闖美利堅的美少女 * Miss.Amber',
    bio:
     '📍San Francisco,California USA\n🎓Academy of Art University🇺🇸Master of Fine Arts\n🎓National Taiwan University of Art🇹🇼BFA\n✈️🇹🇼🇮🇹🇪🇺🇺🇸🇳🇴🇦🇹🇭🇷🇸🇮🇻🇦🇻🇳🇰🇷🇭🇰🇯🇵🇨🇳🇲🇴',
    pic_url: 'C:/Users/Administrator/Downloadsyujing.lai.jpg' } ]

[ { username: 'rky_thitiworada',
    name: 'ฐิติวรดา จังจริง',
    bio:
     'Thitiworada Janjring\n🎧DJ.Rky🎧\nI\'m interested in music,mixing,EDM,DJ   \n ติดต่องาน ☎️ 097-0922200 \nติดต่องาน ID line➡️  djrky  \n🖤#msrk 💞👫 @mashiimx',
    pic_url: 'C:/Users/Administrator/Downloadsrky_thitiworada.jpg' } ]

[ { username: 'mimisayhihi',
    name: '米恩綺Mimi',
    bio:
     '📍Taipei \n📩工作聯繫窗口\nKaren ：[email protected] \n方慈/judy@ cclfilm.com \n活動邀約/[email protected]',
    pic_url: 'C:/Users/Administrator/Downloadsmimisayhihi.jpg' } ]

however after exporting the JSON to csv and open it in ms excel in windows it looked like this

enter image description here

Any idea on what i need to do to make this encoded and shown correctly ?

Upvotes: 0

Views: 2492

Answers (1)

Terry Lennox
Terry Lennox

Reputation: 30725

You can prepend a Byte order mark to the file to tell Excel to decode the characters correctly, this should do the trick: 

const users = [ { username: 'jung.y00n',
    name: '정 윤',
    bio:
    'ғʀᴇᴇʟᴀɴᴄᴇʀᴍᴏᴅᴇʟ\nᴄᴏɴᴛᴀᴄᴛ 📩 ᴅᴍ 사적/공구 연락 확인 못해요\n⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀\n🔜바이오리 슬림/브이 스틱 (10/14-16)',
    pic_url: 'C:/Users/Administrator/Downloadsjung.y00n.jpg' } 
,
{ username: 'yujing.lai',
    name: '勇闖美利堅的美少女 * Miss.Amber',
    bio:
    '📍San Francisco,California USA\n🎓Academy of Art University🇺🇸Master of Fine Arts\n🎓National Taiwan University of Art🇹🇼BFA\n✈️🇹🇼🇮🇹🇪🇺🇺🇸🇳🇴🇦🇹🇭🇷🇸🇮🇻🇦🇻🇳🇰🇷🇭🇰🇯🇵🇨🇳🇲🇴',
    pic_url: 'C:/Users/Administrator/Downloadsyujing.lai.jpg' } 
,
{ username: 'rky_thitiworada',
    name: 'ฐิติวรดา จังจริง',
    bio: 'Thitiworada Janjring\n🎧DJ.Rky🎧\nI\'m interested in music,mixing,EDM,DJ   \n ติดต่องาน ☎️ 097-0922200 \nติดต่องาน ID line➡️  djrky  \n🖤#msrk 💞👫 @mashiimx',
    pic_url: 'C:/Users/Administrator/Downloadsrky_thitiworada.jpg' } 
,
{ username: 'mimisayhihi',
    name: '米恩綺Mimi',
    bio:
    '📍Taipei \n📩工作聯繫窗口\nKaren ：[email protected] \n方慈/judy@ cclfilm.com \n活動邀約/[email protected]',
    pic_url: 'C:/Users/Administrator/Downloadsmimisayhihi.jpg' } 
];

const fs = require("fs");
const createCsvWriter = require('csv-writer').createObjectCsvWriter;

const outputFile = "users.csv";
const csvWriter = createCsvWriter({
    path: outputFile,
    header: Object.keys(users[0]).map(key => { return {id: key, title: key} })
});

function prependBOM(file) {
    let fileContents = fs.readFileSync(file);
    fs.writeFileSync(file, "\ufeff" + fileContents);
}

async function writeCSV() {
    await csvWriter.writeRecords(users);
    // Prepend BOM to file so Excel will display characters correctly.
    prependBOM(outputFile);    
}

writeCSV();

If you don't want to do this, you can import UTF-8 data manually into Excel:

  • Start Excel.
  • Import your csv data using Data->Import External Data->Import Data
  • Select file type "csv", browse to your file and select it.
  • In the import wizard change the File_Origin to "65001 (UTF-8)".
  • You can then click "Transform data" to see your data.
  • You can select close and load to create a new worksheet with your imported data.

Upvotes: 2

Related Questions