Forms Survey Excel export is not well formed

%3CLINGO-SUB%20id%3D%22lingo-sub-993371%22%20slang%3D%22en-US%22%3EForms%20Survey%20Excel%20export%20is%20not%20well%20formed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-993371%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20new%20to%20Office%20365%20and%20have%20worked%20hard%20to%20create%20an%20extensive%20survey%20for%20our%20business%20leadership%20team%20(with%20multiple%20chains%20between%20surveys).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETesting%20this%20survey%20shows%20that%20the%20responses%20are%20accurately%20captured%20and%20displayed%20via%20the%20Forms%20Response%20Analysis%20tab.%26nbsp%3B%20The%20survey%20takers%20captured%20summary%20of%20responses%20are%20also%20correctly%20displayed%20(when%20the%20survey%20taker%20requests%20a%20summary%20of%20responses%20to%20be%20emailed%20to%20them).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20when%20I%20export%20the%20survey%20responses%2C%20I'm%20seeing%20an%20error%20on%20opening%20the%20Excel%20document.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3EWe%20found%20a%20problem%20with%20some%20content%20in%20'exportedresponses(9).xlsx.%20Do%20you%20want%20us%20to%20try%20to%20recover%20as%20much%20as%20we%20can%3F%20If%20you%20trust%20the%20source%20of%20this%20workbook%20-%20click%20Yes.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3ERepairs%20to%20'exportedresponses%20(9).xlsx'%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3EExcel%20was%20able%20to%20open%20the%20file%20by%20repairing%20or%20removing%20the%20unreadable%20content.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3EExcel%20completed%20file%20level%20validation%20and%20repair.%20Some%20parts%20of%20this%20workbook%20may%20have%20been%20repaired%20or%20discarded.%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%23FF0000%22%3ERepaired%20Records%3A%20Table%20from%20%2Fxl%2Ftables%2Ftable1.xml%20part%20(Table)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3EOnce%20I%20finally%20open%20the%20exported%20Excel%20spreadsheet%20-%20the%20data%20found%20in%20the%20exported%20spreadsheet%20is%20NOT%20WELL%20FORMED!%26nbsp%3B%20Questions%20and%20responses%20can%20be%20out%20of%20order%20or%20scrambled.%26nbsp%3B%20In%20addition%2C%20it%20appears%20that%20some%20older%20questions%20are%20reflected%20in%20the%20export.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3EI%20don't%20understand%20-%20how%20is%20the%20excel%20export%20almost%20completely%20different%20than%20what%20I%20am%20seeing%20in%20the%20%22Question-wise%22%20analysis%20tab%3F%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3EIn%20my%20review%20of%20Microsoft%20forums%20-%20I'm%20seeing%20that%20this%20is%20a%20common%20problem%20(that%20has%20been%20around%20sometime).%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F79817%22%20target%3D%22_blank%22%3E%40microsoft%3C%2FA%3E%26nbsp%3B-%20this%20Forms%20tool%20survey%20looks%20like%20it%20can%20be%20a%20powerful%20tool.%26nbsp%3B%20However%20-%20I%20have%20no%20confidence%20that%20I%20can%20use%20this%20survey%20with%20my%20management%20team%20-%20as%20getting%20exported%20summary%20of%20responses%20is%20not%20reliable.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHey%20-%20I%20recognize%20the%20problem%20could%20be%20user%20error%20-%20so%20I%20appreciate%20your%20guidance%20if%20I'm%20doing%20something%20wrong%20on%20my%20side.%26nbsp%3B%20Is%20there%20anything%20that%20we%20can%20do%20to%20troubleshoot%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-993371%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExport%20Results%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EForms%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESurvey%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1002651%22%20slang%3D%22en-US%22%3ERe%3A%20Forms%20Survey%20Excel%20export%20is%20not%20well%20formed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1002651%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F450614%22%20target%3D%22_blank%22%3E%40JohnAndrew1590%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20have%20you%20tried%20to%20open%20the%20excel%20file%20direct%20from%20the%20OneDrive%2FSharePoint%20site%20that%20it%20is%20stored%20in%20rather%20than%20clicking%20the%20'open%20in%20excel'%20button%20on%20the%20response%20tab%3F%20FYI%20-%20It%20is%20usually%20saved%20in%20the%20Documents%20folder%20of%20a%20SharePoint%20site%20(if%20it%20is%20a%20group%20form)%20and%20the%20top%20level%20of%20a%20OneDrive%20folder%20if%20it%20is%20a%20personal%20form.%26nbsp%3B%3CBR%20%2F%3EOnce%20you%20find%20it%20you%20can%20open%20and%20save%20a%20copy%20or%20you%20can%20use%20Get%20%26amp%3B%20Transform%20%2F%20Power%20Query%20to%20import%20the%20results%20into%20another%20excel%20or%20Power%20Bi%20file%20to%20analyse%20the%20results%20and%20dynamically%20refresh%20the%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1279656%22%20slang%3D%22en-US%22%3ERe%3A%20Forms%20Survey%20Excel%20export%20is%20not%20well%20formed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1279656%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F434871%22%20target%3D%22_blank%22%3E%40AndyRath%3C%2FA%3E%26nbsp%3BI%20experience%20the%20same%20error%20when%20downloading%20the%20response%20data%20file%20from%20Microsoft%20Forms%20Pro.%20I%20tried%20your%20solution%2C%20but%20was%20not%20able%20to%20find%20the%20file%20on%20my%20OneDrive.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20check%20the%20response%20data%2C%20everything%20seems%20fine%2C%20all%20the%20questions%20are%20there%20although%20not%20in%20the%20same%20order.%20However%2C%20the%20error%20makes%20me%20a%20bit%20nervous%2C%20same%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F450614%22%20target%3D%22_blank%22%3E%40JohnAndrew1590%3C%2FA%3E%3CSPAN%3E%26nbsp%3B.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20wonder%20whether%20anybody%20was%20able%20to%20find%20out%20why%20the%20error%20keeps%20happening%20and%20what%20can%20be%20done%20about%20it.%20Thanks%20a%20lot.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I'm new to Office 365 and have worked hard to create an extensive survey for our business leadership team (with multiple chains between surveys).

 

Testing this survey shows that the responses are accurately captured and displayed via the Forms Response Analysis tab.  The survey takers captured summary of responses are also correctly displayed (when the survey taker requests a summary of responses to be emailed to them).

 

However, when I export the survey responses, I'm seeing an error on opening the Excel document. 

We found a problem with some content in 'exportedresponses(9).xlsx. Do you want us to try to recover as much as we can? If you trust the source of this workbook - click Yes.

Repairs to 'exportedresponses (9).xlsx'

Excel was able to open the file by repairing or removing the unreadable content.

Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
Repaired Records: Table from /xl/tables/table1.xml part (Table)

 

Once I finally open the exported Excel spreadsheet - the data found in the exported spreadsheet is NOT WELL FORMED!  Questions and responses can be out of order or scrambled.  In addition, it appears that some older questions are reflected in the export.

 

I don't understand - how is the excel export almost completely different than what I am seeing in the "Question-wise" analysis tab?

 

In my review of Microsoft forums - I'm seeing that this is a common problem (that has been around sometime).

 

@microsoft - this Forms tool survey looks like it can be a powerful tool.  However - I have no confidence that I can use this survey with my management team - as getting exported summary of responses is not reliable.

 

Hey - I recognize the problem could be user error - so I appreciate your guidance if I'm doing something wrong on my side.  Is there anything that we can do to troubleshoot?

2 Replies
Highlighted

@JohnAndrew1590 

Hi have you tried to open the excel file direct from the OneDrive/SharePoint site that it is stored in rather than clicking the 'open in excel' button on the response tab? FYI - It is usually saved in the Documents folder of a SharePoint site (if it is a group form) and the top level of a OneDrive folder if it is a personal form. 
Once you find it you can open and save a copy or you can use Get & Transform / Power Query to import the results into another excel or Power Bi file to analyse the results and dynamically refresh the data.

Highlighted

@AndyRath I experience the same error when downloading the response data file from Microsoft Forms Pro. I tried your solution, but was not able to find the file on my OneDrive.

 

When I check the response data, everything seems fine, all the questions are there although not in the same order. However, the error makes me a bit nervous, same as @JohnAndrew1590 .

 

I wonder whether anybody was able to find out why the error keeps happening and what can be done about it. Thanks a lot.