How to Import tab-delimited “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.

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?

回答1:

Use Utilities.parseCsv(csv, delimiter). The following is adapted from the Google Apps Script tutorial Interacting With Your Docs List, replacing CSVToArray() with 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

/**
 * 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);
}

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");
}