python - 使用 Python 将 Google Sheet 下载为 CSV，生成只有一行的 CSV

Question

我创建了一个相当基本的程序，它使用 Google 表格并将其转换为 CSV 文件，然后将所述 CSV 文件输出到给定目录中。该部分按预期工作，但输出 .CSV 没有换行符或换行符，因此当它加载到 Excel 中时，它被读取为一行，使用 \r\n 而不是换行符。

这个程序有一些不需要的导入和其他问题，但我还没有清理它们。

程序如下:

# Package Imports
from __future__ import print_function
import re
import os.path
import io
import csv
import ctypes
import google.auth.exceptions
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from googleapiclient.http import MediaIoBaseDownload

# Function Runners (for test purposes)

# Other Programmable Strings
EXPORT_DIRECTORY = 'DIREC'
CREDS_DIRECTORY = 'DIREC'
GSHEET_ID = 'ID'
FILE_NAME = 'NAME' + '.csv'
# When editing this URL, token.json MUST be deleted to reauthorize with Google.
SCOPES = 'https://www.googleapis.com/auth/drive'

# Define Global Variables
MessageBox = ctypes.windll.user32.MessageBoxW

def main():
    creds = None

    if os.path.exists('token.json'):
        creds = Credentials.from_authorized_user_file('token.json', SCOPES)

    # If there are no (valid) credentials available, directs user to OAuth login through default browser.
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            try:
            
                creds.refresh(Request())

            except google.auth.exceptions.RefreshError:
                MessageBox(None, 'There was an error authorizing with Google. You will be redirected to a login page.', 'Google Sheet to CSV: Fatal Error', 0)

        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                'C:\\Users\AeroG\OneDrive\Desktop\SHEETSCSV\credentials.json', SCOPES) # Sometimes requires a specific path to credentials.json.
            creds = flow.run_local_server(port=0)

        # Save the credentials for the next run
        with open('token.json', 'w') as token:
            token.write(creds.to_json())

    service = build('drive', 'v3', credentials=creds)

基本上您只需要看下面的这一部分。

    # Call the Drive v3 API and compile CSV file from provided ID
    file_id = GSHEET_ID
    request = service.files().export_media(fileId=file_id,
                                             mimeType='text/csv')
    fh = io.BytesIO()
    downloader = MediaIoBaseDownload(fh, request)
    done = False
    while done is False:
        status, done = downloader.next_chunk()
        print("Download %d%%." % int(status.progress() * 100))
    
    fh.seek(0)
    with open('C:\\Users\AeroG\OneDrive\Desktop\SHEETSCSV\\' + FILE_NAME, 'w', newline='\r\n') as f:
        f.write(str(fh.read()))
        f.close()

if __name__ == '__main__':
    main()

我真的需要帮助。简而言之，我得到的输出 CSV 是这样的:

b' ,Jurisdiction,SHIPPING_ID,Combined,JobTitle,Shipping_Address,City,State,Zip,Phone,Email,Bill to addresses,,JobTitle,Address,City,State,Zip,Phone,Email\r\nDATA,DATA,DATA,DATA,DATA,DATA,DATA,DATA,DATA,,,,,,,,,\r\nDATA,DATA,DATA,DATA,DATA,DATA,DATA,DATA,DATA,DATA,DATA,,,,,,,,,\r\nDATA,DATA,DATA,DATA

而不是所需的输出，这是:

b' ,Jurisdiction,SHIPPING_ID,Combined,JobTitle,Shipping_Address,City,State,Zip,Phone,Email,Bill to addresses,,JobTitle,Address,City,State,Zip,Phone,EmailDATA,DATA,DATA,DATA,DATA,DATA,DATA,DATA,DATA,,,,,,,,,DATA,DATA,DATA,DATA,DATA,DATA,DATA,DATA,DATA,DATA,DATA,,,,,,,,,DATA,DATA,DATA,DATA

我知道 \r\n 应该转换为换行符，但事实并非如此。非常感谢任何帮助或引导我朝着正确方向前进的东西。我对任何解决方案都很满意，即使它涉及一些变通方法。

Answer 1

这是来自 documentation 的示例.我怀疑问题是否不在于您如何将片段写入文件。

file_id = '1ZdR3L3qP4Bkq8noWLJHSr_iBau0DNT4Kli4SxNc2YEo'
request = drive_service.files().export_media(fileId=file_id,
                                             mimeType='application/pdf')
fh = io.BytesIO()
downloader = MediaIoBaseDownload(fh, request)
done = False
while done is False:
    status, done = downloader.next_chunk()
    print "Download %d%%." % int(status.progress() * 100)

为什么不更简单一些。

with open('C:\\Users\AeroG\OneDrive\Desktop\SHEETSCSV\\' + FILE_NAME, "wb") as f:
    f.write(fh.getbuffer())