如何导入制表符分隔的“CSV” [英] How to Import tab-delimited "CSV"

问题描述

在与您的文档列表交互教程中，从CSV文件导入数据显示如何导入包含逗号分隔值的CSV文件。

In the Interacting With Your Docs List tutorial, Importing data from a CSV file shows how to import a CSV file with comma-separated values.

但是，我将上传 .txt 文件（带制表符分隔值）。

However, I'll be uploading a .txt file with tab-separated values. (Such as may be exported from Excel.)

如何修改脚本以将导入的电子表格识别为制表符分隔值而不是逗号分隔值？

How could that script be modified to recognize the imported spreadsheet as tab-separated values instead of comma-separated values?

推荐答案

使用 Utilities.parseCsv（csv，delimiter） 。以下内容来自Google Apps脚本教程与您的文档列表互动 ，将 CSVToArray（）替换为 Utilities.parseCsv（）。

要自动检测分隔符是标签还是逗号是一件简单的事情：

To automatically detect whether the deliminator is a tab or comma is a simple thing:

// Detect delimiter - tab or comma
var delim = ",";
if (csvFile.indexOf("\t") != -1) delim = "\t"; 

importFromCSV

importFromCSV

/**
 * Populates a sheet with contents read from a CSV file located
 * in the user's GDrive. If either parameter is not provided, the
 * function will open inputBoxes to obtain them interactively.
 *
 * Adapted from <I>Tutorial: Interacting With Your Docs List.</I>
 (https://developers.google.com/apps-script/articles/docslist_tutorial#section2)
 *
 * @param {string}fileName (Optional) The name of the input file.
 *
 * @param {string} sheetName (Optional) The name of the destination sheet.
 *        If the sheet does not exist, it will be created by
 *        this function. A pre-existing sheet will be cleared 
 *        before importing the CSV data.
 *
 * @returns {JsonObject} A summary of the import operation,
 *        including the date the input file was last updated
 *        [see <a href="https://developers.google.com/apps-script/class_file#getLastUpdated">File.getLastUpdated()</a>], and the number of rows
 *        and columns imported.
 * <PRE>
 *
 *   { lastUpdated : 5-Mar-2014, numRows : 2541, numCols : 22 }
 *  </PRE>
 *
 *  @throws {Error} "No Input File" if input CSV not found.
 */
function importFromCSV(fileName,sheetName) {
  var useBrowser = (!fileName);   // Assume that spreadsheet UI in use, if no args
  fileName = fileName || Browser.inputBox("Enter the name of the file in your Docs List to import (e.g. myFile.csv):");
  sheetName = sheetName || Browser.inputBox("Enter the name of the sheet to import into (e.g. Sheet1):");

  var files = DocsList.getFiles();
  var csvFile = "";
  var lastUpdated = 0;

  for (var i = 0; i < files.length; i++) {
    if (files[i].getName() == fileName) {
      csvFile = files[i].getContentAsString();
      lastUpdated = files[i].getLastUpdated();
      break;
    }
  }

  if (lastUpdated == 0) {
    Browser.msgBox("No Input File", "Either no file name was provided, or file does not exist.", Browser.Buttons.OK);
    throw new Error("No Input File");
  }
  else {
    // Detect delimiter - tab or comma
    var delim = ",";
    if (csvFile.indexOf("\t") != -1) delim = "\t"; 

    var csvData = Utilities.parseCsv(csvFile, delim);

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(sheetName);
    if (sheet === null) {
      ss.insertSheet(sheetName);
      sheet = ss.getSheetByName(sheetName);
    }

    sheet.clear();

    var numRows = csvData.length;
    var numCols = csvData[0].length; // assume all rows are same width

    // Make a single call to spreadsheet API to write values to sheet.
    sheet.getRange(1, 1, numRows, numCols).setValues( csvData );

  }

  // Report results (if UI attached)  
  Browser.msgBox("Imported " + numRows + " rows x " + numCols + "columns");

  // Return an object with import results
  return { lastUpdated : lastUpdated, numRows : numRows, numCols : numCols };
}

电子表格菜单项

创建自定义菜单前端：

Spreadsheet Menu Item

To create a custom menu front-end:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cMenuEntries = [{name: "Load CSV Data", functionName: "importFromCSV"}];
  ss.addMenu("Custom", cMenuEntries);
}

使用参数调用

您还可以从其他脚本调用函数，为csv文件名和电子表格选项卡传递参数。

Call with parameters

You can also call the function from other scripts, passing arguments for the csv filename and spreadsheet tab.

function test_importFromCSV() {
  return importFromCSV("My CSV File.txt","Sheet1");
}

这篇关于如何导入制表符分隔的“CSV”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！