Import CSV

Quick Links

How to Import a CSV File into a Database or Freezerbox Widget

How to format a CSV File for a Database Widget

How to format a CSV File for a Freezerbox Widget

Index of Error and Warning Messages related to CSV Import

 

In LabArchives, you can import a CSV file to create a Database or Freezerbox Widget. Import CSV allows you to display a CSV file directly within a notebook page and it creates a template Widget that you can save, share, and reuse. The advantages of using this feature are:

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 as 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.

 

How to Import a CSV File into a Database or Freezerbox Widget

1. On a page of your lab notebook, select “Import CSV” on the Add Entry toolbar.

2. On the "Import CSV File" window, select Freezer Box or Database and specify a unique Widget Title. 

Note: If you ever want to use the same template for another entry/notebook, you can find it by its specific Widget Title. 

3. Click “Choose File”,select your CSV file, and click “Continue” to upload

4. Your Widget's data form will be automatically created. A preview of this form will appear within the Import CSV window.  If the form is correct, click “Yes, this form is correct”.

5. Now, your freezerbox or database widget will be available as an entry on the page and as a widget in your notebook.

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”. 

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. 

 

How to format a CSV File for a Database Widget

Most Excel files (or similar spreadsheet files) can be saved in CSV format with UTF-8 encoding. For an example CSV file for a Database Widget, please download the file linked below.

Download a Sample Database file at the bottom of this article

NOTE: Every column of data must have a unique header in row 1 of your spreadsheet.Each header will become a column in the Database and a field in the form. 

 

If your spreadsheet looks like this:

It will create fields that look like this when you add new data to 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

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

If you are running into any error or warning messages, you can see a complete list here.

 

How to format a CSV File for a Freezerbox Widget

Most Excel files (or similar spreadsheet files) can be saved in CSV format with UTF-8 encoding. For an example CSV file for a FreezerboxWidget please download the file linked below.

Download a Sample Freezebox CSV file at the bottom of this article

There are two requirements to creating a Freezerbox Widget spreadsheet

1. The first column of the CSV file must be "Column/Row"to indicate the location of the freezer box cell.Every column of data must have a unique header in row 1 of your spreadsheet.Each header will become a field in the FreezerBox Widget.
2. 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.

If your spreadsheet looks like this:

It will create a 4X4 freezer box like this:

When you add additional cells, the edit form would look like this:

 

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. 

 

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

 

 

Index of  Error and Warning Messages

Field	Error Condition	Description
_mandatory	Data is empty	_mandatory fields must contain a value.
_number	Data contains characters other than numbers, periods, commas and dash	_number fields must contain only numbers.
_calculator	Data contains characters other than numbers, periods, commas and dash	_calculator fields must contain only numbers.
_date	Data contains text that is not in a date format	_date fields must be in the format mm/dd/yyyy
_date	Date not in correct format but another date format	_date field is not in the recommended format We suggest you change it to the format mm/dd/yyyy.
_color	Data contains text that is not a color	_color fields must contain a valid color name.
_radio	Data has > 20 choices, suggest to make a ‘select’	_radio fields may have at most 20 choices.  We suggest you make it a _select field.
Misc	The given CSV is not saved with UTF-8 encoding.	Your file must be saved in UTF-8 encoding to work properly. Make sure you are trying to upload is in the proper CSV format and uses UTF-8 encoding
Misc		An error occurred parsing the csv file. <More detailed description of the error: Check that the header for each column is in the correct format Check that the file you are importing is a comma delimited file (other) >
(Freezerbox only) Cell	Cell format invalid	Cell format must be a letter followed by a number, e.g. “A5”
(Freezerbox only) Cell	Cell is a duplicate	Cell is a duplicated in another row

 

Field	Warning Condition	Description
_text	Data has only numbers	We noticed this field contains only numbers.  We suggest you make it a _number or _calculator field.
_select	Data has > 100 choices	We noticed this field has over 100 choices.  We suggest that you verify that the choices listed are correct.
	Header name is empty	Header is blank. We recommend you specify a header name otherwise it will have the name "Column #".