Exported Excel file is missing lines

Hello everyone,

Can somebody help my in a export problem, please?

In one of the survey I conducted in the Interviewes tab, in Survey Solutions, I can see that the “Completed surveys” number is egg. 751, but in the exported excel file I’m getting just 669 lines.

Comparing to the exported file for “Interviewer assigned surveys”, the export file has the correct number of lines (interviewer keys).

I can’t see why this is happening!

Thank you!

1 Like

Here are a few idea of what might explain this difference:

1. Suvey !== questionnaire

For one survey, you might have several versions of the questionnaire. For the Interviews tab, you might see all interviews for all versions of a questionnaire. Meanwhile, the export data will only be for one version of the questionnaire.

2. Filtered views

Both when viewing interviews on Headquarters’ Interviews tab and when exporting data, one can get a subset of all interviews. In Headquarters, one can filter by lots of different types of interview metadata. When exporting data, one can filter by the interview status.

Thank you for your reply.

I don’t think that this is the case. These are the stats for one version of my survey. You can see it like this:

Another possibility that springs to mind: the exported data is older than the data on Headquarters, and so the difference is due export data being “out of sync” with the server.

  1. Try exporting All interviews (without any filter) and then count the interviews in the Completed status. Provided that this number matches the count shown in the interface, then you can compare with the export of just the Completed interviews, to determine which ones were not included there. With roughly 10% of the interviews missing (as per your claim) this should not take long to find several examples.

  2. Your post is titled “Exported Excel file is missing lines”. Since Survey Solutions does not export data in Excel format (*.xls or *.xlsx), if you are getting specifically that format, then there may be some conversion tool in the middle, which may or may not be working correctly. Clarification is needed on all the software (beginning from the version of Survey Solutions) used in this chain.

  3. Consider exporting the data from the demo server to check if you can see a similar situation there, which would be easier to trouble-shoot.

@arthurshaw2002 has written that “the exported data is older than the data on Headquarters”, I assume that @DGRICP is doing repetitive attempts at exporting the data and consistently replicates the same problem, if not, mention that.

@arthurshaw2002 also mentions the “export data being “out of sync” with the server”, since the count of completed interviews was 751 when the original question was posted, and remains the same a day later (from the screenshot) the volatility of data volume due to new syncs is likely not the explanation in this case (for a 10% difference), but @DGRICP may want to confirm this directly by requesting an export of data and simultaneously refreshing and downloading a report on the interviews and their statuses.

@DGRICP may inspect the readme file in the export archive to check for the date/time when the data was exported.

Thank you for answer, @sergyi!

I found out that the problem in the exported .tab file (I’m opening it in excel, that is why I wrote excel in the post, sorry) after I applied concatenate formula. In one cell I found a big amount of the missing data that should be, normaly, in a single cell/string.

The problem in my survey is that I have some fields with open text answer. The respondents wrote entire paragraphs describing problems in a specific field. The exported .tab file seems to ignore the commas (,) from their text answers and the registered lines are spread in different cells after using text to columns. Every text is spread in another cell when encountering a comma, that is not displayed in the export file.

How can I prevent this on my next interviews?

Thank you so much!

I don’t understand this. In one cell you’ve found content that should be in one cell. What is wrong?

Please demonstrate this with an example. A comma is an absolutely normal and common occurrence in the open text questions and should not be ‘ignored’ in the export process.

*In one cell I found a big amount of the missing data that should be, normaly, displayed in multiple rows. Sorry for my bad explanation.

After applying concatenate, the formula did its job partially.

I will show this example for you, just to explain the entire process.

And after a long trust in Microsoft excel I found out that Libre Office is displaying the export file 100% correct. Must be a limitation of my excel version. Must be something in settings that in this version is missing. Shameful. I Can’t understand why, at this time.

I am so sorry that I took your time with this. I didn’t know that this version of Microsoft Excel can ignore the commas for this type of export (.tab) and can complicate simple things.

Hello @DGRICP ,

I anticipate no such issue with the content that you’ve shown and the common Excel version. Furthermore, I have gone through the steps you’ve provided and I could not replicate the problem that you’ve described.

  1. This is the questionnaire that I have used:
    https://designer.mysurvey.solutions/q/details/953faa24e13144ac984e1ad62593aab5
  2. This is the content that I have entered:
  3. This is the export data file that I’ve obtained.
  4. This is what the file in question actually contains with explanatory comments:
  5. This is how the file gets opened in Excel:

If your Excel visualizes the same, then the example that you’ve provided is not illustrative, or possibly you are not using the most recent version of Survey Solutions and your output is somehow affected. (unlikely, since you are mentioning that Libre Office shows it fully). Hence, clarify the version and share the actual unmodified Survey Solutions output file (not a screenshot of how it appears in some spreadsheet),

In any case, I still don’t see how parsing the content on comma into one or several columns could affect the number of rows/lines/records, that you were describing in the original post.

I’m so sorry, but after further investigation, the problem comes just from the older version of excel (2013) installed on that particular old working station I used and also, probably, an older version of windows (8). At the time I was writing the first post, I couldn’t imagine that this can happen.

I downloaded the same file and on the laptop with windows 11, but the same microsoft office 2013 excel version still I’m facing problems with the text to column function, but this time, the number of rows equivalent to the number of interviews are all showing, but still some columns are missing.

I’m so sorry, but I can not disclose the content of the file, that’s why I can not show you the exact bugs I’m facing.

But what matters is that I know that Survey Solutions (and its exported files) are not the problem.

That’s why you couldn’t replicate it.

I think this solves the topic.
But this can be a lesson for those that are using Office from 12 years ago, but this is must be a rare percentage of people.

Thank you!