Import Workflow

To convert a spreadsheet simply drag and drop a valid file over the shaded upload box located on the left-hand side of the screen. RackForms processes the file and displays a live preview of the imported data.

At this point we'll select our conversion properties. The default is to convert the file into a fully-featured form, complete with database table and submission page, along with backfilling the spreadsheet data into the newly created database table.

We have full control over the destination database. However, if no values are supplied to the Database Settings area we simply use the "default" database RackForms is installed too.

If we use default settings when we press the Start Import Process button RackForms:

• Generates a form, complete with Submission page that contains an SQL+ module for adding new records.
• Updates the database to contain the destination table in the same format as the original spreadsheet.
• Loops through the spreadsheet and backfills all data from it to the newly created table.

When complete the import window closes and we can start editing our form. However, we have a few key options that help us customize the process if needed:

Action

The default option is to perform all actions, however we can also choose to just build a form based on the file. It's key to note that we can always use the SQL+ Module later on to create the database table and SQL+ insert item for us.

Insert Mode

This option allows us to create a new job (the default), or just append the created form page to the existing job.

Job Name

This field is automatically populated based on the uploaded file's name, but we can change it as needed.

Worksheet

Displays the currently selected worksheet, which can then be changed to any other sheet present in the file.

Important Notes And Considerations

Generally speaking the import process depends heavily on the quality of the source file. RackForms attempts to make smart decisions based on the data found using the general guidelines below:

• Required Fields — RackForms designates columns with values for all rows as Required in the form, and as NOT NULL in any generated database table.
  
  
• String Values and String Lengths — As your spreadsheet is parsed RackForms takes the highest-character count value found and sets that as the database table's VARCHAR column length. If that column has empty fields it will not be set as required, though RackForms still prevents entry of longer values on the browser by setting the Validation > Max Character property. If all rows are blank for a given column RackForms sets that field as a VARCHAR(60) along with Max Character of 60. Thus, the data present in the Spreadsheet during import plays a critical role in determining the meta-properties of each column and form input field.
  
  
• Data Types — RackForms inspects all rows of each column and if a string value is found, that column will be set as a string (VARCHAR or TEXT) in the generated database. This happens even if that column also contains boolean or numeric data. The only way a column will be treated as a pure number or boolean field is when all rows of data are that same type.
  
  One small modification to this rule is when a text columns contains a string longer than 60 characters, that field is set as TEXT in the database and as a text-area field in RackForms.
  
  
• Database Truncate — All destination tables are TRUNCATED before we backfill data. This means we'll want to be sure the target table doesn't contain values we wish to retain.
  
  
• Renaming — File and column remove non-characters and spaces, and capitalize each word's first character.
  
  
• Database Delta — When we run a database action we TRUNCATE all data and sync the databases columns to match the input spreadsheet. This means if a column exists in the database but not in the source spreadsheet it will be removed, and vice-versa.
  
  
• Worksheets — At this time RackForms only converts one sheet into a form at a time. However, we can select which sheet we'd like to convert via the Worksheet dropdown (if more than one sheet exists).