title: Improper Functioning of sql to tbl conversion
body: <p>I want to convert a sql file containing insert statements to a corresponding tbl file. I have tries the code given below. It works well but I need a blank line after each record i.e a blank line has to be present in between processing to two insert statements.</p>
  
  <p>Input:</p>
  
  <pre><code> --------------------------------------------------
   PRINT ''
   PRINT 'Inserting data into table post_tran...'
   PRINT ''
   --------------------------------------------------
   GO
  INSERT INTO 
   post_tran(post_tran_id,tran_nr,datetime_tran_local,system_trace_audit_nr,settle_
   amount_rsp,settle_tran_fee_rsp,post_tran_cust_id,prev_post_tran_id,next_post_tra
   n_id,message_type,tran_postilion_originated,sink_node_name,settle_entity_id,batc
   h_nr,tran_completed,tran_type,rsp_code_rsp,auth_type,auth_reason,retrieval_refer
   ence_nr,settle_amount_req,settle_tran_fee_req,settle_currency_code,datetime_req,
   recon_business_date,realtime_business_date,acquiring_inst_id_code) VALUES(  
   1,2,'2002-04-02 19:02:28','008497',4120,10, 2, 0,0,'0200', 0, 'LinkSink', 
   1,1,0,'01','01','00',0,'000000000102',6000,0,'840', '', '2004-02-11 
   12:00:00', '2004-02-11 12:00:00', '2200017000')
  
  
   INSERT INTO post_tran( 
   post_tran_id,tran_nr,datetime_tran_local,system_trace_audit_nr,settle_amount_rsp
   ,settle_tran_fee_rsp,post_tran_cust_id,prev_post_tran_id,next_post_tran_id,messa
   ge_type,tran_postilion_originated,sink_node_name,settle_entity_id,batch_nr,tran_
  
     completed,tran_type,rsp_code_rsp,auth_type,auth_reason,retrieval_reference_nr,se
   ttle_amount_req,settle_tran_fee_req,settle_currency_code,datetime_req,recon_busi
   ness_date,realtime_business_date,acquiring_inst_id_code) VALUES( 2,2,'2002-
   04-02 19:02:28','008497',4120,10, 2, 0,0,'0200', 1, 'LinkSink', 
   1,1,0,'01','01','00',0,'000000000102',6000,0,'840', '', '2004-02-11 
   12:00:00', '2004-02-11 12:00:00', '2200017000')
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>[name] post_tran_id[/name]=[data] 1[/data]
  [name]tran_nr[/name]=[data]2[/data]
  [name]datetime_tran_local[/name]=[data]2002-04-02 19:02:28[/data]
  [name]system_trace_audit_nr[/name]=[data]008497[/data]
  [name]settle_amount_rsp[/name]=[data]4120[/data]
  [name]settle_tran_fee_rsp[/name]=[data]10[/data]
  [name]post_tran_cust_id[/name]=[data] 2[/data]
  [name]prev_post_tran_id[/name]=[data] 0[/data]
  [name]next_post_tran_id[/name]=[data]0[/data]
  [name] post_tran_id[/name]=[data] 2[/data]
  [name]tran_nr[/name]=[data]2[/data]
  [name]datetime_tran_local[/name]=[data]2002-04-02 19:02:28[/data]
  [name]system_trace_audit_nr[/name]=[data]008497[/data]
  [name]settle_amount_rsp[/name]=[data]4120[/data]
  [name]settle_tran_fee_rsp[/name]=[data]10[/data]
  [name]post_tran_cust_id[/name]=[data] 2[/data]
  [name]prev_post_tran_id[/name]=[data] 0[/data]
  [name]next_post_tran_id[/name]=[data]0[/data]
  </code></pre>
  
  <p>Required Output:</p>
  
  <pre><code>[name] post_tran_id[/name]=[data] 1[/data]
  [name]tran_nr[/name]=[data]2[/data]
  [name]datetime_tran_local[/name]=[data]2002-04-02 19:02:28[/data]
  [name]system_trace_audit_nr[/name]=[data]008497[/data]
  [name]settle_amount_rsp[/name]=[data]4120[/data]
  [name]settle_tran_fee_rsp[/name]=[data]10[/data]
  [name]post_tran_cust_id[/name]=[data] 2[/data]
  [name]prev_post_tran_id[/name]=[data] 0[/data]
  [name]next_post_tran_id[/name]=[data]0[/data]
  
  [name] post_tran_id[/name]=[data] 2[/data]
  [name]tran_nr[/name]=[data]2[/data]
  [name]datetime_tran_local[/name]=[data]2002-04-02 19:02:28[/data]
  [name]system_trace_audit_nr[/name]=[data]008497[/data]
  [name]settle_amount_rsp[/name]=[data]4120[/data]
  [name]settle_tran_fee_rsp[/name]=[data]10[/data]
  [name]post_tran_cust_id[/name]=[data] 2[/data]
  [name]prev_post_tran_id[/name]=[data] 0[/data]
  [name]next_post_tran_id[/name]=[data]0[/data]
  </code></pre>
  
  <p>Code:</p>
  
  <pre><code> public class SqlToTblCovertor {
  
  private File source_folder = null;
  private File destination_folder = null;
  private String source_absolute_path = null;
  private String destination_absolute_path = null;
  private String absolute_file_name = null;
  private String absolute_new_file_name = null;
  private List&lt;String&gt; column_name_list = null;
  private List&lt;String&gt; column_values_list = null;
  
  public SqlToTblCovertor(String source_folder_name,
          String destination_folder_name) {
      source_folder = new File(source_folder_name);
      destination_folder = new File(destination_folder_name);
      source_absolute_path = source_folder.getAbsolutePath();
      destination_absolute_path = destination_folder.getAbsolutePath();
      column_name_list = new ArrayList&lt;String&gt;();
      column_values_list = new ArrayList&lt;String&gt;();
   }
  
   public void run() throws IOException {
      validateInputs();
      migrateFiles();
   }
  
   private void validateInputs() {
      if (source_folder.isDirectory() == false) {
          displayMessageAndExit("Source must be a FOLDER");
      } else if (destination_folder.isDirectory() == false) {
          displayMessageAndExit("Destination must be a FOLDER");
      }
   }
  
   private void migrateFiles() throws IOException {
      String[] file_list = source_folder.list();
      for (String file_name : file_list) {
          migrateFileContains(file_name);
      }
   }
  
   private void migrateFileContains(String file_name) throws IOException {
      absolute_file_name = source_absolute_path + File.separator + file_name;
      absolute_new_file_name = destination_absolute_path + File.separator
              + getNewFileName(file_name);
      BufferedReader br = new BufferedReader(new InputStreamReader(
              new FileInputStream(new File(absolute_file_name))));
      String line_info = br.readLine();
      StringBuffer new_query = new StringBuffer("");
      while (line_info != null) {
          new_query.append(convertQuery(line_info));
          line_info = br.readLine();
      }
      FileWriter fw = new FileWriter(new File(absolute_new_file_name));
      fw.write(new_query.toString());
      br.close();
      fw.close();
   }
  
   private String convertQuery(String query) {
      String new_query = "";
      if (query.startsWith("INSERT")) {
          int round_bracket_start = query.indexOf('(');
          int round_bracket_end = query.indexOf(')');
          int round_bracket_start_after_values = query.indexOf('(',
                  round_bracket_end);
          String query_column_name = query.substring(round_bracket_start + 1,
                  round_bracket_end);
          String query_column_values = query.substring(
                  round_bracket_start_after_values + 1, query.length() - 1);
          covertColumnNameList(query_column_name);
          covertColumnValueList(',' + query_column_values + ',');
          new_query = createNewQuery() + "\n";
      }
      column_name_list.clear();
      column_values_list.clear();
      return new_query;
   }
  
   private void covertColumnNameList(String query_column_name) {
      String[] column_list = query_column_name.split(",");
      for (String column_name : column_list) {
          column_name_list.add(column_name);
      }
   }
  
   private void covertColumnValueList(String query_column_values) {
      if (query_column_values.equals(",")) {
          return;
      }
      String column_value = null;
      int comma_index = query_column_values.indexOf(',');
      int next_comma_index = 0;
      if (query_column_values.charAt(comma_index + 1) == '\'') {
          int quote_index = query_column_values.indexOf('\'', comma_index);
          int next_quote_index = query_column_values.indexOf('\'',
                  quote_index + 1);
          next_comma_index = query_column_values.indexOf(',',
                  next_quote_index);
          column_value = query_column_values.substring(comma_index + 2,
                  next_comma_index - 1);
      } else {
          next_comma_index = query_column_values
                  .indexOf(',', comma_index + 1);
          column_value = query_column_values.substring(comma_index + 1,
                  next_comma_index);
      }
      column_values_list.add(column_value);
      covertColumnValueList(query_column_values.substring(next_comma_index));
   }
  
   private String createNewQuery() {
      StringBuffer buffer = new StringBuffer("");
      if (column_name_list.size() != column_values_list.size()) {
          display("Error : " + absolute_file_name);
      } else {
          display("Migrate : " + absolute_file_name);
          for (int index = 0; index &lt; column_name_list.size(); index++) {
              buffer.append(createNewColumn(column_name_list.get(index),
                      column_values_list.get(index)));
          }
      }
      displayFileInformation();
      return buffer.toString();
   }
  
   private void displayFileInformation() {
      display("Column Name :" + column_name_list);
      display("Column Values :" + column_values_list);
      display("\n");
   }
  
   private String createNewColumn(String column_name, String column_value) {
      StringBuffer buffer = new StringBuffer("");
      buffer.append("[name]");
      buffer.append(column_name);
      buffer.append("[/name]=[data]");
      buffer.append(column_value);
      buffer.append("[/data]");
      buffer.append("\r\n");
      return buffer.toString();
   }
  
   private String getNewFileName(String file_name) {
      String new_file_name = "";
      int dot_index = file_name.indexOf('.');
      new_file_name = file_name.subSequence(0, dot_index) + ".tbl";
      return new_file_name;
  }
  
  private static FileWriter log_file = null;
  
  private static void display(String message) {
      try {
          if (log_file == null) {
              File file = new File("d:\\config_migrator_output.log");
              file.deleteOnExit();
              log_file = new FileWriter(file);
          }
          log_file.write(message + "\n");
          log_file.flush();
      } catch (IOException e) {
          System.out.println("Error in generating log file.");
      }
  }
  
  private static void displayMessageAndExit(String message) {
      display(message);
      System.exit(0);
  }
  
  private static void showUsages() {
      displayMessageAndExit("java ConfigMigrator &lt;source-folder&gt; &lt;destination- 
   folder&gt;");
   }
  
   public static void main(String[] args) {
      if (args.length != 2) {
          showUsages();
      }
      SqlToTblCovertor config_migrator = new SqlToTblCovertor(args[0], 
    args[1]);
      try {
          config_migrator.run();
      } catch (IOException e) {
          e.printStackTrace();
      }
    }}
  </code></pre>