Swaraj Giri Swaraj Giri - 4 months ago 30
Node.js Question

What is the correct way to write an image in a csv/xlsx file?

I am trying to write an image, into a csv file. The script writes the file, but the contents of the image are messed up.
Long sequence of

����\u0000\u0010JFIF\u0000\u0001\u0001
.
Can someone point out what i am missing with the encoding or should i be doing something else?

To test:
mkdir so-38711430; cd so-38711430; npm init -y; npm i -S lodash json2csv;


'use strict';

const _ = require('lodash');
const json2csv = require('json2csv');
const fs = require('fs');

let rows = [
{
'id': 1,
'name': '12323',
},
{
'id': 2,
'name': '22323',
},
{
'id': 3,
'name': '323232',
},
{
'id': 4,
'name': '24242',
},
];

// image path is valid
fs.readFile(`./images/2NTSFr7.jpg`, 'utf-8', (err, data) => {
_.each(rows, (item) => {
item.image = data;
});

json2csv({
'data': rows
}, (err, csv) => {
fs.writeFile('file.csv', csv, (err) => {
if (err) throw err;

console.log('file saved');
});
});
});

Answer

It depends how you're rendering the CSV/XLSX document.

In CSV, if you open the document in NotePad, obviously you won't see an image. NotePad won't render images, just text. As others have said, you could write it in base64 string to a CSV, as such using node-base64-image and jQuery-CSV:

import {encode, decode} from 'node-base64-image'; // ES6

// Get the image as base64 string
var base64string = encode("path/to/img");

// Create an arrays object, to convert to CSV
// Note this is an array of arrays. E.g.,
//   For an array: 
//     array = [ "a", "b", "c" ], 
//   you get the third element by doing:
//     array[2] ( = "c" )
//
//   So, for an array of arrays, i.e., 3 arrays containing 3 elements each:
//     arrayOfArrays = [ [ "a", "b", "c" ],[ "d", "e", "f" ], [ "g", "h", "i" ] ]
//   you get the third element by doing (as above):
//     array[2] ( = [ "g", "h", "i" ] )
//   and the third element of the selected array by doing:
//     array[2][2] ( = "i" )
var csvArrays = [
    [ "Image Name",     "Image base64 Data" ], // Example titles row
    [ "name-for-image", base64string ],        // Example data row
];

// Convert arrays to CSV
var csvData = $.csv.fromArrays(csvArrays); 

// Write the file!
fs.writeFile('csvFile.csv', csvData, (err) => {
    if (err) throw err;
    console.log('It\'s saved!');
});

EDIT: Then to decode it from the CSV file, we can get our CSV file we saved earlier:

import {encode, decode} from 'node-base64-image'; // ES6

var csvFile = fs.readFile('csvFile.csv');
var csvDataAsArrays = $.csv.toArrays(csvFile);

// Get the second array from the group of arrays, then the second element from that array, hence `[1][1]`
var csvImgAsBase64 = csvDataAsArrays[1][1];
var imgAsData = decode(csvImgAsBase64);

fs.writeFile("img.jpeg", imgData, function() {
    if (err) throw err;
    console.log('It\'s saved!');
}

In XLSX, you could insert the image as you want to, then open the workbook in Excel to view the image. Here's how to do this using the Excel for Node package:

ws.addImage({
    path: './screenshot2.jpeg',
    type: 'picture',
    position: {
        type: 'absoluteAnchor',
        x: '1in',
        y: '2in'
    }
});

You can also position it relative to cells:

ws.addImage({
    path: './screenshot1.png',
    type: 'picture',
    position: {
        type: 'twoCellAnchor',
        from: {
            col: 1,
            colOff: 0,
            row: 10,
            rowOff: 0
        },
        to: {
            col: 4,
            colOff: 0,
            row: 13,
            rowOff: 0
        }
    }
});
Comments