Knowledgebase
4.10.1 Import CSV File
Posted by LabArchives Support on 04 May 2017 10:49 AM

Quick Links

Requirements for Uploading a CSV File into a Database Widget

Requirements for Uploading a CSV File into a Freezer Box Widget

Index of Error and Warning Messages related to CSV Import

 
How to Upload a CSV into a Widget

In LabArchives you have the ability to import a CSV file to create either a Database or Freezerbox Widget and populate it with your existing data. 

There are a few key advantages to doing this:

1. Importing your CSV’s to create a custom widget allows you to view it directly on the notebook page, rather than viewing it in another window if it were an attachment.
2. Widgets are displayed like an interactive web table, and they include features which make them superior to a raw spreadsheet in many cases.
3. Once a CSV file is imported to create a widget, the widget template can be saved, shared, and reused indefinitely.
4. The CSV upload feature can also be used to create a “blank” database or Freezerbox template. This allows you to create new widgets without actually using the Widget Manager.

If you don’t have any CSV files on hand, remember that any Excel file (or similar spreadsheet file) can usually be saved in CSV format, just be sure to follow the formatting guidelines provided below.

You will find the Import CSV option under your “More” Drop Down menu, within the Add Entry toolbar, as shown below.

After you select this option, you will see a window that looks like the one below:

 
Notice that you have the following prompts here:

1. Import your CSV into a Freezerbox Widget or a Database Widget. The differences will be explained below.
2. Specify your Widget title. This is the title you will look for if you ever want to add a new widget that uses the same template as this one elsewhere in your notebook(s).
3. Choose the CSV file from which you want to import. This file can be stored on your local hard drive or on a network drive your device has access to.

Your CSV file must be formatted in a certain way for it to be successfully imported, however, the requirements depend on whether you select “Freezerbox” or “Database”. Please see the relevant articles on how to format your CSV in each of these cases.

If your CSV does not meet the given requirements, you will see an error message describing the problem after attempting to upload. The system will also provide suggestions in case it finds something that could be formatted better. An example of such suggestions/error messages appears below:

 
Remember that Warning messages are only suggestions, and you do not have to comply with them to successfully upload your CSV into a widget. Please see the error/warnings index for a complete list of all our error and warning messages, and the meaning of each here.

Requirements for Uploading a CSV File into a Database Widget

The column headers in the first row of your spreadsheet will be the names of the fields in your database widget. Therefore, if your spreadsheet looks like this:


 

It will create fields that look like this when you are in entering additional data to the widget after it has been added to the notebook:


 

Tips:

  • Every column of data must have a header in row 1 of the spreadsheet.
  • Do not use duplicate column headers on the spreadsheet.

Using Form Commands

Form commands are appended to column header names with an underscore “_” and allow you to further restrict the field defined by the column header. The following is a list of the allowable

Form Commands. Note that an underscore must be used as shown.

  • _number : This extension limits the input to numbers, including periods and commas. It should be used in an input field of type “text” (the default), not “text area.” Example:
  • ID_number_ mandatory : If used, this indicates that all the rows in the column must contain data.

Further, if you add additional data to the widget within the notebook, the form entry cannot be saved to the notebook unless completed. “_mandatory” can be used on any input field type. Example: Name_mandatory

  • _calculator : If used, this will display a pop-up scientific calculator adjacent to the field when adding data to this field in the widget. This may be used in an input field of type “text”, but not “text area”. Example: Resulting Percentage_calculator
  • _date : By indicating that this is a date field, a pop-up calendar will be displayed when adding data to this field in the widget in your notebook. Example: Create Date_date

Note that multiple extensions can be used together. For example, a field can be named “Mass_number_mandatory” indicating that the field name is “Mass” but it must contain a value (_mandatory)and the value must be numeric (_number) or the spreadsheet cannot be uploaded.
 

Using Form Elements

Form Elements define how data can be entered into the widget once it has been created and added to your notebook. Adding form elements to fields in a CSV file ensures the interactive nature of the field when inputting data into the form that you are creating. Just as with Form Commands, the following list of Form Elements can be appended to a column header with an underscore (_).

Note that an underscore must be used. If a Form Element is not specified the default form is a text box.

  • _text - Text box (this is the default). If you would like the entry field to be a text box when entering additional data to the widget within your notebook, this will be used by default. Example: Unknown Number_text
  • _select - Select from the drop down menu. The unique values in the columns of the .csv will be the choices in the drop-down menu. Example: Group_select
  • _textarea - This is similar to a text box but wraps text to show on multiple lines. Example: Description_textarea
  • _radio - Radio button. The unique values in the columns of the .csv will be the choices of the radio buttons. Note that there is a limit of 20 choices. Example: Vendor_radio

Download a Sample Database CSV File here
 

Requirements for Uploading a CSV File into a Freezer Box Widget

The first column of the .CSV must be the Column/Row header to indicate the column and row for the freezer box cells. The following column headers in the first row of your spreadsheet will be the names of the fields in your freezer box widget. Therefore, if your spreadsheet looks like this:


 

It will create a freezer box that is 3x2 freezer box (3 columns and 2 rows). When you want to add addition cells to the widget, the edit form would look like this:

 
Tips:

  • Every column of data must have a header in row 1 of the spreadsheet.
  • Column 1 of the header row must be used for the Column/Row designation of the freezerbox cells.
  • Do not use duplicate column headers on the spreadsheet.

 

Setting up the Column/Row Header

The first column of this spreadsheet will be used to determine the layout and location of the data in the remaining columns. We use a 2-character letter/number system in which the first character defines the column (typically A, B, C, D, etc.) and the second defines the row (typically 1, 2, 3, etc.). These can be flipped so that columns are numbered and rows are letters. Either way, it is important to have this done correctly so that data to be imported into the cells is the desired location in the widget.
 

Using Form Commands

Form commands are appended to column header names with an underscore “_” and allow you to further restrict the field defined by the column header. The following is a list of the allowable Form Commands. Note that an underscore must be used as shown.

  • _number : This extension limits the input to numbers, including periods and commas. It  should be used in an input field of type “text” (the default), not “text area.” Example: ID_number
  • _ mandatory : If used, this indicates that all the rows in the column must contain data. Further, if you add additional data to the widget within the notebook, the form entry cannot be saved to the notebook unless completed. “_mandatory” can be used on any input field type. Example: Name_mandatory
  • _calculator : If used, this will display a pop-up scientific calculator adjacent to the field when adding data to this field in the widget. This may be used in an input field of type “text”, but not “text area”. Example: Resulting Percentage_calculator
  • _date : By indicating that this is a date field, a pop-up calendar will be displayed when adding data to this field in the widget in your notebook. Example: Create Date_date
  • _color : This will place a small colored square in the cell when the widget is created. When adding or editing cell data in the widget in your notebook, this will display a drop-down list of colors. The colors in the drop down to choose from are:
    • (none)
    • black
    • light blue
    • dark blue
    • brown
    • gray
    • green
    • natural
    • orange
    • pink
    • purple
    • red
    • white
    • Yellow

Note that multiple extensions can be used together. For example, a field can be named “Mass_number_mandatory” indicating that the field name is “Mass” but it must contain a value (_mandatory)and the value must be numeric (_number) or the spreadsheet cannot be uploaded.
 

Using Form Elements

Form Elements define how data can be entered into the widget once it has been created and added to your notebook. Adding form elements to fields in a CSV file ensures the interactive nature of the field when inputting data into the form that you are creating. Just as with Form Commands, the following list of Form Elements can be appended to a column header with an underscore (_).

Note that an underscore must be used. If a Form Element is not specified the default form is a text box.

  • _text - Text box (this is the default). If you would like the entry field to be a text box when entering additional data to the widget within your notebook, this will be used by default. Example: Unknown Number_text


 

  • _select - Select from drop down menu. The unique values in the columns of the .csv will be the choices in the drop down menu. Example: Group_select


 

  • _textarea - This is similar to a text box but wraps text to show on multiple lines. Example: Description_textarea

 
 

  • _radio - Radio button. The unique values in the columns of the .csv will be the choices of the radio buttons. Note that there is a limit of 20 choices. Example: Vendor_radio


 

Download a Sample Freezebox CSV file here  
 
 

(1 vote(s))
Helpful
Not helpful

Comments (0)