merge two columns into one preserving the values of each - Jquery

LelioFaieta
February 8, 2023
165 views
3 votes
3 Answers

I have a table that contains some data that I want to put in an array. The table layout can change from time to time and it is up to the user to decide what are the column header for each column (the column header becomes the key in the array. In the table there can be multiple columns with the same header but in that case the data are in one or the ohter column:

all unique

Date	Description	Amount
First	row	100
Second	row	-50

duplicate header

Date	Description	Amount	Amount
First	row	100
Second	row		-50

Note that the order of the columns is defined by the user depending on the source of the table (a csv he uploaded to my application). The code I have so far works well with unique header and is able to spot the index of the duplicates.
For the duplicate situation the issue is that on row First it will return Amount:'' because it will overwrite the former with the latter. On row Second it will return Amount: '-50' as expected. I need help to improve my code to have row First returning Amount: '100' in the duplicate situation.

My code:

$('.importer').click(function(e){
        e.preventDefault();
        //quante colonne sono rimaste
        var cols= $(".tbi").find("tr:first th").length-1;
        //mappatura di ogni colonna
        var intestColonne = [];
        $('.tbi').find('tr:first th > select').each(function(){
            intestColonne.push($(this).val());
        });
        
        const importi=intestColonne.map((x, i) => x === 'importo' ? i : null).filter(x => x != null);
        //il pezzo commentato fa la stessa cosa della riga sopra
        /*intestColonne.reduce((acc, curr, i) => {
        if (curr === 'importo') acc.push(i);
            console.log(acc);
            return acc;
        }, []);*/
        //leggo ogni colonna
        var movimenti=[];
        $('.tbi > tbody  > tr').each(function(index, tr) { 
            var movimento = [];
            $(this).find('td').each(function(index,td){
                if(index<cols){
                    movimento[intestColonne[index]] = $(this).text();
//here I have to add something to manage the duplicate indexes that are stored in the importi const                             
                }else{
                    movimento['causale']= $(this).children('select').val();
                    movimento['segno']= $(this).children('select').find(':selected').data('segno');
                }
            });
            movimenti.push(movimento);
        });
        console.log(movimenti);
    });

The result for Second row will be like this

Array []
    causale: "3"
    data_cont: "19/01/2021"
    data_val: "19/01/2021"
    descr: "PRELIEVO BANCOMAT"
    importo: "-50"
    length: 0
    segno: "-"

while actually for row First it will be like

0: Array []
    causale: "3"
    data_cont: "19/01/2021"
    data_val: "19/01/2021"
    descr: "PRELIEVO BANCOMAT"
    importo: ""
    length: 0
    segno: "-"

while I’d like to have importo:"100" there

$('.importer').click(function(e){
        e.preventDefault();
        //quante colonne sono rimaste
        var cols= $(".tbi").find("tr:first th").length-1;
        //mappatura di ogni colonna
        var intestColonne = [];
        $('.tbi').find('tr:first th > select').each(function(){
            intestColonne.push($(this).val());
        });
        const importi=intestColonne.map((x, i) => x === 'importo' ? i : null).filter(x => x != null);
        
    //il pezzo commentato fa la stessa cosa della riga sopra
        /*intestColonne.reduce((acc, curr, i) => {
        if (curr === 'importo') acc.push(i);
            console.log(acc);
            return acc;
        }, []);*/
        //leggo ogni colonna
        var movimenti=[];
        $('.tbi > tbody  > tr').each(function(index, tr) { 
            var movimento = [];
            $(this).find('td').each(function(index,td){
                if(index<cols){
                    movimento[intestColonne[index]] = $(this).text();                               
                }else{
                    movimento['causale']= $(this).children('select').val();
                    movimento['segno']= $(this).children('select').find(':selected').data('segno');
                }
            });
            movimenti.push(movimento);
        });
        console.log(movimenti);
    });

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<button class="importer">create array</button>
<table class="tbi">
    <thead>
        <tr>
            <th><select name="tipo_mov0">
                    <option value="">Tipo campo</option>
                    <option value="data_val" selected>Data Valuta</option>
                    <option value="data_cont">Data Cont</option>
                    <option value="descr">Descrizione</option>
                    <option value="importo">Importo</option>
                </select> <i class="fas fa-trash-alt bin"></i></th>
            <th><select name="tipo_mov1">
                    <option value="">Tipo campo</option>
                    <option value="data_val">Data Valuta</option>
                    <option value="data_cont" selected>Data Cont</option>
                    <option value="descr">Descrizione</option>
                    <option value="importo">Importo</option>
                </select> <i class="fas fa-trash-alt bin"></i></th>
            <th><select name="tipo_mov2">
                    <option value="">Tipo campo</option>
                    <option value="data_val">Data Valuta</option>
                    <option value="data_cont">Data Cont</option>
                    <option value="descr" selected>Descrizione</option>
                    <option value="importo">Importo</option>
                </select> <i class="fas fa-trash-alt bin"></i></th>
            <th><select name="tipo_mov4">
                    <option value="">Tipo campo</option>
                    <option value="data_val">Data Valuta</option>
                    <option value="data_cont">Data Cont</option>
                    <option value="descr">Descrizione</option>
                    <option value="importo" selected>Importo</option>
                </select> <i class="fas fa-trash-alt bin"></i></th>
            <th><select name="tipo_mov5">
                    <option value="">Tipo campo</option>
                    <option value="data_val">Data Valuta</option>
                    <option value="data_cont">Data Cont</option>
                    <option value="descr">Descrizione</option>
                    <option value="importo" selected>Importo</option>
                </select> <i class="fas fa-trash-alt bin"></i></th>
            <th>Causale</th>
        </tr>
    </thead>
    <tbody>







        <tr class="text-info">
            <td>19/01/2021</td>
            <td>19/01/2021</td>
            <td>PRELIEVO BANCOMAT</td>
            <td>250.0</td>
            <td></td>
            <td class="text-danger"><select name="causali">
                    <option value="">Scegli</option>
                    <option value="1" data-segno="+">Stipendio</option>
                    <option value="2" data-segno="-">Rata Mutuo</option>
                    <option value="3" data-segno="-">Saldo carta di credito</option>
                    <option value="4" data-segno="+">Interessi Attivi</option>
                    <option value="5" data-segno="-">Interessi Passivi</option>
                    <option value="9" data-segno="-">Spese banca</option>
                    <option value="10" data-segno="-">giro a c/famiglia</option>
                </select></td>
        </tr>
        <tr class="text-info">
            <td>19/01/2021</td>
            <td>16/01/2021</td>
            <td>PAGAMENTO POS </td>
            <td>60.0</td>
            <td></td>
            <td class="text-danger"><select name="causali">
                    <option value="">Scegli</option>
                    <option value="1" data-segno="+">Stipendio</option>
                    <option value="2" data-segno="-">Rata Mutuo</option>
                    <option value="3" data-segno="-">Saldo carta di credito</option>
                    <option value="4" data-segno="+">Interessi Attivi</option>
                    <option value="5" data-segno="-">Interessi Passivi</option>
                    <option value="9" data-segno="-">Spese banca</option>
                    <option value="10" data-segno="-">giro a c/famiglia</option>
                </select></td>
        </tr>
        <tr class="text-info">
            <td>19/01/2021</td>
            <td>16/01/2021</td>
            <td>PAGAMENTO POS </td>
            <td>42.0</td>
            <td></td>
            <td class="text-danger"><select name="causali">
                    <option value="">Scegli</option>
                    <option value="1" data-segno="+">Stipendio</option>
                    <option value="2" data-segno="-">Rata Mutuo</option>
                    <option value="3" data-segno="-">Saldo carta di credito</option>
                    <option value="4" data-segno="+">Interessi Attivi</option>
                    <option value="5" data-segno="-">Interessi Passivi</option>
                    <option value="9" data-segno="-">Spese banca</option>
                    <option value="10" data-segno="-">giro a c/famiglia</option>
                </select></td>
        </tr>
        <tr class="text-info">
            <td>15/01/2021</td>
            <td>15/01/2021</td>
            <td>ADDEBITO SDD</td>
            <td>1501.2</td>
            <td></td>
            <td class="text-danger"><select name="causali">
                    <option value="">Scegli</option>
                    <option value="1" data-segno="+">Stipendio</option>
                    <option value="2" data-segno="-">Rata Mutuo</option>
                    <option value="3" data-segno="-">Saldo carta di credito</option>
                    <option value="4" data-segno="+">Interessi Attivi</option>
                    <option value="5" data-segno="-">Interessi Passivi</option>
                    <option value="9" data-segno="-">Spese banca</option>
                    <option value="10" data-segno="-">giro a c/famiglia</option>
                </select></td>
        </tr>
        <tr class="text-info">
            <td>15/01/2021</td>
            <td>13/01/2021</td>
            <td>PAGAMENTO POS </td>
            <td>167.31</td>
            <td></td>
            <td class="text-danger"><select name="causali">
                    <option value="">Scegli</option>
                    <option value="1" data-segno="+">Stipendio</option>
                    <option value="2" data-segno="-">Rata Mutuo</option>
                    <option value="3" data-segno="-">Saldo carta di credito</option>
                    <option value="4" data-segno="+">Interessi Attivi</option>
                    <option value="5" data-segno="-">Interessi Passivi</option>
                    <option value="9" data-segno="-">Spese banca</option>
                    <option value="10" data-segno="-">giro a c/famiglia</option>
                </select></td>
        </tr>
        <tr class="text-info">
            <td>11/01/2021</td>
            <td>06/01/2021</td>
            <td>PAGAMENTO UTENZE</td>
            <td>20.0</td>
            <td></td>
            <td class="text-danger"><select name="causali">
                    <option value="">Scegli</option>
                    <option value="1" data-segno="+">Stipendio</option>
                    <option value="2" data-segno="-">Rata Mutuo</option>
                    <option value="3" data-segno="-">Saldo carta di credito</option>
                    <option value="4" data-segno="+">Interessi Attivi</option>
                    <option value="5" data-segno="-">Interessi Passivi</option>
                    <option value="9" data-segno="-">Spese banca</option>
                    <option value="10" data-segno="-">giro a c/famiglia</option>
                </select></td>
        </tr>
        <tr class="text-info">
            <td>08/01/2021</td>
            <td>05/01/2021</td>
            <td>PAGAMENTO POS </td>
            <td>7.88</td>
            <td></td>
            <td class="text-danger"><select name="causali">
                    <option value="">Scegli</option>
                    <option value="1" data-segno="+">Stipendio</option>
                    <option value="2" data-segno="-">Rata Mutuo</option>
                    <option value="3" data-segno="-">Saldo carta di credito</option>
                    <option value="4" data-segno="+">Interessi Attivi</option>
                    <option value="5" data-segno="-">Interessi Passivi</option>
                    <option value="9" data-segno="-">Spese banca</option>
                    <option value="10" data-segno="-">giro a c/famiglia</option>
                </select></td>
        </tr>
        <tr class="text-info">
            <td>07/01/2021</td>
            <td>06/01/2021</td>
            <td>PRELIEVO BANCOMAT</td>
            <td>250.0</td>
            <td></td>
            <td class="text-danger"><select name="causali">
                    <option value="">Scegli</option>
                    <option value="1" data-segno="+">Stipendio</option>
                    <option value="2" data-segno="-">Rata Mutuo</option>
                    <option value="3" data-segno="-">Saldo carta di credito</option>
                    <option value="4" data-segno="+">Interessi Attivi</option>
                    <option value="5" data-segno="-">Interessi Passivi</option>
                    <option value="9" data-segno="-">Spese banca</option>
                    <option value="10" data-segno="-">giro a c/famiglia</option>
                </select></td>
        </tr>
        <tr class="text-info">
            <td>05/01/2021</td>
            <td>05/01/2021</td>
            <td>BONIFICO</td>
            <td></td>
            <td>900.0</td>
            <td class="text-danger"><select name="causali">
                    <option value="">Scegli</option>
                    <option value="1" data-segno="+">Stipendio</option>
                    <option value="2" data-segno="-">Rata Mutuo</option>
                    <option value="3" data-segno="-">Saldo carta di credito</option>
                    <option value="4" data-segno="+">Interessi Attivi</option>
                    <option value="5" data-segno="-">Interessi Passivi</option>
                    <option value="9" data-segno="-">Spese banca</option>
                    <option value="10" data-segno="-">giro a c/famiglia</option>
                </select></td>
        </tr>

    </tbody>
</table>

If you run the above snippet you will end up that only the last row will have a value for "Importo". All the other will have nothing. 250.0 in the first row will be overwritten by the second "Importo" column that is empty

Answers

Chosen as BEST ANSWER

I spotted the solution by myself:

$(this).find('td').each(function(index,td){
                if(index<cols){
                    if(importi.includes(index)){
                        if($(this).text()!=''){
                            movimento[intestColonne[index]] = $(this).text();
                        }
                    }else{
                        movimento[intestColonne[index]] = $(this).text();
                    }
                }else{
                    movimento['causale']= $(this).children('select').val();
                    movimento['segno']= $(this).children('select').find(':selected').data('segno');
                }
            });

If I am on one of the columns marked as "Importo" if(importi.includes(index)){ and the cell is not empty if($(this).text()!=''){ then I push the value. Otherwise I will skip the cell. I had this in mind but was not able to write it on code before.

(Edit)

- DimitriosTsalkakis
- February 8, 2023 at 4:53 pm
- 0 votes
0
From my understanding, when you move to next cell, you want to grab the cell text value UNLESS it is empty and there is already a non empty grabbed value for this header.
So, the line
```
movimento[intestColonne[index]] = $(this).text();
```
should become
```
var oldText = movimento[intestColonne[index]];
var newText = $(this).text();
if (newText || !oldText) movimento[intestColonne[index]] = newText;
```
Login or Signup to reply.

Don’t take this the wrong way, but I still have trouble with your explanations.
I nevertheless wrote this piece of code which I hope will help you (?)

const
  TbHead = document.querySelector('table.tbi thead')
, TbBody = document.querySelector('table.tbi tbody')
  ;
btMergeCols.onclick =_=>
  {
  let cols = [...TbHead.querySelectorAll('th select[name^="tipo_mov"]' )]
    .map( selec => ({ lib:selec.value, col: selec.closest('th').cellIndex, smovN: selec.name }))
    .sort( (a,b)=> b.lib.localeCompare(a.lib) || b.col-a.col); // simplify test order.

  //console.log( JSON.stringify(cols,0,2)); 

  cols.forEach( (curElm,i,{[i+1]:nxtElm}) => 
    {
    if (!!nxtElm && curElm.lib===nxtElm.lib )
      {
      TbHead.rows[0].deleteCell(nxtElm.col);     console.log( `${curElm.smovN} (${curElm.lib}) is removed (duplicate)` );

      for (let r = 0; r < TbBody.rows.length; r++) 
        {
        let CurVal = TbBody.rows[ r ].cells[ curElm.col ].textContent.trim();

        if (CurVal !== '')
          TbBody.rows[ r ].cells[ nxtElm.col ].textContent = CurVal;

        TbBody.rows[r].deleteCell(curElm.col);
        }
      }
    })
 
  // create array part
  let movimenti = []
    , finalCols = [...TbHead.querySelectorAll('th select[name^="tipo_mov"]' )]
        .map( selec => ({ lib:selec.value, col: selec.closest('th').cellIndex }))
    ;
  [...TbBody.rows].forEach( row => 
    {
    let elm = {};
    movimenti.push(elm);
    finalCols.forEach( ({lib,col})=>
      {
      elm[lib] = row.cells[col].textContent.trim();
      });
    });

  console.log( JSON.stringify(movimenti,0,2 ));
  }

body {
  font-family : Arial, Helvetica, sans-serif;
  font-size   : 14px;
  margin      : 1rem;
  }
table {
  border-collapse  : separate;
  border-spacing   : 1px;
  background-color : lightslategrey;
  }
tr { background-color: cadetblue;  }
td { background-color: whitesmoke; padding: .2em .5em; }

<button id="btMergeCols"> merge duplicates columns / create array </button>
<br><br>

<table class="tbi">
  <thead>
    <tr>
      <th>
        <select name="tipo_mov0">
          <option value="">Tipo campo</option>
          <option value="data_val" selected>Data Valuta</option>
          <option value="data_cont">Data Cont</option>
          <option value="descr">Descrizione</option>
          <option value="importo">Importo</option>
        </select> <i class="fas fa-trash-alt bin"></i></th>
      <th>
        <select name="tipo_mov1">
          <option value="">Tipo campo</option>
          <option value="data_val">Data Valuta</option>
          <option value="data_cont" selected>Data Cont</option>
          <option value="descr">Descrizione</option>
          <option value="importo">Importo</option>
        </select> <i class="fas fa-trash-alt bin"></i></th>
      <th>
        <select name="tipo_mov2">
          <option value="">Tipo campo</option>
          <option value="data_val">Data Valuta</option>
          <option value="data_cont">Data Cont</option>
          <option value="descr" selected>Descrizione</option>
          <option value="importo">Importo</option>
        </select> <i class="fas fa-trash-alt bin"></i></th>
      <th>
        <select name="tipo_mov4">
          <option value="">Tipo campo</option>
          <option value="data_val">Data Valuta</option>
          <option value="data_cont">Data Cont</option>
          <option value="descr">Descrizione</option>
          <option value="importo" selected>Importo</option>
        </select> <i class="fas fa-trash-alt bin"></i></th>
      <th>
        <select name="tipo_mov5">
          <option value="">Tipo campo</option>
          <option value="data_val">Data Valuta</option>
          <option value="data_cont">Data Cont</option>
          <option value="descr">Descrizione</option>
          <option value="importo" selected>Importo</option>
        </select> <i class="fas fa-trash-alt bin"></i></th>
      <th>Causale</th>
    </tr>
  </thead>
  <tbody>
    <tr class="text-info">
      <td>19/01/2021</td>
      <td>19/01/2021</td>
      <td>PRELIEVO BANCOMAT</td>
      <td>250.0</td>
      <td></td>
      <td class="text-danger">
        <select name="causali">
          <option value="">Scegli</option>
          <option value="1" data-segno="+">Stipendio</option>
          <option value="2" data-segno="-">Rata Mutuo</option>
          <option value="3" data-segno="-">Saldo carta di credito</option>
          <option value="4" data-segno="+">Interessi Attivi</option>
          <option value="5" data-segno="-">Interessi Passivi</option>
          <option value="9" data-segno="-">Spese banca</option>
          <option value="10" data-segno="-">giro a c/famiglia</option>
        </select>
      </td>
    </tr>
    <tr class="text-info">
      <td>19/01/2021</td>
      <td>16/01/2021</td>
      <td>PAGAMENTO POS </td>
      <td>60.0</td>
      <td></td>
      <td class="text-danger">
        <select name="causali">
          <option value="">Scegli</option>
          <option value="1" data-segno="+">Stipendio</option>
          <option value="2" data-segno="-">Rata Mutuo</option>
          <option value="3" data-segno="-">Saldo carta di credito</option>
          <option value="4" data-segno="+">Interessi Attivi</option>
          <option value="5" data-segno="-">Interessi Passivi</option>
          <option value="9" data-segno="-">Spese banca</option>
          <option value="10" data-segno="-">giro a c/famiglia</option>
        </select>
      </td>
    </tr>
    <tr class="text-info">
      <td>19/01/2021</td>
      <td>16/01/2021</td>
      <td>PAGAMENTO POS </td>
      <td>42.0</td>
      <td></td>
      <td class="text-danger">
        <select name="causali">
          <option value="">Scegli</option>
          <option value="1" data-segno="+">Stipendio</option>
          <option value="2" data-segno="-">Rata Mutuo</option>
          <option value="3" data-segno="-">Saldo carta di credito</option>
          <option value="4" data-segno="+">Interessi Attivi</option>
          <option value="5" data-segno="-">Interessi Passivi</option>
          <option value="9" data-segno="-">Spese banca</option>
          <option value="10" data-segno="-">giro a c/famiglia</option>
        </select>
      </td>
    </tr>
    <tr class="text-info">
      <td>15/01/2021</td>
      <td>15/01/2021</td>
      <td>ADDEBITO SDD</td>
      <td>1501.2</td>
      <td></td>
      <td class="text-danger">
        <select name="causali">
          <option value="">Scegli</option>
          <option value="1" data-segno="+">Stipendio</option>
          <option value="2" data-segno="-">Rata Mutuo</option>
          <option value="3" data-segno="-">Saldo carta di credito</option>
          <option value="4" data-segno="+">Interessi Attivi</option>
          <option value="5" data-segno="-">Interessi Passivi</option>
          <option value="9" data-segno="-">Spese banca</option>
          <option value="10" data-segno="-">giro a c/famiglia</option>
        </select>
      </td>
    </tr>
    <tr class="text-info">
      <td>15/01/2021</td>
      <td>13/01/2021</td>
      <td>PAGAMENTO POS </td>
      <td>167.31</td>
      <td></td>
      <td class="text-danger">
        <select name="causali">
          <option value="">Scegli</option>
          <option value="1" data-segno="+">Stipendio</option>
          <option value="2" data-segno="-">Rata Mutuo</option>
          <option value="3" data-segno="-">Saldo carta di credito</option>
          <option value="4" data-segno="+">Interessi Attivi</option>
          <option value="5" data-segno="-">Interessi Passivi</option>
          <option value="9" data-segno="-">Spese banca</option>
          <option value="10" data-segno="-">giro a c/famiglia</option>
        </select>
      </td>
    </tr>
    <tr class="text-info">
      <td>11/01/2021</td>
      <td>06/01/2021</td>
      <td>PAGAMENTO UTENZE</td>
      <td>20.0</td>
      <td></td>
      <td class="text-danger">
        <select name="causali">
          <option value="">Scegli</option>
          <option value="1" data-segno="+">Stipendio</option>
          <option value="2" data-segno="-">Rata Mutuo</option>
          <option value="3" data-segno="-">Saldo carta di credito</option>
          <option value="4" data-segno="+">Interessi Attivi</option>
          <option value="5" data-segno="-">Interessi Passivi</option>
          <option value="9" data-segno="-">Spese banca</option>
          <option value="10" data-segno="-">giro a c/famiglia</option>
        </select>
      </td>
    </tr>
    <tr class="text-info">
      <td>08/01/2021</td>
      <td>05/01/2021</td>
      <td>PAGAMENTO POS </td>
      <td>7.88</td>
      <td></td>
      <td class="text-danger">
        <select name="causali">
          <option value="">Scegli</option>
          <option value="1" data-segno="+">Stipendio</option>
          <option value="2" data-segno="-">Rata Mutuo</option>
          <option value="3" data-segno="-">Saldo carta di credito</option>
          <option value="4" data-segno="+">Interessi Attivi</option>
          <option value="5" data-segno="-">Interessi Passivi</option>
          <option value="9" data-segno="-">Spese banca</option>
          <option value="10" data-segno="-">giro a c/famiglia</option>
        </select>
      </td>
    </tr>
    <tr class="text-info">
      <td>07/01/2021</td>
      <td>06/01/2021</td>
      <td>PRELIEVO BANCOMAT</td>
      <td>250.0</td>
      <td></td>
      <td class="text-danger">
        <select name="causali">
          <option value="">Scegli</option>
          <option value="1" data-segno="+">Stipendio</option>
          <option value="2" data-segno="-">Rata Mutuo</option>
          <option value="3" data-segno="-">Saldo carta di credito</option>
          <option value="4" data-segno="+">Interessi Attivi</option>
          <option value="5" data-segno="-">Interessi Passivi</option>
          <option value="9" data-segno="-">Spese banca</option>
          <option value="10" data-segno="-">giro a c/famiglia</option>
        </select>
      </td>
    </tr>
    <tr class="text-info">
      <td>05/01/2021</td>
      <td>05/01/2021</td>
      <td>BONIFICO</td>
      <td></td>
      <td>900.0</td>
      <td class="text-danger">
        <select name="causali">
          <option value="">Scegli</option>
          <option value="1" data-segno="+">Stipendio</option>
          <option value="2" data-segno="-">Rata Mutuo</option>
          <option value="3" data-segno="-">Saldo carta di credito</option>
          <option value="4" data-segno="+">Interessi Attivi</option>
          <option value="5" data-segno="-">Interessi Passivi</option>
          <option value="9" data-segno="-">Spese banca</option>
          <option value="10" data-segno="-">giro a c/famiglia</option>
        </select>
      </td>
    </tr>
  </tbody>
</table>

Please signup or login to give your own answer.

Click here to cancel reply.

merge two columns into one preserving the values of each – Jquery

Answers