CODEWITHSUNDEEP
python-3.xgoogle-sheetsgoogle-sheets-apigspread
user12278773
user12278773

Reputation:

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

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.

Upvotes: 1

Views: 380

Answers (1)

Tanaike
Tanaike

Reputation: 201493

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:

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 |.

Upvotes: 2

Related Questions