- CART_dataAsked on June 14, 2016 at 04:02 AM
Hello! I'm trying to import submissions in an xlsx file to my form (see link below) using the app.
The import seems to work OK but when I integrate the form with a GSheet and go to my GDrive to check the results, the following fields are wrong:
- datetime (i.e. created_at)
- date (i.e. 7_Birth Date)
- 5_Full Name is not properly divided into First and Last name fields
-I don't know how to include fields like Signature because althought they are visible in the form they don't have a tag name in properties
-I don't know how to include fields like IP, Submission ID... because they are not visible at all
Can you help me with that?
- JotForm SupportChriistianAnswered on June 14, 2016 at 04:27 AM
I checked your form's submission and I noticed that there are some data which are incorrect. For example, there are submission date with value -0001-11-30 00:00:00.
Also for the Birth Date and Full Name there are incorrect values.
These are also the data that will be imported to you Google Spreadsheet integration. The reason for the issue on your Google Spreadsheet might be that the imported data you made have incorrect values. Please check the imported data and see if the issue is on the imported data itself.
Regarding to your other concern, I moved it to a separate thread so we can better assist you. You can check the threads here:
We will attend to your concern on that thread accordingly.
- CART_dataAnswered on June 14, 2016 at 10:10 PM
The submissions that you see in the form with funny values for Submission Date and Birthdate have been imported from an Excel spreadsheet with proper values/formats. I don't know why correct values become -0001-11-30 00:00:00 or integer numbers!!
The submissions with good values in datetime / date fields where inserted using the form, not imported.
It might help if I send you the xlsx. How can I do that?
- CART_dataAnswered on June 15, 2016 at 12:15 AM
I'll try to explain myself better ...
I've cloned my form (see now https://form.jotform.co/61658506192864).
Then, I've gone to the import app and copied the top line of the xls in a new xlsx spreadsheet.
I've put 10 rows of data, verifying the format in my xlsx matches the format of the xls template:
- 'created at' has a Custom yyyy-mm-dd hh:mm:ss
- 'Birth date' has a Date yyyy-mm-dd
- 'Registration Date' has a Custom yyyy-mm-dd hh:mm
Then, I've imported the file and I've integrated my form with GSheets.
Although the xlsx looks OK:
Datatime and date fields are NOT right in the GSheet:
The last submission has been added using the form and the formats are perfect. The only thing is that the Submission Date is not in my timezone (although 'Today' is in my timezone).
- JotForm SupportChriistianAnswered on June 15, 2016 at 02:00 AM
I did some tests based on your specification and I was able to replicate your issue. The date format comes out wrong when importing the excel.
From what I can see, it seems that excel is changing the format of the date cells which causes the error when importing the data to the form. Instead of 2016-05-24 16:13:08, the date can become 2016-05-24 4:13:08 AM or other formats.
To resolve the issue, please change the format of the cells to text so that they will not be changed by excel.
1. Select all the cells
2. Right click then select Format Cells
3. Select Text and click OK.
4. Save the form then try importing it again using the import app. The format on the submission should now look like the one below.
If the issue still persists, please let us know.