Upload folder of CSV'S to Google Sheet using Python

user_19077799 0
Viewed 72

I am amateur in python and Google API. I have to create script which uploads all CSV from folder to Google Drive as sheets. So far I have this script which uses gspread to upload one csv file to one specified Google Sheet. I have a lot of those CSV and I need to upload them as sheets to google drive. I am not able find a solution to deal with multiple CSV files.

import gspread
gc = gspread.oauth(credentials_filename='/users/krzysztofpaszta/credentials.json')

content = open('ONETTF-Projects-INFO.csv', 'r').read().encode('utf-8') 

gc.import_csv('1gv-bKe-flo5FwIbt_xgCp1vNn0L0KBnpiu', content)

All of information is about uploading one csv file. Could someone give me some hint how to manage to upload folder with csv files such that all files as saved in Google Drive as sheets?

Thank you for your help.

EDIT: As @metamorporpoise suggested I have modified the script, I am very very close because I can see looking at the sheet in google drive that the script is actually uploading all of my CSV but in the same moment it is replacing imported CSV with new one. That's because those files are uploading on the same sheet.

import gspread
import os
gc = gspread.oauth(credentials_filename='/users/krzysztofpaszta/credentials.json')

os.chdir('/users/krzysztofpaszta/CSVtoGD')

files = os.listdir()

for filename in files:
   if filename.split(".")[1] == "csv":
      content = open(filename, 'r').read().encode('utf-8') 
      gc.import_csv('1gv-bKe-flo5FwIbt_xgCp1vNn0L0KBnpiuRak9a1efQ', content)

Is there a way to modify the gc.import_csv to import those files to different sheets?

google-drive-api google-sheets csv python
1 Answers

While I am not entirely familiar with the gspread package, I do know that the os package would be very helpful for iterating through files in a folder. You would not need to install os, as it should already come with Python. You can use it like so:

import gspread
import os
gc = gspread.oauth(credentials_filename='/users/krzysztofpaszta/credentials.json')

os.chdir(FOLDER_PATH)

files = os.listdir()

for filename in files:
   if filename.split(".")[1] == "csv":
      content = open(filename, 'r').read().encode('utf-8') 
      gc.import_csv('1gv-bKe-flo5FwIbt_xgCp1vNn0L0KBnpiu', content)

You would need to replace FOLDER_PATH with the path to the folder you are storing the csv's in relative to the directory you are running your python script in. The if filename.split(".")[1] == "csv": line is there to ensure that the script only tries to upload csv files and ignores any other type of file. I am not entirely familiar with what the first argument in the gc.import_csv() command is doing, so that might cause an issue if it is specific to the particular csv you were trying to upload before. Hope this helps!

EDIT: Upon looking into the import_csv() function, it seems that my current code would just continuously overwrite the same spreadsheet over and over. It seems like you would need to create new spreadsheets for every file and then pass in the file_id as the argument to import_csv() each time

EDIT2: Try adding this line after the if statement: sh = gc.create(filename.split(".")[0]) and then replacing the long string that is currently the first argument of import_csv() with sh.id. I hope this works!

user_192269340
Related