zerion

Scroll
Follow

How Do I Import CSV Data?

Import

CSV Data

 


Important Notes

1. Some users have reported issued with Smart Table Search not working properly when loading a CSV file created with MS Office 2007.  We have also found that when creating an CSV file using Excel, there has been more success with using a "CSV (Macintosh)" file.  If you are having trouble please try to create your CSV file using a different application. 

 
"Date" data, must be formatted accordingly (YYYY-MM-DD)
 
**Data needs to be encoded as UTF-8**

2. 
File name cannot contain apostrophe ('); otherwise the CSV file will fail to load. 
 
3. MIME Type Errors
  •  MIME Type errors usually occur because the Excel File is still open when you are trying to upload the CSV file. To eliminate this After creating your CSV file from the Excel Document Close the excel document then you should be able to upload the CSV file.
  • This error also occurs when you are saving the CSV file as MSDOS CSV or WINDOWS CSV. If these CSV file types produce the MIME Type Error please save the file as a CSV Macintosh and this should resolve your issue.
  • This error also can appear when the file size is to large for the Platforms Limit. You will want to split up your csv file into multiple files then upload each one individually.

If none of these suggestions work and you are still receiving the MIME type error please submit a ticket to our Help Desk and they will help resolve the issue.


CSV Upload

This feature allows users to populate tables in iFormBuilder using a CSV formatted file. The header row of the CSV file must match the corresponding data column names in your iFormBuilder table. Use with Smart Table Search to quickly create and add new data to your look-up tables. 


Data Column Names:
 
first_name, last_name, dob, address, email_address


CSV Header Row:
 
first_name, last_name, dob, address, email_address
 

To import data, first verify the CSV schema matches the table, and click on the List View for the table to be populated. Scroll to the bottom of the page to find “Upload CSV Data” button. If you do not have any existing data in the table, you will see the message below.

Screen_Shot_2012-05-03_at_10.55.08_PM.png



To load data into a table that already contains data, follow the same steps, and scroll to the bottom of the page to find the “Upload CSV Data” button. You will be taken to a new page where you can search for the CSV file on your local machine or network. 

There are two options for CSV upload “Fields Terminated by” and “Fields enclosed by”. 

Fields terminated by is used as a delimiter to split between each column. The default is set to “,” (Comma) as it is default when saving an Excel sheet as CSV. 
Fields enclosed by is used when you have data that may contain character that is the same as the Delimiter character. For example, the default delimiter is “,” and you may have an address column that has data as followed: 

ABC Road, Herndon VA 

In this case your CSV data contains commas as part of the data and you will need to enclose the data for that column row combination in quotes. 

“ABC Road, Herndon VA” 


CSV Format 

Upload CSV Data

The easiest way to generate your CSV file to be compatible with your form, is to capture 1 record for the table to be populated. Generate the excel data feed or view and use this as your template. You can choose to populate and upload all rows, or only specific rows if you like. 

Use the Excel Feed if you want to populate sub-form data and connect it to the appropriate parent record. You will need to assign the PARENT_RECORD_ID, PARENT_PAGE_ID, and PARENT_ELEMENT_ID of the sub-form data to have it point to the correct parent. You can find PARENT_ELEMENT_ID in FormBuilder under the element Info tab. 

Use the Excel View format if you don’t care about defining sub-form relationships, and you don’t need to populate the default columns captured for every record (CREATED_DATE, CREATED_BY, CREATED_LOCATION, CREATED_DEVICE_ID, MODIFIED_DATE, MODIFIED_BY, MODIFIED_LOCATION, MODIFIED_DEVICE_ID). 
 
The server will automatically generate this information. Create a template using the same method described above for the Excel Feed. If you only want to upload 1 or two columns you can easily create a new Excel file and use the exact naming convention as your element names in FormBuilder. 




Uploading CSV Data with One Subform Element


Lets say we have this:

Parent Form

Form name: csv_upload_parent

2 Elements

Text element data column name: driver_name

Subform element data column name: routes  

**Make sure the subform element this is set to multiple paging if you are uploading more then one record in the csv file in order for this to perform correctly on the device**

SubForm

Form name: csv_upload_subform

3 Elements

Number element data column name: stop_number

Text element data column name: address

Signature element data column name: sign_here


Parent Form

Step 1:

Go on your device, create and upload one record for the parent form csv_upload_parent

Step 2:

Go to data view on your account and look at the uploaded record on the parent form and download the data feed XLS(first one under data feeds).  Open it and delete everything except row 1 so you will have just the first row filled with the data column names of the elements.

Step 3:

In the first column under ID you will want to put the NEXT record ID of the parent form which will be 2 in this case. So now you should have the first row filled with the data column names and the first column should have 2 under ID.

**The ID Column will always be filled with the next record ID that will be created so if in your list view you have 5 records the ID for your excel file will be 6 ID

Step 4:

You will then want to scroll to the right of the screen and look where it says routes. And you will look at how many records you will be uploading in the sub-form and count the number for. So if you have 5 sub-form records that will be uploaded then put 5 under routes in row 2.

Step 5:

Save this sheet as a CSV file.


SubForm

 

Step 1:

You will want to go into the second tab at the bottom of your excel file that is called csv_upload_subform. Only focus on the stop_number and address columns for now. We want to pre-loaded data into the cells with the data column names you would like to populate. In this example we are populating the stop_number and address columns. You will add your data into these columns.

Step 2:

After this information has been added you will want to look at a column called PARENT_RECORD_ID and you will want to put the same number that you put in as the ID form the parent form CSV file. In this case it will be 2.  This will link this data to the parent record 2.


Step 3:(Only perform this step if the Parent Form includes 2 or more subform elements pointing to the same subform or else continue to step 4)

Note: If you have multiple subforms on the parent form that you would like to populate you must change the parent element ID to the Parent Element ID found in the info tab of the subform element(can be found on info tab of element properties).

 

**As you can see above the Parent Element ID changes after every 5 records and it is changed to the        Element ID of the Subform element on the parent form**

You will also have to make a change to the parent form you are uploading and ensure that the other 2 subform elements are filled with a 5 under the routes_a and 4 under the routes_b.

The 5 under routes_a and the 4 under routes_b is the number of subform records that are linked to the parent form. 

**Save this as your new parent form csv file as you will upload this csv file instead of the one that was saved earlier**

Step 4: 

Save this file as a CSV and prepare to upload!


Uploading
 

 

Step 1:

Select the data tab in FormBuilder and click on the parent form list view button and select CSV upload on the right side of the screen. Browse and find the parent form CSV file.

Step 2:

Go into the list view of the subform and select CSV upload and browse and find the sub-form CSV upload.

**By this point you should have uploaded 2 CSV files; 1 for your parent form and 1 for your subform.

Step 3:

Go into the parent form list view and look at the data and look under the routes column and select the sub-form(1) and it will take you to the list of data that was uploaded to the sub-form.

**This shows that the parent form and the sub-form are linked**

Now your set to go! Happy Data Collecting!

 

 
Can't find what your looking for?  Before submitting a request look here!
    Was this article helpful?
    0 out of 0 found this helpful
    Have more questions? Submit a request

Comments

Powered by Zendesk