UTF-8 error with CSV file using GAMADV-XTD3

[Simon Laycock]

I need to update a Google sheet each night on a schedule using a CSV file generated by our school management system. Unfortunately, I have no control over the format of the CSV. 

There is one student called Esmé in the file, and when I run 

gam user simonlaycock update drivefile xxxxxxxxxxxxxx csvsheet id:0 localfile summary.csv

the e acute is causing the following error:

ERROR: File: summary.csv, 'utf-8' codec can't decode byte 0xe9 in position 61004: invalid continuation byte

It works fine if I change her name to "Esme". I need this to run unattended, so I need a way around the problem...

The second issue is that the file gets saved on a network share, and I haven't had any success pointing at it e.g. 

gam user simonlaycock update drivefile xxxxxxxxxxxxxx csvsheet id:0 localfile \\someserver\Staff2\Admin Drive\SIMS Dashboard Data\summary.csv

I've tried using quotes around the path, but no joy.

Any ideas on either problem?

[Troy Yochelson]

Since it appears you're running Windows, you could use the PowerShell techniques shown on this page to strip out your diacritics; not sure how you'd get them put back into whatever "drivefile" you're updating, though:

https://lazywinadmin.com/2015/05/powershell-remove-diacritics-accents.html

As for reaching the network file: would it be possible to copy it to %TEMP% first, then process the file, then clear it from %TEMP% as part of automation scripting?

--

Troy Yochelson, Network Administrator

Lick-Wilmerding High School

[Simon Laycock]

Thanks Troy - as it turns out Ross helped me out last night and I'm here to post the solutions for anyone else who needs them.

The file path issue was easy - I needed to use double quotes around the path rather than single quotes. I didn't realise Windows required double quotes.

The character set problem had two different solutions. The first was to remove the csvsheet id:0 and just replace the whole file. This means the Sheets API is not used, just the Drive API, which is less fussy about the encoding.

gam user simonlaycock update drivefile xxxxxxxxxxxxxxxx localfile "\\someserver\Staff2\Admin Drive\SIMS Dashboard Data\summary.csv"

The other solution, which still allows a specific sheet to be updated, is as below:

gam user simonlaycock update drivefile xxxxxxxxxxxxxxxx csvsheet summary.csv charset latin1 localfile "\\someserver\Staff2\Admin Drive\SIMS Dashboard Data\summary.csv"

I chose to reference the sheet name as it won't ever change, and needed to also add the character set parameter: charset latin1

Thanks again to Ross for saving the day!