将CSV数据导入Google表格 [英] Import CSV data into Google Sheets

问题描述

尝试为此文件使用IMPORTDATA函数时:

When trying to use the IMPORTDATA function for this file:

https://www.kaggle.com/stefanoleone992/fifa-20-complete-player-dataset#players_20.csv

发生意外错误，表明无法将数据导入电子表格.还有什么其他方法可以将这些数据带到电子表格中?

An unexpected error occurs that says it is impossible to import data into the spreadsheet. Is there any other way that I can bring this data to my spreadsheet?

这些数据对于我正在进行的工作非常重要.能够键入和复制所有内容，然后根据我的需要进行过滤，这将使我节省了将近3个月的工作.

This data would be very important to the work I'm doing. It would save me from almost 3 months of work to be able to type and copy everything and then filtering according to my need.

能够至少导入所有玩家的简单信息非常重要，但不必一定要从每个玩家导入所有信息列.可以导入的列数已经很完美了.

It would be very important to be able to import at least the simple info of all players, but do not necessarily have to import all columns of info from each player. The amount of columns can import is already perfect.

如果能的话，我将不胜感激.

I would be grateful if there was any way.

推荐答案

• 您要从https://www.kaggle.com/stefanoleone992/fifa-20-complete-player-dataset下载players_20.csv的CSV文件，并将CSV数据放入电子表格.
• 您想使用Google Apps脚本实现这一目标.
• You want to download a CSV file of players_20.csv from https://www.kaggle.com/stefanoleone992/fifa-20-complete-player-dataset and put the CSV data to the Spreadsheet.
• You want to achieve this using Google Apps Script.

如果我的理解是正确的，那么这个答案如何?请认为这只是几个答案之一.

If my understanding is correct, how about this answer? Please think of this as just one of several answers.

很遗憾，不能直接从https://www.kaggle.com/stefanoleone992/fifa-20-complete-player-dataset#players_20.csv的URL下载CSV数据.为了下载CSV文件，需要登录到kaggle.作为其他模式，您也可以使用API​​下载它.在此答案中，为了下载CSV文件，我使用了Kaggle的公共API.

Unfortunately, the CSV data cannot be directly downloaded from the URL of https://www.kaggle.com/stefanoleone992/fifa-20-complete-player-dataset#players_20.csv. In order to download the CSV file, it is required to login to kaggle. As other pattern, you can also download it using API. In this answer, in order to download the CSV file, I used Kaggle's public API.

在使用脚本之前，请向https://www.kaggle.com注册一个帐户，然后检索令牌文件.关于如何检索令牌文件，您可以参阅官方文档.

Before you use the script, please register an account to https://www.kaggle.com, and retrieve the token file. About how to retrieve the token file, you can see the official document.

要使用Kaggle的公共API，您必须首先使用API​​令牌进行身份验证.在网站标题中，单击您的用户个人资料图片，然后从下拉菜单中单击我的帐户".这将带您进入 https://www.kaggle.com/account 的帐户设置.向下滚动到页面的标有API的部分:

In order to use the Kaggle’s public API, you must first authenticate using an API token. From the site header, click on your user profile picture, then on "My Account" from the dropdown menu. This will take you to your account settings at https://www.kaggle.com/account. Scroll down to the section of the page labelled API:

要创建新令牌，请单击创建新API令牌"按钮.这会将新的身份验证令牌下载到您的计算机上.

To create a new token, click on the "Create New API Token" button. This will download a fresh authentication token onto your machine.

在此脚本中，将使用下载的令牌文件中的令牌对象.

In this script, the token object in the downloaded token file is used.

请将以下脚本复制并粘贴到电子表格的容器绑定脚本中.并设置csvFilename，path和tokenObject的变量.在您的情况下，我已经设置了csvFilename和path.因此，请仅设置您的令牌对象.

Please copy and paste the following script to the container-bound script of Spreadsheet. And please set the variavles of csvFilename, path and tokenObject. In your case, I have already set csvFilename and path. So please set only your token object.

function myFunction() {
  var csvFilename = "players_20.csv"; // Please set the CSV filename.
  var path = "stefanoleone992/fifa-20-complete-player-dataset"; // Please set the path.
  var tokenObject = {"username":"###","key":"###"}; // <--- Please set the token object.
  
  var baseUrl = "https://www.kaggle.com/api/v1/datasets/download/";
  var url = baseUrl + path;
  var params = {headers: {Authorization: "Basic " + Utilities.base64Encode(tokenObject.username + ':' + tokenObject.key)}};
  var blob = UrlFetchApp.fetch(url, params).getBlob();
  var csvBlob = Utilities.unzip(blob).filter(function(b) {return b.getName() == csvFilename});
  if (csvBlob.length == 1) {
    var csvData = Utilities.parseCsv(csvBlob[0].getDataAsString());
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  } else {
    throw new Error("CSV file of " + csvFilename + " was not found.");
  }
}

流动:

此脚本的流程如下.

Flow:

The flow of this script is as follows.

1. 运行脚本时，kaggle datasets download -d stefanoleone992/fifa-20-complete-player-dataset的kaggle命令与Google Apps脚本一起运行.这样，便下载了ZIP文件.
2. 从下载的ZIP文件中检索csvFilename的CSV文件.
3. 从CSV文件中解析CSV数据.
4. 将CSV数据放入活动工作表中.
   • 在此脚本中，所有数据都使用Blob处理.因此不会创建文件.
1. When the script is run, the kaggle command of kaggle datasets download -d stefanoleone992/fifa-20-complete-player-dataset is run with Google Apps Script. By this, the ZIP file is downloaded.
2. Retrieve the CSV file of csvFilename from the downloaded ZIP file.
3. Parse the CSV data from the CSV file.
4. Put the CSV data to the active sheet.
   • In this script, all data is processed with the blob. So the file is not created.

注意:

• 似乎CSV数据很大.因此，请等待脚本完成.
  • 在我的环境中，我花了大约150秒钟，直到将CSV数据放入电子表格中.
  • players_20.csv的CSV数据具有18279行和104列.

  Note:

  • It seems that the CSV data is large. So please wait until the script is finished.
    • In my environment, I spent for about 150 seconds until the CSV data is put to the Spreadsheet.
    • The CSV data of players_20.csv has 18279 rows and 104 columns.
    • 对Kaggle的公共API进行身份验证
    • kaggle-api
    • Authentication of Kaggle's public API
    • kaggle-api

    如果我误解了您的问题，而这不是您想要的方向，我深表歉意.

    If I misunderstood your question and this was not the direction you want, I apologize.

    如果要选择要放入的列，请按如下所示修改上面的示例脚本.

    If you want to select the columns you want to put, please modify above sample script as follows.

    var csvData = Utilities.parseCsv(csvBlob[0].getDataAsString());
    var sheet = SpreadsheetApp.getActiveSheet();
    

    收件人:

    var csvData = Utilities.parseCsv(csvBlob[0].getDataAsString());
    var needColumns = [1, 2, 3];
    csvData = csvData.map(function(row) {return needColumns.map(function(col) {return row[col]})});
    var sheet = SpreadsheetApp.getActiveSheet();
    

    • 在上述修改中，作为测试用例，将1、2和3列放入电子表格中.

    来自将CSV数据放入电子表格的基准测试结果，例如使用表格API放置CSV数据?为此，请如下修改上述示例脚本.在运行脚本之前，请在高级Google服务中启用Sheets API.

    From the result of benchmark for putting CSV data to Spreadsheet, for example, how about using Sheets API for putting CSV data? For this, please modify above sample script as follows. Before you run the script, please enable Sheets API at Advanced Google services.

    var csvData = Utilities.parseCsv(csvBlob[0].getDataAsString());
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
    

    收件人:

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var resource = {requests: [{pasteData: {data: csvBlob[0].getDataAsString(), coordinate: {sheetId: sheet.getSheetId()}, delimiter: ","}}]};
    Sheets.Spreadsheets.batchUpdate(resource, ss.getId());
    

    • 在这种情况下，我花了大约50秒钟，直到将CSV数据放入电子表格中.
    • 基准测试:使用Google Apps脚本将CSV数据导入到电子表格中
    • 高级Google服务
    • Benchmark: Importing CSV Data to Spreadsheet using Google Apps Script
    • Advanced Google services

    这篇关于将CSV数据导入Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！