How can I specify the delimiter that gspread uses when running the function import_csv to separate the fields?

Question

I am writing a .csv file that uses | as the delimiter. I would like to be able to specify that when running the function import_csv(). How can I do this? To do this would I have to use a gspread alternative?. Tbh I just need a code example.

Answer 1

I believe your goal as follows.

• You want to put a CSV data to Google Spreadsheet using gspread.
• You want to put the CSV data by giving the delimiter like |.
• You have already been able to get and put values for Google Spreadsheet with Sheets API.

Issue and workaround:

When I saw the script of gspread, it seems that import_csv(file_id, data) puts the CSV data using Drive API. Ref When Drive API is used, in the current stage, the delimiter cannot be manually set.

So, in this answer, as a workaround, I would like to propose to use the method of batchUpdate in Sheets API for achieving your goal. The sample script is as follows.

Sample script:

spreadsheetId = "###" # Please set the Spreadsheet ID.
sheetName = "Sheet1" # Please set the sheet name you want to put the CSV data.
delimiter = "|" # Please set the delimiter.
csv = open('filename.csv', 'r').read() # Please set the CSV data.

client = gspread.authorize(credentials)
spreadsheet = client.open_by_key(spreadsheetId)
sheet = spreadsheet.worksheet(sheetName)
body = {
    "requests": [
        {
            "pasteData": {
                "data": csv,
                "delimiter": delimiter,
                "coordinate": {
                    "sheetId": sheet.id
                }
            }
        }
    ]
}
spreadsheet.batch_update(body)

• This script puts the CSV data to Sheet1 in spreadsheetId.
• In this script, about client, please use it for your actual script for using gspread.

References:

• import_csv(file_id, data)
• batch_update(body)
• Method: spreadsheets.batchUpdate
• PasteDataRequest

Added:

From your following reply,

when implementing this it throws me the error: TypeError: Object of type bytes is not JSON serializable for the variable body

I'm worry about that the value of csv = open('filename.csv', 'r').read() might be due to the reason of your current issue. So, I would like to add one more sample script. In this sample script, the CSV data is directly used in the script.

Sample script:

spreadsheetId = "###" # Please set the Spreadsheet ID.
sheetName = "Sheet1" # Please set the sheet name you want to put the CSV data.
delimiter = "|" # Please set the delimiter.

csv = """a1|b1|c1|d1|e1
a2|b2|c2||e2
a3|b3||d3|e3
"""

client = gspread.authorize(credentials)
spreadsheet = client.open_by_key(spreadsheetId)
sheet = spreadsheet.worksheet(sheetName)
body = {
    "requests": [
        {
            "pasteData": {
                "data": csv,
                "delimiter": delimiter,
                "coordinate": {
                    "sheetId": sheet.id
                }
            }
        }
    ]
}
spreadsheet.batch_update(body)

• In this sample script, the value of csv is put to the Spreadsheet as the delimiter of |.