UTF-8 encoding during synch Google Sheets with Excel 2016

Hi!
I have a multilanguage form here (with hungarian, english and spanish translations):
https://eu.jotform.com//?formID=63073762797367

I integrated it with my Google Sheets and the connection to synch is working correctly.

I attached my Google Sheets/JotForm sheet to a sheet of my Excel 2016 workbook and I use a special VBA code to edit and process all data (fields) and copy them to another worksheet using conditions, calculations and special formatting.

Until today it worked perfectly: the submitted data appeared on Google Sheets and then imported immediately to my Excel workbook, then my VBA process could do everything.

Now, today evening some new submissions detected and I tried to import them... but:

After synching my Excel, all of data on sheets that connected to Google has been changed and the character coding is incorrect. Only in my Excel worksheet!
My default language in JotForm is hungarian, so I use many special character eg. í, á, ű etc.
Until now it was not problem, these characters appeared correctly both in Google and Excel.

Now in Google the text is perfect, it uses hungarian characters.
The pdf (sent by JotForm to my email address) is also correct, uses shows these special characters.
The Excel sheets, after synching, looks like a total chaos, all of special hungarian characters disappeared and changed to meaningless strings.

For example:

The hungarian word "Csomagszállítás" (= "package transport") has been changed to "Csomagszállítás" and it is not able to use it by VBA process, conditions are not working - so the form process is not working.
All of previous (already processed) data changed to doodle also.

(If I would get separated csv or xls copy of all submission, it would be a great option, but now this synch is the only solution to import and process any submission by Excel automatically)

Please help us with any advice, tips, suggestions to solve this trouble that is a great trouble for us.

Thanks

Laci

My first post includes a screenshot of the incorrect look of sheet.
Here is the correct format:

Hi there,

I am not sure what VBA code you are using to transfer data from Google Spreadsheet to Excel so I am not able to test the transfer and find where the problem is. If the data displays correctly in the Google Spreadsheet connected with your form, it is possible that the error is somewhere between the transfer of your data from Google Spreadsheet to Excel.

Have you tried encoding your Excel file to UTF-8? Please see https://help.surveygizmo.com/help/encode-an-excel-file-to-utf-8-or-utf-16 and check if the solution there works for your Excel file.

Our apologies for the spam that you have received a while ago. They look like not a bot, but a group of people who wants to get more traffic by spamming forums.  Please just disregard. 

Hi!

I don't really understand answer by پرورش قارچ :-)

Thanks for your tip, Chriistian!
I tried to solve this trouble using by method suggested by your URL - but nothing changed.

1.) I see all of submissions in correct format by viewing the JotForm's database itself
2.) I see all of submissions in correct format by viewing the Google Sheets on the web
3.) I see ALL of submissions in INCORRECT format in Excel sheet, after synching online with Google (all of previous, earlier imported data has been changed to invalid format also). Because of it, I can't use my VBA code to process the new submissions, the code can't recognize the "words" include invalid characters, parts of the strings
4.) I see ALL of earlier submissions in correct format on another sheet of the same Excel workbook, where the VBA process copy them after processing (there are no any change to invalid format)
5.) Everything was OK until yesterday's noon
6.) I did not (!) change or modify yesterday in synching settings neither in Excel's settings neither in VBA code
6.) Since yesterday evening I see all of submissions in invalid format, UTF-8 recoding of sheet doesn't solve it, re-synching brings data in invalid format again and again etc.

It is important: I did not change ANYTHING in JotForm, neither in Google, Excel, VBA etc.
The synching worked perfectly until yesterday - and now I can't use my Excel database and data processes based on JotForm.

Anybody else has a good tip for me?
What have (can) I change in Excel to show correctly these UTF-8 characters?

It is very important and urgent for me, for us.

Thanks

Laci

How exactly do you sync integrated google spreadsheet data with your excel file?

You may also do a test with some brand new form to see if the new form and new spreadsheet is affected by the same issue.

Thanks for your tip...

And it is a surprise.

I made a very simple new form (https://form.jotformeu.com/70273916798369) that includes special UTF-8 characters both in labels and options.
Then I integrated it to the same Google Sheet account (of course as a new sheet).
I filled a form in the preview, submitted it and it appeared on Google inmediately.
I set up my SAME Excel workbook (by opening a brand new empty sheet and using the same synch option!) - and the result is correct, all of characters appeared perfectly.
Then I refreshed my original sheet (that connected to the main form), but the result is bad, the text is unreadable, there is no change.

What is it?
The only different between two forms is the first "main" form has three language, default is hungarian, optional is English or Spanish, and now the "test" form has got only one language without any setup process of language.

Have you any idea what is the solution?

(I have to add an information more: yesterday afternoon I inserted my form into a Facebook Page as your guide shows it. It is working correctly. Can it cause this trouble? This is the only modification between the times of my system worked correctly and stopped to working correctly)

Thanks

Laci

Hi there,

The languages in your original form may be causing the issue. Have you tried removing the other languages in your form so that only one language is left? Please see if it works.

Still, we have no idea how you are syncing your Google Spreadsheet with your Excel file so if you could share with us more information about that, it would be great.

Regards.

Hi again,

I made a clone of my main form, removed English and Spanish languages and then integrated it and set up to synch into a new, empty sheet of my existed Excel workbook.

The synching now is OK, the characters look correctly.

I'm so disappointed.
My original form has more than 120 fields (+ options of dropdowns and radio buttons etc.), and has translations to 2 more languages.

If I make 3 separated forms to 3 separated languages, any modification will cause much working on all of forms... And I can synch to one sheet only one form in one time...
If we get 3 submissions in 3 languages, it will cause 3 process to synch to 3 different sheets and needs 3 different VBA code to process them into the formatted database-sheet.

And finally I don't understand that the original 3-language form worked perfectly until a point of the previous day and then it stopped and changed to a damaged synch process.

To your question about synching method:
I set up my Google Sheet to 'Public on the web' and the Excel sheet to get data from the web, including URL that Google generated for publish. The I set up the first field of the sheet to copy the source from there - and everything is OK. This method is working correctly with my test-form, with main form single-language edition, but it is not working correctly with my 3-language main form (since yesterday afternoon).
I don't understand and accept it.

I try to find any workaround, manually copy from Google or JotForm's Excel-report etc., but all of these method cause other troubles because of different format (the order, data fromat etc. are not the same as on Google Sheets and needs modification of my VBA process).

I think this situation is very bad and I'm so frustrated...

Thanks

Laci

Hi there,

We are sorry for the inconvenience this has caused you. However, our integration with Google Spreadsheet covers the workflow of sending the data up to the online spreadsheet only. We are also not sure what caused the break in your workflow of sending data to your Excel file.

Have you tried opening the Excel file using different encodings such as ANSI? If you could also share with us the Excel file that has the problem so we can check, it would be great.

Regards.

Hi,

I really do not understand the background of my trouble.
It is so weird: the database of JotForm contains the submissions correctly.
The Google Sheet shows the submissions correctly.
After synching with Excel, it shows incorrectly.
An other form appears correctly everywhere, including the SAME Excel workbook, using the same synching method...
It is a black magic.

I tried to change default encoding of my workbook but nothing happens.
Moreover this workbook can show all of other data (already processed previous submissions, manual texts on other sheets, synchronized data from other forms via same method, from Google etc) correctly, so I don't think so the trouble would be inside of my Excel workbook.
The trouble is happening during the synchronization, but the method of it is also same, and other form by JotForm and Google can be imported (synchronized) correctly...
K.O.

Can you check on server side the language- or other hidden settings of my form?
Maybe something went wrong in JotForm's side.
Why do I think it?
During translation and setting up the languages, sometimes I got invalid results, for example one of the three language disappeared totally and later "came back", another day the spanish language became as default without any modification by me and later the settings became correct again automatically (!) to "default language is hungarian".
These special happenings appeared only in editor mode, during watching the language setting window - the form is working correctly in 3 languages continuosly, but maybe these situations caused some damaged settings in server side, affecting the uploads to Google... I don't know, it's just an idea.

Thanks

Laci

Where did you save the excel file? On a local drive or online? This is possibly an issue with language/character encoding on the excel file. Would you mind sharing us the excel file if it's online? 

You may also consider using an alternative such as Zapier.  With Zapier, you can send your form submissions directly to Excel online.

https://www.jotform.com/blog/221-Connect-Your-Forms-to-Excel-with-Zapier

Or, from Google Spreadsheet to Excel online.

Zapier should work for you. They are free up to 5 Zaps (connections). 

It is a wonder here... I got a new submission from a client and everything is OK.
I didn't modify anything: just started the synch process and all of data changed to correct format.
I don't understand anything but I'm happy :-)

Yes, I know Zapier, but I wouldn't like to make a much more complicated receiving process for submissions.

I think the method is not optimal, because it is impossible to refresh local database submission by submission, I can't get separated report, I have to download / import / synch the whole database to get all new submission. It is not a normal solution, especially if I use a mobile network access with limited traffic.

The best and final solution would be an option to get a new submission by email in CSV or XLSX format instead of/together with PDF report. It would be smaller size and the process would be much more easy, I would work only with actual submission only and not a whole sheet that includes all of previous submissions, that are already processed and are downloaded again and again by synch method.

So, at this moment my trouble has been solved without any interaction by me.
Same way as it went wrong yesterday without any interaction by me :-)

Welvin: I use my Excel workbook on my local machine only. The base of this trouble wouldn't be a local setting in Excel neither Windows, because same Excel can handle all of other UTF-8 imports and manual text entering. My old and processed data are also here without any problem, ONLY ONE of my Jotform's synching caused malfunctions.
Same Google Sheet, same Excel workbook, same machine, other form by JotForm = working OK.
Only one of my forms went wrong - and this form is correct already, again... I don't know why, I don't know, how :-)))

Thanks for your support, I hope I will be able to use my form tomorrow too :-)

Friendly

Laci

Unfortunately, we do not have a feature to send submissions via email notification in CSV or XLSX format, but we can forward a request to our back-end team on your behalf if you want to.

Glad to know the issue was solved itself. The problem might have been on the Excel workbook side. 

Open a new thread if you need anything else, we will be glad to assist you.