Empty records shown after import of data

Hi,

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,

Regards,
Georges

Attached is the Excel input file, file renamed from .xls to .jpg for loading purpose

Hi,

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.

Thanks.

Hi,

Please find hereunder the link for the file:

https://drive.google.com/file/d/0B5nOKTq5CtU6U05YY1VHSHNwbmc/view?usp=sharing

Regards,
Georges

Hi,

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.

Regards.

Hi Kevin,

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.

Regards,

Georges

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.

Thanks.

Hi Kevin,

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.

Regards,

Georges

Hi,

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.

Regards.

Hi,

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

Hi Chriistian,

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.

Regards,
Georges

Can you please tell us Georges if you have any luck with the modified file made by my colleague?

https://www.dropbox.com/s/sb34v2tyyhqyrye/Xfer._VailhauTrail.xls?dl=0

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.

Hi Ben,

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.

Regards,

Georges

Hi,

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.

Thanks.

Hi Kevin,

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.

Regards,
Georges

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:

http://cms.jotform.com/uploads/answers/answer/sudsport/740476_Xfer._VailhauTrail.jpg

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

Hi Ben,

I don't want to eat up your time, I'll the time shift trick.

Thanks for your help.

Regards,
Georges

Hi Georges,

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.

Regards.