CSV to SQL

CSV to SQL

by Mingtao Sun

HTML

Change text to trigger <br/>
<textarea  id="input" rows="20" cols="200">
TISH_IL_FACTOR
ID,HYPOTHESIS_CODE,RH_BOP,CW_ONSET,STP_ACT,STREAMLINE_ACT,ABOVE_BELOW_LINE,PROPAGATE_TYPE,APPLY_GENDER,CREATED_USER,CREATED_TS,LAST_UPDATE_USER,LAST_UPDATE_TS,FACTOR_NAME,FACTOR_TEXT
N,C,C,C,C,C,C,C,C,C,C,C,C,C,C
1,107704,RH,CW,NA,NA,B,NA,M,Data Load,2017-09-04,Data Load,2017-09-04,Inability to obtain appropriate clinical management for tinea of the skin,inability to obtain appropriate clinical management for tinea.
2,107705,BOP,CW,NA,NA,B,NA,F,Data Load,2017-09-04,Data Load,2017-09-04,Inability to obtain appropriate clinical management for tinea of the skin,inability to obtain appropriate clinical management for tinea.
3,107708,RH,CW,VEA,VEA,B,SINGLE,BOTH,Data Load,2017-09-04,Data Load,2017-09-04,Skin maceration,"for tinea corporis, tinea capitis, tinea barbae or tinea pedis only: (ii) having skin maceration at the affected site between two days and four weeks before the clinical worsening of tinea; or   for tinea unguium only: (ii) having skin maceration at the affected site between two days and three months before the clinical worsening of tinea; or"
4,107706,RH ,ONSET,VEA,VEA,A,SINGLE,null,Data Load,2017-09-04,Data Load,2017-09-04,Skin maceration,"for tinea corporis, tinea capitis, tinea barbae or tinea pedis only: (ii) having skin maceration at the affected site between two days and four weeks before the clinical onset of tinea; or                                                                                                                     for tinea unguium only: (ii) having skin maceration at the affected site between two days and three months before the clinical onset of tinea; or"
5,107707,BOP ,ONSET,MRCA,MRCA,A,MULTIPLE,null,Data Load,2017-09-04,Data Load,2017-09-04,Skin maceration,"for tinea corporis, tinea capitis, tinea barbae or tinea pedis only:  (ii) having skin maceration at the affected site between two days and four weeks before the clinical onset of...

JavaScript

$(function() {
	  $('#input').change(function(){
	      dml($(this));      
    });
});

//insert into tableName (columns) values ();

function dml(element){
    var lines = $('#input').val().split("\n");
    var output = '';
    var tableName = lines[0];
    var columns = lines[1];
    var types = lines[2].split(",");
    var sql = '';
    $.each(lines, function(index, value){ 
      if (index > 2){
         var valueClause = getValueClause(value, types);
         sql = "INSERT INTO " + tableName + " ( " + columns  +") VALUES ("+valueClause+");" 
      }
      output += sql + "<br/>";
    });
    $('#output').html(output);
}

function getValueClause(values, typesArr){
  var result = [];
  $.each(values.match(/(".*?"|[^",]+)(?=\s*,|\s*$)/g) || [], function(index, value){       
     var type = typesArr[index];
     var convertedValue = value;
     if(type == 'C'){
         var trimmedValue = $.trim(value);
         trimmedValue = trimLeadingAndTrailingDoubleQuote(trimmedValue);
         trimmedValue = replaceSingleQuoteWithTwoSingleQuote(trimmedValue);
         convertedValue = "'"+trimmedValue+"'";
     }else{
         convertedValue = $.trim(value);
     }
     if (convertedValue == "'null'"){
         convertedValue = 'null';	
     }
     result.push(convertedValue);
  });
  return result.join(",");
}

function trimLeadingAndTrailingDoubleQuote(value){
	if (value.indexOf('"') == 0){
      value = value.substring(1, value.length);
  }
  if (value.lastIndexOf('"') == value.length-1){
      value = value.substring(0, value.length-1);
  }
  return value;
}

function replaceSingleQuoteWithTwoSingleQuote(value){
  return value.replace(/'/g, "''");
}