Import CSV convert code to ID using table.ajax.json()

cpshart

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

Hi

I need to import CSV where 2 of the fields are code labels which have a corresponding ID to reference another MySQL table. I have been using the support call for ideas

https://datatables.net/forums/discussion/63104

My problem is that I am unable to extract the ID from the json, here is some of the debug below

selectColumns function in client file

function selectColumns ( editor, csv, header ) {
    var selectEditor = new $.fn.dataTable.Editor();
    var fields = editor.order();
 
    for ( var i=0 ; i<fields.length ; i++ ) {
        var field = editor.field( fields[i] );
        
        selectEditor.add( {
            label: field.label(),
            name: field.name(),
            type: 'select',
            options: header,
            def: header[i]
        } );
    }
 
    selectEditor.create({
        title: 'Map CSV fields',
        buttons: 'Import '+csv.length+' records',
        message: 'Select the CSV column you want to use the data from for each field.'
    });

    selectEditor.on('submitComplete', function (e, json, data, action) {
        
        // Use the host Editor instance to show a multi-row create form allowing the user to submit the data.
        editor.create( csv.length, {
            title: 'Confirm import',
            buttons: 'Submit',
            message: 'Click the <i>Submit</i> button to confirm the import of '+csv.length+' rows of data. Optionally, override the value for a field to set a common value by clicking on the field below.'
        } );
console.log('json:',json);
console.log('data:',data);
console.log('action:',action);
 
        for ( var i=0 ; i<fields.length ; i++ ) {
            var field = editor.field( fields[i] );
            var mapped = data[ field.name() ];
console.log('field:',field);
console.log('mapped:',mapped);
            for ( var j=0 ; j<csv.length ; j++ ) {
console.log("400usersTable.ajax.json().options['myFieldName']",usersTable.ajax.json().options[mapped]);     var json = usersTable.ajax.json();
   var jsondata = usersTable.ajax.json().options[stock_id];
console.log('403json value is:',json);              
console.log('405jsondata value is:',jsondata);              
                field.multiSet( j, csv[j][mapped] );
            }
        }
    } );
    
    
}       

I need to extract the portfolio_id and stock_id values from the JSON file

Here is a sample of the import file

"Holdings_id","User", Transaction Date","Transaction Type","Portfolio","Symbol","Quantity","Price","Total"
"766",1","2021-02-07 00:00:00","Buy","TEST","BNKR","2200.00","900","18500.00"

Here is a run of the script below

So in summary I need to get the value of portfolio_id and stock_id

any help much appreciated.

Regards

Colin

allan

Hi Colin,

I need to extract the portfolio_id and stock_id values from the JSON file

Do you mean the CSV file? If not, then what JSON file? The JSON from the DataTable load?

The csv variable contains the data from the CSV file, so you could loop over that and extra the data from there.

Allan

cpshart

Hi Allan

I am importing a CSV file for the children in a parent child datatable as discussed here in your blog, in addition I will need to create a parent row from the child rows.

https://datatables.net/blog/2019-01-11

The CSV file contains a stock line transaction consisting of 2 codes in this case Portfolio TEST and Stock Symbol BNKR

The CSV file format is as follows

dm_holdinglines
holdings_id, id of parent table dm_holdings
user, user id
transaction_date, date of transaction
transaction_type, Buy or Sell
portfolio_id, dm_portfolios.id  (after conversion code to ID)
symbol_id, dm_stocks.id (after conversion code to ID)
quantity, number of shares
price, price of share
total, total amount paid

I can pass the portfolio_id and stock_id in the CSV file and it will import 100% successfully using the file shown below

"Holdings_id","User", Transaction Date","Transaction Type","Portfolio","Symbol","Quantity","Price","Total"
"766",1","2021-02-07 00:00:00","Buy","13","492","2200.00","900","18500.00"

where

dm_portfolios.code = TEST
dm_portfolios.id = 13

dm_stocks.symbol = BNKR
dm_stocks.id = 492

but the user would not know the id value of the portfolio or share, so they would need to use the portfolio code and share code in the CSV file respectively

so I need to convert
portfolio code to portfolio ID
and
stock code to stock ID

before importing the CSV file.

I could use the value of portfolio_id and stock_id in the datatable JSON parent, but ultimately I would not have this data available, as I would like the user to present a single CSV file for import which would create the parents and child rows.

I need to pre process the CSV file and convert the portfolio code and stock code to dm_portfolios.id and dm_stocks.id respectively. I need to pass the codes in an AJAX call which runs a server script to return the JSON with the corresponding codes and ID's is this the right approach

You may know an easier way to achieve this ?

Many Thanks

Colin

cpshart

Hi Allan

I have made progress on this problem, I am getting the ID value of the portfolio code and assigning to variable portfolio_id

My problem is that as the value of portfolio_id returned from the function getIds is lost outside the ajax call. I suspect I need to use a callback in order for the following line to save the value to the fieldvalue variable.

Client and server code shown below, clkient code based upon your Import CSV example.

Can you advise me how if correct I can incorporate a callback into the code, with thanks.

I can provide access to my website if required.

client extract

// declare variables as arrays to retain value outside functions
let portfolio_id = [];
let portfolio_code = '';
let fieldvalue = [];

.... etc

function selectColumns ( editor, csv, header ) {
    var selectEditor = new $.fn.dataTable.Editor();
    var fields = editor.order();
 
    for ( var i=0 ; i<fields.length ; i++ ) {
        var field = editor.field( fields[i] );

        selectEditor.add( {
            label: field.label(),
            name: field.name(),
            type: 'select',
            options: header,
            def: header[i]
        } );
                
                
    }
 
    selectEditor.create({
        title: 'Map CSV fields',
        buttons: 'Import '+csv.length+' records',
        message: 'Select the CSV column you want to use the data from for each field.'
    });

    selectEditor.on('submitComplete', function (e, json, data, action) {
        
        // Use the host Editor instance to show a multi-row create form allowing the user to submit the data.
        editor.create( csv.length, {
            title: 'Confirm import',
            buttons: 'Submit',
            message: 'Click the <i>Submit</i> button to confirm the import of '+csv.length+' rows of data. Optionally, override the value for a field to set a common value by clicking on the field below.'
        } );
        
        for ( var i=0 ; i<fields.length ; i++ ) {                           
                var field = editor.field( fields[i] );            
                var mapped = data[ field.name() ];          
                    
            for ( var j=0 ; j<csv.length ; j++ ) {
            if (mapped == 'Portfolio') {
                let fieldvalue = getIds(csv[j][mapped]);    
            } else {
                    fieldvalue = csv[j][mapped];
            }       
              field.multiSet(j, fieldvalue);                
            }
        }
    } );
    
    
}       
/************************************************************************** 
* CSV Import Section 
***************************************************************************
*/
    
/************************************************************************** 
* call php to get the portfolio_id for a given portfolio code and user_id
***************************************************************************
*/
function getIds(portfolio_code) {
    
    var userid = $('#passuserid').val();    
        $.ajax( {
    url: "../../" + EDITOR_DIR + "/controllers/dview-get_portfolio_id-1.00.php",
            type: "post",
            data: { 
                "userid": userid,
                "portfolio_code": portfolio_code
                  },
            
    success: function (data) {  
        if (data) {
    try {
        if ( data.length > 1 ) {
        myJSON = JSON.parse(data);
            let portfolio_id = myJSON[0].id;            
        }
    }
            
    catch (error) {
        console.error("Crashing while parsing ");
        throw error;
    }                   
        }
        else if (data.trim()) {
        }
    }

    }); 
    };

server script extract
dview-get_portfolio_id-1.00.php

$userid = $_POST['userid'];

$portfolio_code = $_POST['portfolio_code'];

$portfolio_array = [];

// check if variable is NOT blank pass JSON back to client 
if ($portfolio_code  <> "") {

try {
    $pdo = new PDO(strtolower($sql_details['type']) . ":host=" . $sql_details['host'] . ";dbname=" . $sql_details['db'], $sql_details['user'], $sql_details['pass']);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//    echo "Connected successfully" . "\n\n"; 
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }

$result = $pdo->query("
SELECT 
id, 
code 
FROM dm_portfolios
WHERE code = 'TEST' 
AND 
user_id = '1'
LIMIT 1
");

        foreach ($result as $row) {
            array_push( $portfolio_array, array( 
                'id'=>$row['id'], 
                'code'=>$row['code'] 
                ) );
        };
                

echo json_encode($portfolio_array);
}


<?php
>
```
?>


JSON returned from the 
server script

[{"id":"13","code":"TEST"}]
```

Many Thanks Colin

allan

Hi Colin,

Apologies for having not been able to reply back yesterday.

My concern with the approach above is that you have an Ajax call inside two for loops. So there will be fields * csv-rows Ajax calls to your server when the upload happens. With a decent size file that's going to DoS your own server!

I would very much suggest that in submitComplete you make one single Ajax call to the server to get all of the mappings. Then, when that is done (i.e. in its success callback) you would do the loops and lookups to assign the labels, and then the multiSet can finally be done.

Does that make sense / help?

Allan

cpshart

Hi Allan

No worries on the the delay ..

I understand your response and I will definitely take your advice, thanks for the heads up on a possible DoS of the server !!!

I will let you know once I get it working.

Best Regards

Colin