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

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

cpshartcpshart Posts: 225Questions: 43Answers: 5

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

This question has accepted answers - jump to:

Answers

  • allanallan Posts: 53,629Questions: 1Answers: 8,318 Site admin
    Answer ✓

    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

  • cpshartcpshart Posts: 225Questions: 43Answers: 5

    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

  • cpshartcpshart Posts: 225Questions: 43Answers: 5

    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);
    }
    
    ?>
    

    JSON returned from the
    server script

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

    Many Thanks Colin

  • allanallan Posts: 53,629Questions: 1Answers: 8,318 Site admin
    Answer ✓

    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

  • cpshartcpshart Posts: 225Questions: 43Answers: 5

    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

Sign In or Register to comment.