CODEWITHSUNDEEP
sqldatabasecoldfusionbulkinsertbatch-insert
dev
dev

Reputation: 565

How to bulk insert the parsed html table data in database in coldfusion?

I am new in programming and got stuck in a task to insert the parsed html table data into the database. I have tried this link bulk insert in coldfusion but It is not working.

I think my case is different. Here is my code.

sql = '';
records = '';
while(i<rows_length){
     // <cfif i NEQ 1 && home_lineup NEQ 1>,</cfif>
     writeoutput('<tr>');
    for(j=1; j<cols_length; j++){
            stat_cell = get_lineup.select('##sl-away-lineup-table tr:eq(#i#) td:eq(#j#)').text();

        //records =records & #stat_cell# & ',';
         // <cfqueryparam cfsqltype="cf_sql_integer" value="#users[u].firstname#">,
         // <cfqueryparam cfsqltype="cf_sql_varchar" value="#users[u].lastname#">,
         // <cfqueryparam cfsqltype="cf_sql_varchar" value="#users[u].email#">,
         // <cfqueryparam cfsqltype="cf_sql_integer" value="#users[u].firstname#">,
         // <cfqueryparam cfsqltype="cf_sql_integer" value="#users[u].firstname#">,
         // <cfqueryparam cfsqltype="cf_sql_integer" value="#users[u].firstname#">,
         // <cfqueryparam cfsqltype="cf_sql_varchar" value="#users[u].lastname#">,
         // <cfqueryparam cfsqltype="cf_sql_varchar" value="#users[u].email#">,
         // <cfqueryparam cfsqltype="cf_sql_varchar" value="#users[u].lastname#">,
         // <cfqueryparam cfsqltype="cf_sql_varchar" value="#users[u].email#">,
        writeoutput('<td>#stat_cell#</td>'); // this is the cell data I want to batch/bulk insert each cell data 
        records = records& #stat_cell# &','; //it is not a good idea i think to concat like this

    }
}

I have no idea how to know that which cell is of which datatype so that I can pass in query param but the other question is that how to make a query(concat or anything else) so that I can insert in database?

Note: This code is written in cfscript but you can provide suggestion other than cfscript.

Upvotes: 0

Views: 628

Answers (1)

Jules
Jules

Reputation: 2021

Your best bet is to use jquery to convert the table into JSON data. http://www.github.developerdan.com/table-to-json/

I would save that JSON string into a field as is. Only because I would not assume the field names to always match. But at least you'll have the data in an intelligent object you could retrieve later, and manipulate with deserializeJSON() as its own query: https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-c-d/DeserializeJSON.html

Upvotes: 0

Related Questions