Hi @Kal_Lam,
I have a little issue with some submissions with the _id and _uuid. I don’t know why but some submissions do not have _id or _uuid.
Could you explain to me why this happen and how I could resolve it?
I would really appreciate your thoughts.
Best regards,
@segadu78, could you share with me the following through a private message so that I could have a closer look at it?
@segadu78, could you also let us know the following so that we could start investigating your case:
Is the data missing only in the XLS format or even in the DATA>Table View too?
Hi @Kal_Lam, both. I not them see neither XLS format or even in the DATA>Table View.
Where only I can view this data is on PowerQuery editor.
Best regards,
@segadu78, I checked both your DATA>Table View and the XLS dataset but did not find any missing _id or _uuid.
FYR:
_id & _uuid under the XLS dataset:
_id & _uuid under the DATA>Table view:
@Kal_Lam that is true, but when I download this information using PowerQuery editor I see some data without _id or _uuid values and those submissions curiously do not appear in the DATA>Table View and the XLS dataset.
You could see it this clearly if you connect this data to Excel using PowerQuery editor. I am downloading this data as below:
Csv.Document(Web.Contents("https://kc.humanitarianresponse.info/api/v1/data/XXXXXX?format=csv"),[Delimiter=",", Columns=57, Encoding=65001, QuoteStyle=QuoteStyle.None])
Best regards,
@segadu78, so you mean the issue is with the Excel Power Query? To check if the same is an issue could you make another query with a smaller project (that has less submissions with it)?
Yes, sure, I could do it @Kal_Lam, but it appears like Power Query Editor gets all the submissions or data that DATA>Table View and the XLS dataset are not getting completed.
So, it could be interesting to see it internally in Kobo because it appears that we have an issue where using these tools DATA>Table View and the XLS dataset we are not getting all the data available of the server.
I have the same issue where these tools DATA>Table View and the XLS dataset are not providing all the data available on the server. To solve it, I am using PowerQuery editor but there are some fields like “_id” or “_uuid” which are blanks.
It appears like Power Query Editor gets all the submissions or data that DATA>Table View and the XLS dataset are not getting completed.
I would really appreciate your thoughts,
@segadu78, I have moved back your topic to this post as we are too investigating if this is an issue with the API connection. We will update you if we find any.
Hi @segadu78, I can’t reproduce this issue on my side even when using the same API v1 endpoint. If you download the CSV file through that endpoint you should see that all _id and _uuid values are present. It seems since some lines in the CSV have additional commas than others and you are delimiting on commas, it’s causing the result to be truncated. Can you please check this and confirm?
Hi @Josh, you have the reason!
The csv connection is very sensitive and some commas could be causing this.
Below are both export files and one example of this issue:
csv import using web connection of PowerQuery editor.
XML values and headers, the same of DATA>Table View and the XLS dataset.
What format do you recommend to prevent this issue in the future? Maybe json?
Best regards,
Hello @segadu78,
What do you do with these internal variables? For example, data sets can be joined by index and parent index.
You may have a look at these broken cases to locate the possible comma issue (see Josh’s hint). Also newlines may create problems sometimes.
Could you compare broken cases in csv with xls data download?
Hello @wroos
The id value is key to editing, delete or checking some submissions, and submission_time too to know the date and time exactly to ensure the data quality.
For that reason, if the csv import could have these little issues in their connection tool is key to identify what else format could be more properly to prevent this issue.
Best regards,
Hi @segadu78, if the XLS or JSON export works for your purposes then it should avoid this issue. It might also be worth investigating if there is something you can change when reading the CSV. Each comma-separated value is enclosed within double quotes and therefore opening the file in something like Excel reads:
"q1","q2"
"I, have a comma","No comma"
As this:
| q1 | q2 |
|---|---|
| I, have a comma | No comma |
Rather than this:
| q1 | q2 | |
|---|---|---|
| I | have a comma | No comma |
Hi @Josh, thank you very much for your explanation.
Are there some tips that you could recommend to me to avoid this issue when people input information into text fields? I mean, ¿some special constraint or calculation?
Best regards,
Hello,
You could use constraint with regex. You can find examples and more info using the
search function of this forum
https://community.kobotoolbox.org/t/text-data-type-and-csv-data-export/13963/3
and the Help Center articles
https://support.kobotoolbox.org/restrict_responses.html?highlight=regex.
