Syntax Error. Unclosed quoted cell Can not parse text argument to "List from csv able" as a CSV-formatted table

[Vipul Chaudhary]

Dear Friends 

Please help me.

In my app, I browse data from a google sheet through web component. Everything works fine. but, one day a user submitted a response row and after then I am getting the error mentioned in the subject. In that row, if I remove all text from a specific cell and make it empty then the error does not occur. What can be the reason? 

Your help is appreciated. Thanks

[TimAI2]

Have a look at the raw data coming back from the google sheet (responseContent)

What method are you using to call the data ?

What is the structure of your dataset (show as table or csv) - any gaps / spaces / empty rows?

[ABG]

You might have to cleanse the incoming responsecontent of

troublesome characters like '  before you send it through

a csv table to list conversion block.

I once had a payroll program I wrote crash when it encountered

the employee named O'Reilly.

What was in the bad cell?

ABG

[Vipul Chaudhary]

Thank you, Sirs, for taking time. 

The culprit text is attached herewith in a text file. 

The text is disturbing all apps.

I made a separate simple checkpoint project. When is pasted this text in any cell of google sheet the problem occurs. The text doesn't have ' or , .

Please have a look into the matter.

Thank You. 

[TimAI2]

Returning OK for me  (output list does require some tidying up)

I copied the text from the file (CTRL+C)

Pasted it to a google sheet (CTRL+V)

Then imported it to AI2 with a web component

[Vipul Chaudhary]

Sir 

please paste all text in one cell as a whole. 

 I use get url like this  as mentioned in pura vida apps. http://puravidaapps.com/spreadsheet.php

GET Url
In your spreadsheet select File - Share and copy the link from the "Link to Share" box. Then replace the text edit?usp=sharing by export?format=csv. 

[TimAI2]

This fixes it, just remove the "

[TimAI2]

To be honest, for viewing, by just removing the csv table block so you put the reponseContent directly into the label, makes for easier viewing

[Vipul Chaudhary]

Thank You Sir

But there are some more questions.

If I remove " from the sheet cell manually (not using blocks as suggested by you) then the problem occurs. 

and 

Why only one user's data is the culprit? Data sent by other users have "

additionally, if I write anything manually in the cell between " then it is not problematic. 

[TimAI2]

Man goes to the doctor:

Man: "Doctor, it hurts when i move my arm like this...."

Doctor: "Don't move your arm like that then..."

[ABG]

Why only one user's data is the culprit? Data sent by other users have "

The csv format works better when the data it wraps

does not contain the markers (") used to wrap it.

If you can scrub the " marks from the data 

on its way into the spreadsheet, your life gets

easier when you have to unwrap the csv

table into its rows and columns.

References:

https://en.wikipedia.org/wiki/Comma-separated_values

https://tools.ietf.org/html/rfc4180  (note item 7)

ABG

 

[ABG]

From examining your single column data sample,

I notice that your logical (not physical) data table format is

in a single three column format,

• doctor name
• service or medicine
• datetime when provided

if you were to keep your data in this structure,

that would enable you to  use the power of SQL

queries to answer questions like:

• who administered this drug between this date and that date?
• who has administered the most of this drug?
• who has administered the most of any drugs in this time frame?

Of course, a SQL engine would help for these queries,

and Google Sheets can do some of that work.

Reshaping your data like this is called normalization.

ABG

[Vipul Chaudhary]

Thank you, Sirs

I can not change the basic design of my complex multiuser app. I must find the culprit thing in the data. I have tried hard to localize the problem.

Here I am attaching two txt files. Their text is the culprit and causing problem Syntax Error. Unclosed quoted cell when app is built. During development Can not parse text argument to "List from csv table" as a CSV-formatted table error occurs.

The text sent to google sheet by my app is in Invisible inverted commas "". In visible means, it is not visible in the cell of the sheet. but, when I right-click on a cell and copy and then paste in a notepad the entire text is between two inverted commas. this happens because that text is generated in the app using the Join Text block. 

The above Replace All Text block does not remove the Invasable Inverted Commas.

This text does not contain any extra inverted commas, commas or pipe. Even If I write these special characters in any cell then nothing is problematic. 

When you test this text in your checkpoint project then please copy all the text with or without inverted commas and then go to the sheet. Double Click on a cell and paste it there. after that, if you browse data to your app, The error would occur. 

Please note that the app works fine normally when many such entries are in the sheet but in rare mysterious case sometimes any user-created text (example attached) enters in a cell then this problem occurs. 

Your help will be appreciated. Thank you very much. 

[TimAI2]

You have moved the goalposts!

Your original problem was with downloading the csv data from google sheets, that is what the ReplaceAllText block was for, and produced a working list(of lists).

Now you are trying (unnecessarily?) to remove invisible double quotes when generating the log in the app, these invisible quotes will exist as they are created by the text block to identify a string 

It is not clear what your problem is now ?

[Vipul Chaudhary]

UPDATE:

I copied the text in three different parts and pasted in the cell to localize the problem but no error occurred all the three-time. Error Occurs when the whole text is placed in any cell of the Google Sheet. 

[Vipul Chaudhary]

Thanks, Tim For the reply.

I tried to replace double quotes before posting because I thought that these Invisible quotes may be creating the problem. 

The error occurs when I download csv table. 

[TimAI2]

Did you use the replacealltext block for " on the responseContent ?

[Vipul Chaudhary]

No.

I am going to test it soon.

[Vipul Chaudhary]

I tried to remove the double quotes using replace all text block from the response content. Yet the error happens. 

[TimAI2]

Like so ?:

https://14973399418869302879.googlegroups.com/attach/b900dab0b1c7f/culpritblocks2.png

[Vipul Chaudhary]

Yes.

Also tired the block between the lable text block and List form Csv Table block.

[TimAI2]

And the data stored in the google sheet is the same as before when we looked at it ?

[Vipul Chaudhary]

Yes Sir

[TimAI2]

Would need access to your aia file and the google sheet to test/analyse further

As previously mentioned by ABG, I to would advise normalising your data into a proper table structure for use with lists / google sheets / csv

[Vipul Chaudhary]

Sir,

Here is aia of a replica of my app. I created to localize the problem. When you paste the culprit text any cell of two columns, the problem will occur.

Link to sheet 

https://docs.google.com/spreadsheets/d/1d5mCBhCPHMYi4stcwPJHxHc0pUre20jEhxyumaada_U/edit?usp=sharing

I use http://code.appinventor.mit.edu

Thanks. _/\_

[TimAI2]

This is all completely different to what we were working on earlier ????

[Vipul Chaudhary]

No 

It is a checkpoint project. It has the same features.

Please paste the culprit text in any one cell of any of two columns and press get button of the app. There will be an error.

To paste, please double click on any cell and then paste whole text in it.

[TimAI2]

I am getting somewhere....

Able to submit to the google form from the app, but response is not showing in the google sheet.

Are the correct form and sheet linked ?

Also able to retrieve data from the sheet

[ABG]

I am suspicious of your MakeListsToPost procedure.

It looks like it trying to create a 2 column table

from two matched 1 dimensional rows.

Using a for each item loop with an index in list lookup

can cause a mismatch if you have duplicate items.

As an alternative, consider a for each number from 1 to length(list1)

combined with select item number blocks to match up

your lists into pairs.

Also, a question about your data ...

Are you trying to squeeze a multidimensional table into each and every cell in column 2?

ABG

[TimAI2]

I believe I have a solution, but

until the google form is linked up with the google sheet

I cannot test 

[TimAI2]

The attached aia project file is working for me, both ways.

I added a second web component and edited your post command to match the one I use here

I think perhaps trying to POST and GET with one web component may have been causing an issue 

[Vipul Chaudhary]

Thank you Very much, Sirs

I have read your instructions and lessons.

Now let me make this question very specif and simple.

I made a simple app. Blocks and aia attached herewith.

When Button 1 is pressed, the web simply browses data table.

But the error occurs if the culprit text (attached herewith) is placed in any cell.

You please try

Copy all text from the txt file attached herewith. Copy with or without double-quotes.

Click on any cell of the sheet and then get data in the app as a csv table. There will be an error.

If you remove this text from the sheet then there will be no problem. 

So something is wrong with the text. Please test with it. 

Sheet URL https://docs.google.com/spreadsheets/d/15R0N_wpmt2MBy4loc1TlpZf50ycwglZIQJbbbWbySPk/edit?usp=sharing

Thanks _/\_

[TimAI2]

Yes, but as I keep telling you, and demonstrated in my aia above, if you do a replace all text on the double quote then the data is pulled through to the app as a list

Tested when getting the data with or without quotes copied into the cell

It looks like you are not going to take our advice and normalise your data into a proper table structure, therefore this is as good as it gets :)

[Vipul Chaudhary]

Sir,

You have pasted text lines in different cells.

I do not use text so.

Please double click on a cell and then paste all text there in that cell. 

After that, there will e error. 

[TimAI2]

Still works :)

[Vipul Chaudhary]

I used Replace All text - Double Quotes earlier. but, Due to some reason or my mistake It was showing error. 

Currently, it is working!! I tasted for both the texts. 

A big thanks to 

TIM Sir and ABG Sir.

_/\_   _/\_

[ABG]

@Vipul, are you doing this project for a grade, or

just for yourself?

A Computer Science professor would deduct points 

for your data organization.

When the day comes when you have to sort, group,

and summarize your data, you will find yourself

having to reorganize your data.

ABG

[Vipul Chaudhary]

Dear Sirs

@ABG

I use this app for a company. A slide show of products shown to Doctors by salesmen. The culprit data shown to you is something like logs which is generated during a manually played slideshow.  I want these logs just as plain text data. No further sorting for that data is required. It is used just to observe whether the slides are shown or not and to whom and at what time. 

@TIMAI2

One error is solved but the idea of removing double quotes from a table of data is creating another problem. When I use Replace Double Quotes blocks, the downloaded data do not behave like a CSV Table. 

Please see in the screenshot of the app. There should be a list of first column items(A,B,C). Instead, it is showing all the data from the table(Sheet). 

[TimAI2]

@ Vipul

Yes you are right. This is not an AI2 issue but a Google Sheets one.

When asked to export the data as a properly structured csv, Google Sheets does its best to comply!

It sees the line returns in the cell, and identifies these as separate elements of the csv

generating the result in your list that you see. Nothing much can be done about this as it stands.

A string with no line breaks is needed.

To fix the problem, you would need to modify the way in which you format the data before

sending it up to the google sheet (and consequently how you handle it when you call it back to the app)

One method would be to use a "non csv delimiter", for example "|" inbetween each item. This will ensure that

everything in the cell is treated as a single list element when called back to the app.

In order to display this in your preferred format, you can then carry out some list and text manipulation.

It shouldn't take too much work to convert your existing format to the new string format in the app

Your original data structure (extract showing one entry):

 

----------

Vaibhav Chaudhary

18/09/2019 12:14:24 PM

 WELLOK1--12:14:24 PM

 COGNIWELL TAB--12:14:26 PM

 ACBRO 200-SR TAB--12:14:27 PM

 ACBRO 3D--12:14:30 PM

Proposed new data structure (extract showing three entries):

" | |----------| |Vaibhav Chaudhary|18/09/2019 12:14:24 PM|WELLOK1--12:14:24 PM|COGNIWELL TAB--12:14:26 PM|ACBRO 200-SR TAB--12:14:27 PM|ACBRO 3D--12:14:30 PM | |----------| |Dr Ashish Yadav|18/09/2019 10:23:58 AM|WELLOK1--10:23:59 AM|COGNIWELL TAB--10:24:01 AM|ACBRO 3D--10:24:02 AM|ACBRO-N--10:24:03 AM|LYCOWELL TAB--10:24:05 AM|FLUOK-FT--10:24:05 AM|TEMP 250 LIQ--10:24:06 AM|TEMP MF LIQ--10:24:07 AM|LEVONLY TAB--10:24:08 AM|WELLOK1--10:24:12 AM|COGNIWELL TAB--10:40:33 AM|WELLOK1--10:43:28 AM | |----------| |N R Patel|18/09/2019 12:03:17 PM|WELLOK1--12:03:17 PM|COGNIWELL TAB--12:03:47 PM|ACBRO -100 TAB--12:04:22 PM|ACBRO 200-SR TAB--12:04:30 PM|ACBRO 3D--12:04:34 PM|ACBRO-N--12:05:00 PM|WELLOK1--12:05:58 PM|COB-12--12:06:07 PM|ACBRO-N--12:06:08 PM

"

Sheet looks like this for just the above entry and a couple of others:

Blocks required to extract this entry from the list and display in the original format:

Label output in app:

Added bonus, this appears to overcome the double quotes issue as well when feeding the Response Content direct to a list (but I am taking them out anyway for presentation purposes)

This way you can keep all your data in a single cell (up to 50,000 characters!) and have control over it when called into your app

[Vipul Chaudhary]

Thank You, Sir

But, this solution is not feasible for me. Because the sheet in the original project contains many other columns with data in them. I use that data for generating various type of reports by sorting them. 

Can you tell me how can I prevent feeding double quote in the sheet? Is it possible to convert a string in a simple plain text?

[TimAI2]

You do tend to look for ways to NOT do something.

You can manipulate the data in the google sheet in much the same way, or use google apps script to do so.

Something has to be changed with your data structure if you want to interact with AI2 and google sheets