0
231
ViewsParse csv file with merged cells in javascript
I'm working with csv files because we will have that as a source of truth (I know, not the best way but its what marketing familiar with and no time available to build a CMS)
Anyway, lets say I have an excel file with merged cells as bellow:
Plain csv file looks like:
Main ,MainDescription,Secondry,Code
First,First Description,First Secondry 1,CODE 1
,,First Secondry 2,CODE 2
,,First Secondry 3,CODE 3
,,First Secondry 4,CODE 4
,,First Secondry 5,CODE 5
,,First Secondry 6,CODE 6
,,First Secondry 7,CODE 7
,,First Secondry 8,CODE 8
Second,Second Description,Second Secondry 1,CODE 9
,,Second Secondry 2,CODE 10
,,Second Secondry 3,CODE 11
,,Second Secondry 4,CODE 12
,,Second Secondry 5,CODE 13
,,Second Secondry 6,CODE 14
,,Second Secondry 7,CODE 15
,,Second Secondry 8,CODE 16
Is there any "smart" javascript csv parser I can use? I can write the logic myself, I just hope that there is something out there.
Ideally I would expect this to be parsed as bellow:
[
{
"Main": "First",
"MainDescription": "First Description",
"Secondry": [
"First Secondry 1",
"First Secondry 2",
"First Secondry 3",
"..."
],
"Code": ["Code 1", "...", "Code 8"]
},
{
"Main": "Second",
"MainDescription": "Second Description",
"Secondry": [
"Second Secondry 1",
"Second Secondry 2",
"Second Secondry 3",
"..."
],
"Code": ["Code 9", "...", "Code 16"]
}
]
·
Juan Pablo Isaza
1 answers
Answer question0
I've put up a demo below on how you can achieve what you need, it's messy, but you need to find how many empty columns you have so you'll know on which key to group the values into an array:
let text = `Main ,MainDescription,Secondry,Code
First,First Description,First Secondry 1,CODE 1
,,First Secondry 2,CODE 2
,,First Secondry 3,CODE 3
,,First Secondry 4,CODE 4
,,First Secondry 5,CODE 5
,,First Secondry 6,CODE 6
,,First Secondry 7,CODE 7
,,First Secondry 8,CODE 8
Second,Second Description,Second Secondry 1,CODE 9
,,Second Secondry 2,CODE 10
,,Second Secondry 3,CODE 11
,,Second Secondry 4,CODE 12
,,Second Secondry 5,CODE 13
,,Second Secondry 6,CODE 14
,,Second Secondry 7,CODE 15
,,Second Secondry 8,CODE 16
`
let csvToObject = (csvContents = '') => {
let str = csvContents + '';
return str.split(/\n/)
.reduce((acc, itm, idx) => {
let row = itm.split(/,/).map(i => i.trim());
if (idx === 0) {
acc['header'] = row;
acc['list'] = [];
} else {
let h = acc['header']
let findEmptyCols = 0;
let rowLen = row.length;
for (let i = 0; i < rowLen; i++) {
if ((!row[i] || !row[i].length)) {
findEmptyCols++
} else {
break;
}
}
if (row.filter(i => i.trim().length).length) {
if (!findEmptyCols) {
acc.list.push(h.reduce((a, i, x) => {
a[i] = row[x];
return a;
}, {}))
} else {
let last = acc.list[acc.list.length - 1];
h.forEach((i, x) => {
if (x >= findEmptyCols) {
if (!Array.isArray(last[i])) {
last[i] = [last[i]];
}
last[i].push(row[x]);
}
})
acc.list[acc.list.length - 1] = last;
}
}
}
return acc;
}, {}).list;
}
console.log('csvToObject', csvToObject(text));
·
Juan Pablo Isaza
Report
Answer question