Sandbox CSV loading from Google Docs

Hi, does anyone have an idea why this won't execute in the Sandbox?
LOAD CSV WITH HEADERS FROM 'https://docs.google.com/spreadsheets/d/1PYwJccqzBKpWoghCKSkCzm2voE6QAYSmn8fB4pgy04U/edit?usp=sharing' AS row
RETURN row

I get this error but can't see anything wrong
At https://docs.google.com/spreadsheets/d/1PYwJccqzBKpWoghCKSkCzm2voE6QAYSmn8fB4pgy04U/edit?usp=sharing @ position 15890 - there's a field starting with a quote and whereas it ends that quote there seems to be characters in that field after that ending quote. That isn't supported. This is what I read: '0"]'
Thanks

You are getting that error because you have " in one of the cells. You need to find a way to escape any internal " because LOAD CSV reads everything in as string with a " at the beginning and the of each field.

could you share a screen shot of this entire row to see what all in included in that field?

1 Like

@mckenzma knows. We've been dealing with unsanitized data at work for weeks now. It's a pain in the tail.

Any field you have a " you need to escape it with another double quote so "". You also need to wrap the string in double quotes. This has the added benefit of handling the random comma or whatever field delimiter you may have chosen.

So this file header and row:

Name, SerialNumber, Description
GTX3080, 4312214, A video card that is 300" long. Yes, it doesn't fit in any computer case.

will fail because of the odd ", and will also treat that row as 4 columns because of the comma. To resolve you need.....

Name, SerialNumber, Description
GTX3080, 4312214,"A video card that is 300"" long. Yes, it doesn't fit in any computer case."

For safety, we've settled on wrapping EVERY field in quotes. Because even if it's supposed to be an integer, it comes from the LOAD CSV as a string. So toInteger() is needed regardless.

Hope that added detail helps!

1 Like