LiveCode Forums.

Hi all

Andre Garzia has given a nice tutorial of importing data from a Google Sheets here:
https://andregarzia.com/2018/11/reading ... ecode.html
Works nicely by exporting the sheet as (the dreaded) CSV. You can download the demo stack and try it. My problem is that my data has carriage returns inside the cells (e.g. by pressing command return on Mac). This messes up the data (not surprisingly).

I have created a minimal sheet here:
https://docs.google.com/spreadsheets/d/ ... edit#gid=0
that is visible by anyone. When I use Andre's method, the returns are mixed with the returns in the sheet. So I thought a tab separated output might solve it (.tsv). Indeed manually downloading the tsv results in a pretty clean file and I think it keeps all the data nicely. I modified the stack (attached) by putting a button and changing all "csv" to "tsv". In particular, in the url to fetch, I changed: to I have added a text field so the full text imported can be seen. It imports the file somehow but it is not clean.

Can anyone help me get clean data into LiveCode? thanks.

Hi.

Tried it out of the box, and the CSV import works fine,

Craig

Craig, pls see the original cells in Google Sheets and the imported version. Attaching screenshot.

Hi,

look here.

Have fun!

Not my idea of fun for the weekend.

I think I have seen that and probably other posts regarding CSV. What a nutty idea to have a comma separating pieces of text!!

I was hoping for an easy solution, and was hoping tsv would solve it.

Tab separated formatting is much saner, but is still going to need adjusting to deal with returns in the data as well as returns being the record separator.

In that other thread you have a link to Richard's article. In that article is a link to the current repository for the Tweedly function to parse csv to tab.

I would take the exported data, apply the Tweedly function and then view it in a spreadsheet. That will make your weekend a lot brighter.

Thank you. That is a great pointer. Will not be working on it weekend but weekend already brighter.

So merggoogle is out, right?
How about generating another sheet using the QUERY and IMPORTDATA functions? I would use the FORMAT clause with the QUERY to surround each column value with another delimiter character.
The original csv syntax was for each cell to be surrounded by quotes and separated by commas. You may be able to use FORMAT in QUERY to do the same thing or to assign a different delimiter.

looking at this more closely, i'd say export as csv and parse. when i do that, b2 gets quotes around it, which tells you it needs special handling.
https://github.com/macMikey/csvToText

kaveh1000 wrote:
Can anyone help me get clean data into LiveCode? thanks.

Hi Kaveh,
As you have reported 2 interesting bugs in sunnYrex last week,
you win half an hour of my rainy sunday morning time and here is the result:

A proof of concept and a working 20 lines of pure Livecode with a couple of regex.


As I'm not a google spreadsheet user, I have no idea how to set up better google,
so to have a more useful and easy export datas to be parsed by LC; but I can't believe
there is not something...

Finally, your problem has nothing to do with XVS !!!
Replace X by comma, tab, space, cheese or whatever; that won't make any difference.

Contact me on the usual channel for details...

Kind regards,

Thierry

Thank you so much Thierry. Very generous of you and I will contact you offline for details.