Turn an array of strings to csv format

Question

I have an array of strings:

let transactions = [
    "   Date     ||  Credit   ||  Debit   ||  Balance  ",
    "20/01/2019  ||          ||  9.00  ||  162.00",
    "20/01/2019  ||  90.00  ||          ||  171.00",
    "20/01/2019  ||          ||  9.00  ||  81.00",
    "20/01/2019  ||  90.00  ||          ||  90.00"
];

I turned it to this using transactions.replace(/\s/g, '')

"Date||Credit||Debit||Balance20/01/2019||||9.00||162.0020/01/2019||90.00||||171.0020/01/2019||||9.00||81.0020/01/2019||90.00||||90.00"

and I am trying to remove all the | from it and replace them with commas.

This is actually not working transactions.replace(/|{2,}/g,","):

error Invalid regular expression: /|{2}/: Nothing to repeat

And transactions.replace(/|{+}/g,",") returns:

",D,a,t,e,|,|,C,r,e,d,i,t,|,|,D,e,b,i,t,|,|,B,a,l,a,n,c,e,2,0,/,0,1,/,2,0,1,9,|,|,|,|,9,.,0,0,|,|,1,6,2,.,0,0,2,0,/,0,1,/,2,0,1,9,|,|,9,0,.,0,0,|,|,|,|,1,7,1,.,0,0,2,0,/,0,1,/,2,0,1,9,|,|,|,|,9,.,0,0,|,|,8,1,.,0,0,2,0,/,0,1,/,2,0,1,9,|,|,9,0,.,0,0,|,|,|,|,9,0,.,0,0,"

All I want is a csv format:

"Date,Credit,Debit,Balance,20/01/2019,,9.00,162.00,20/01/2019,90.00,,171.00,20/01/2019,,9.00,81.00,20/01/2019,90.00,,90.00"

You need to escape the |, because it's a special regexp character. — Dan Dascalescu
– Dan Dascalescu, Commented Jan 20, 2019 at 11:22

Blundering Philosopher · Accepted Answer · 2019-01-20 11:43:27Z

You can do this to replace all '||' strings with ',' char:

let output = transactions.split('||').join(',')

It also looks like your first .replace is not adding a '||' between elements in the array, so I'd recommend first using transactions.join('||'), then moving forward with your .replace then my code above.

Here's what the full code would look like:

let transactions = [
    "   Date     ||  Credit   ||  Debit   ||  Balance  ",
    "20/01/2019  ||          ||  9.00  ||  162.00",
    "20/01/2019  ||  90.00  ||          ||  171.00",
    "20/01/2019  ||          ||  9.00  ||  81.00",
    "20/01/2019  ||  90.00  ||          ||  90.00"
];

let output = transactions.join('||') // 1. combine array with 2 '|' chars
    .replace(/\s/g, '')              // 2. remove all space characters
    .split('||').join(',');          // 3. replace '||' str with ','

console.log(output);

If you want to stick with the .replace function, you could use:

let output = transactions.replace(/\|{2}/g, ',');

This matches 2 instances of the '|' character right next to each other, anywhere in the original string, and replaces them with ','. It's very similar to what you had, but as @Dan Dascalescu mentioned, you have to escape the | character in Regex AND you have to get rid of the comma in the {2,} piece, as this will match 2 or more '|' characters.

Here's how that would look in the full code:

let transactions = [
    "   Date     ||  Credit   ||  Debit   ||  Balance  ",
    "20/01/2019  ||          ||  9.00  ||  162.00",
    "20/01/2019  ||  90.00  ||          ||  171.00",
    "20/01/2019  ||          ||  9.00  ||  81.00",
    "20/01/2019  ||  90.00  ||          ||  90.00"
];

let output = transactions.join('||') // 1. combine array with 2 '|' chars
    .replace(/\s/g, '')              // 2. remove all space characters
    .replace(/\|{2}/g, ',');         // 3. replace '||' with ','

console.log(output);

Shidersz · Accepted Answer · 2019-01-20 15:31:14Z

Another approach, could be using the new experimental flatMap() for map every string to an array splited by ||, and then apply trim() to each element of this new array to remove extra spaces, this new array will then be flatten, and finally we can join all elements with , to obtain the required pseudo CSV. Note that this approach will keep the relevant spaces, for example, Date And Time won't be changed to DateAndTime.

let transactions = [
    "   Date And Time     ||  Credit   ||  Debit   ||  Balance  ",
    "20/01/2019 09:57:00  ||          ||  9.00  ||  162.00",
    "20/01/2019 10:20:12 ||  90.00  ||          ||  171.00",
    "20/01/2019 14:17:30 ||          ||  9.00  ||  81.00",
    "20/01/2019 21:00:30 ||  90.00  ||          ||  90.00"
];

let out = transactions.flatMap(str => str.split("||").map(e => e.trim())).join(",");

console.log(out);

However, if you want a real CSV format:

CSV is a delimited data format that has fields/columns separated by the comma character and records/rows terminated by newlines.

Then, you should consider adding the new line (\n) character to each new row of data, like on the next approach:

let transactions = [
    "   Date And Time     ||  Credit   ||  Debit   ||  Balance  ",
    "20/01/2019 09:57:00  ||          ||  9.00  ||  162.00",
    "20/01/2019 10:20:12 ||  90.00  ||          ||  171.00",
    "20/01/2019 14:17:30 ||          ||  9.00  ||  81.00",
    "20/01/2019 21:00:30 ||  90.00  ||          ||  90.00"
];

let out = transactions.map(
    str => str.split("||").map(e => e.trim()).join(",")
).join("\n");

console.log(out);

Cat · Accepted Answer · 2019-01-20 11:29:10Z

0

I don't use regex much but I would expect .replace(/\|+/g,",") to work. (It should find any number of consecutive pipe characters and replace the lot with a single comma.)

answered Jan 20, 2019 at 11:29

Cat

4,2502 gold badges13 silver badges18 bronze badges

Comments

Code Maniac · Accepted Answer · 2019-01-20 12:46:18Z

0

You can join the array elements by || and use replace and in callback use function based on captured group return '' or ,.

This will save you using replace twice.

let str = ["   Date     ||  Credit   ||  Debit   ||  Balance  ", "20/01/2019  ||          ||  9.00  ||  162.00", "20/01/2019  ||  90.00  ||          ||  171.00", "20/01/2019  ||          ||  9.00  ||  81.00", "20/01/2019  ||  90.00  ||          ||  90.00"];

let op = str.join('||').replace(/(\s+)|(\|\|)/g,
   function(match,firstMatch, secondMatch){
   return firstMatch ? '' : ','
})
console.log(op)

answered Jan 20, 2019 at 12:46

Code Maniac

37.9k5 gold badges44 silver badges65 bronze badges

Comments

Roham Tehrani · Accepted Answer · 2019-01-20 13:54:03Z

0

You can use this code:

let output = transactions.join('||').replace(/\s/g, '').replace(/\|\|/g, ',');

edited Jan 20, 2019 at 13:54

answered Jan 20, 2019 at 11:48

Roham Tehrani

3,0298 gold badges38 silver badges51 bronze badges

Collectives™ on Stack Overflow

Turn an array of strings to csv format

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related