Load CSV Data as New Form

Q

How to load data file in CSV format with given field names in the file? I don't want to create a new form and define its fields to match the file manually.

✍: FYIcenter.com

A

If you want to load a data file to Form Tools as a new form, you can create an empty form with generic fields first. Then you can update those fields when loading data file based on column headings given in the file.

1. Go to "Forms > Add Form" and click "SELECT" in the "Internal" section to create an "Internal" form.

2. Enter the following to complete the new generic form:

Form Name: Generic Form
Number of Fields: 50 (or more if needed)
Access: Public

3. Make sure the data file is in CSV form with field names in the first line:

fyicenter$ vi Test-Form-Data.csv

Name,Phone,Email,Balance
Joe,123-456-7890,joe@joe.com,39.99
Jay,234-567-8901,jar@jar.com,49.99
Jim,345-678-9012,jim@jim.com,59.99

4. Download and save the following PHP script:

<html><body><pre>
<?php
#- Load-Generic-Form-CSV.php
#- Copyright (c) FYIcenter.com

   if (count($argv)<3) {
      print "Error: Missing form name or file name.\n"
         . "   Run the script again as 'php Load-Generic-Form-CSV.php"
         . " form_name file_name'.\n";
      exit;
   }

   $form_name = $argv[1];
   $file_name = $argv[2];
   $con = mysqli_connect('127.0.0.1','formtools','f0rmt00l$','ft');

   $sql = "SELECT count(*) FROM ft_forms where form_name = '$form_name'";
   $row = get_record($sql);
   if ($row[0]>0) {
      print "Error: Existing form with the same name found.\n";
      exit;
   };

   $file = fopen($file_name, "r") or die(
      "Error: Unable to open the CSV file.");
   $fields = fgetcsv($file);
   if (count($fields)>50) {
      print "Error: CSV file has > 50 fields.\n";
      exit;
   }

   $sql = "SELECT form_id from ft_forms where form_name = 'Generic Form'";
   $row = get_record($sql);
   if (!$row) {
      print "Error: Missing 'Generic Form'.\n";
      exit;
   }
   $form_id = $row[0];

   $rs = mysqli_query($con,"START TRANSACTION");
   $rs = mysqli_query($con,"SET autocommit = OFF");

#- Update form name and field titles
   $sql = "UPDATE ft_forms set form_name='$form_name' where form_id=$form_id";
   $rs = mysqli_query($con,$sql);
   $cols = "";
   for ($i = 1; $i <= count($fields); $i++) {
      $field_name = $fields[$i-1];
      $cols .= "col_$i".",";
      $sql = "UPDATE ft_form_fields set field_title='$field_name'"
         . " where form_id=$form_id and field_name='field$i'";
      $rs = mysqli_query($con,$sql);
   }

#- Insert form data
   while ($fields = fgetcsv($file)) {
      $vals = "";
      for ($i = 1; $i <= count($fields); $i++) {
         $field_value = str_replace("'", "''", $fields[$i-1]);
         $vals .= "'$field_value',";
      }
      $sql = "INSERT ft_form_$form_id ($cols submission_date, last_modified_date)"
         . " VALUES ($vals now(), now())";
      $id = insert_record($sql);
   };

   fclose($file);

   # dump_records("SELECT * from ft_forms where form_id = $form_id");
   # dump_records("SELECT * from ft_form_fields where form_id = $form_id");
   # dump_records("SELECT * from ft_form_$form_id");
   # $rs = mysqli_query($con,"ROLLBACK");

   $rs = mysqli_query($con,"COMMIT");

function insert_record($sql) {
   global $con;
   $id = 0;
   if (mysqli_query($con,$sql)) {
      $id = mysqli_insert_id($con);
      print("Record inserted with id = $id.\n");
   } else {
      print("Error: Could not insert the record.\n");
      print(mysqli_errno($con).": ".mysqli_error($con)."\n");
      die;
   }
   return $id;
}

function get_record($sql) {
  global $con;
  $rs = mysqli_query($con,$sql);
  $row = mysqli_fetch_array($rs);
  mysqli_free_result($rs);
  return $row;
}

function dump_records($sql) {
  global $con;
  $rs = mysqli_query($con,$sql);
  $rows = mysqli_fetch_all($rs);
  mysqli_free_result($rs);
  var_dump($rows);
}
?>
</pre></body></html>

5. Run the PHP script to load the test data file. The "Generic Form" will be renamed and with data loaded from the data file.

fyicenter$ php Load-Generic-Form-CSV "Test Form" Test-Form-Data.csv

Â

â‡’ Add Form to Database Directly

â‡ Data Records of a New Form

â‡‘ Understanding Form Tools Database

â‡‘â‡‘ Form Tools - Frequently Asked Questions

2019-11-08, 1159🔥, 0💬