- sudsportAsked on January 09, 2016 at 09:05 AM
I want to do data import from excel file keeping submissions dates it seems to work properly but records are empty end submission date is nonsense.
Thanks for your help,
Attached is the Excel input file, file renamed from .xls to .jpg for loading purposePage URL:
- JotForm SupportKevin_GAnswered on January 09, 2016 at 04:55 PM
Unfortunately, I was unable to download the file, you can try uploading it to Dropbox or Google Drive and share us the link.
I also, would suggest you to double-check your Excel file to make sure it's configured properly, you can also find further info on this guide : http://www.jotform.com/help/308-Import-App-Easily-import-your-Excel-or-CSV-data-into-JotForm
Do let us know if you are still unable to import it after following it.
- sudsportAnswered on January 09, 2016 at 05:00 PM
Please find hereunder the link for the file:
- JotForm SupportKevin_GAnswered on January 09, 2016 at 07:20 PM
I have removed the first field from the Excel file, also I noticed you had an incomplete row at the end of the file, I removed it,please delete all the submissions that were imported empty and try importing them from this file : https://www.dropbox.com/s/sb34v2tyyhqyrye/Xfer._VailhauTrail.xls?dl=0
You can see all the imported submissions on this link : https://support.jotform.com/submissions/60087231329957
Hope this will help you.
- sudsportAnswered on January 10, 2016 at 01:38 PM
The idea was to keep initial registration date for the import which I cannot have any more when deleting the first field.
Agreed with uncompleted last line, I'll fix it to on my original file.
I looked to day at my form with imported data, data is now showing up although submision dates are still incoherent. Didi you do anything on this specific form https://eu.jotform.com/submissions/60075466305958 ?
I tried to see your proposed submission form but it is passworded.
I tried another import by setting the Excel field date submission with proper format (YYYY-MM-DD HH:MM:SS) but it still give incoherent dates although records are present now.
- JotForm SupportKevin_GAnswered on January 10, 2016 at 05:23 PM
Take a look at this guide, here is the imported data : https://support.jotform.com/grid/60097085039054
I guess that is not possible to import the submission date, since when you are on the import app, in the given fields there is not any submission date, there are only the fields in your form :
And as explained there, the number of columns should match, when you import the data, it takes the date when you are importing it.
However, try using this date format YYYY-MM-DD HH:MM just with the minutes.
Let us know the result.
- sudsportAnswered on January 10, 2016 at 05:57 PM
There is a tick box allowing to import submissions dates.
I tried without seconds and the result is the same.
I used the import last year when I was still on US server.
- JotForm SupportChriistianAnswered on January 11, 2016 at 03:03 AM
I apologize for the inconvenience. I will investigate this issue further and update you on this thread once there is a possible solution to the incoherent dates.
- JotForm SupportChriistianAnswered on January 11, 2016 at 05:19 AM
I was able to replicate the issue you are reporting. I downloaded the excel file you have shared to us and cloned your form. When I have imported the excel, I noticed that the Submission Data is incorrect.
I checked your the excel file and I noticed that the field in the created_at column is on dd/mm/yy hh:mm and the datatype is Date. Please try to make the created_at column to text format with yyyy-mm-dd hh:mm format value.
I tested this on my end and I was able to successfully import the excel file with proper dates.
Can you try it again and see if it will work now? Please note that you will need to format the created_at column properly for it to work.
Do let us know if the issue still persists.
- sudsportAnswered on January 11, 2016 at 07:11 AM
I made few tries with mentioned format date I still have the same incoherent dates added to this I've discovered that records cannot be edited.
I then tried to load using CSV file, I gate coherent dates but with an offset of +6 hours probably due to CET. I can cope with a work around doing an offset of -6 hours on the source file. Still loaded records cannot be edited for updates.
- BenAnswered on January 11, 2016 at 11:23 AM
Can you please tell us Georges if you have any luck with the modified file made by my colleague?
Do try to upload it and see if it creates the same issue for you.
I would just like to add that as my colleague had said, the submissions date can not be imported. You could however add a hidden datetime field on your form and set the date of form submissions to be added to it instead of being dropped.
- sudsportAnswered on January 11, 2016 at 01:56 PM
I tried to upload the modified file of your colleague and all the records are date as of import date & time.
By the away about data edition, it's OK I think it's only this upload thing which is driving me nuts.
- JotForm SupportKevin_GAnswered on January 11, 2016 at 03:36 PM
I tried the suggestion provided by my colleague Chriistian and I was able to import the submissions with the custom date field.
Try importing the submissions on this file, there are not all the submissions, since it's a test : https://www.dropbox.com/s/47gjt1dh83uwqxg/customDateTest.xls?dl=0
The submissions date is uploading as in the Excel file.
Please, do let us know the result, it should help you.
- sudsportAnswered on January 11, 2016 at 05:03 PM
I have neeb able to load the customDateTest.xls with +6hours difference due to CET
I can see in the XLS file that all submissions have the same date & time stamp.
- BenAnswered on January 11, 2016 at 06:33 PM
If I understood you correctly, and I apologize if I did not, you want to download the xls file as the one that you have shared with us above:
(I have downloaded it and changed the extension to .xls)
Now, once that is done, I have uploaded the same to Google Sheets.
There, we would need to edit the times, but unfortunately this is a bit problematic - we either need to create a new column that will hold this value (use a function +Time(6,0,0) to add to the current time) or we create some function that checks the spreadsheet and replaces the values with the correct ones (both taking some time and having their own sets of issues).
Now I do want to mention that if you are uploading these times to your form, they will not be captured as the form submission date, that should be set as the time of the upload by the import app.
Instead, you should create another DateTime field where you would upload the correct date to.
Hope this helps.
- sudsportAnswered on January 11, 2016 at 06:39 PM
I don't want to eat up your time, I'll the time shift trick.
Thanks for your help.
- JotForm SupportKevin_GAnswered on January 11, 2016 at 08:07 PM
You're welcome on behalf of all involved on this thread.
Please, do let us know if you need more help, we will be more than glad to assist you.