Utilities.parseCsv(csv, “;”) also splits on commas

2019-08-16 02:36发布

问题:

I want to work with a csv file delimited by ;. In some fields, we have commas, but it's ok.

An example line is as follows (notice there is a comma in one column):

01.02.2018;01.02.2018;"SEPA-Dauerauftrag an";Maier, Herbert;RINP Dauerauftrag Miete Kräpelinstr. 61;DE45700100800225067803;PBNKDEFFXXX;;;;;;;;;-900,00;;EUR

The Google Apps Script code that I use is as follows;

function importCSVFromGoogleDrive() {
  var ss = SpreadsheetApp.openById('<wb id>');
  var outputSheet = ss.getSheetByName('import');

  var fileIterator = DriveApp.getFilesByName("Kontoumsaetze_220_320895600_20180728_155842_DEV.csv");
  var csv = fileIterator.next().getBlob().getDataAsString('ISO-8859-1');
  Logger.log(csv);
  var csvData = Utilities.parseCsv(csv, ";");
  Logger.log("-------------");
  Logger.log(csvData);
}

I just want to separate using ;, but I can not achieve it. GAS keeps separating also the comma and this breaks my program.

  1. Why is it separating by , if I am telling ;.
  2. How can I fix the issue?

Here are the logs (it separates using the comma, in Mayer, Herber):

[18-07-29 18:42:22:922 CEST] 01.02.2018;01.02.2018;"SEPA-Dauerauftrag an";Maier, Herbert;RINP Dauerauftrag Miete Kräpelinstr. 61;DE45700100800225067803;PBNKDEFFXXX;;;;;;;;;-900,00;;EUR
[18-07-29 18:42:22:923 CEST] -------------
[18-07-29 18:42:22:924 CEST] [[01.02.2018, 01.02.2018, SEPA-Dauerauftrag an, Maier, Herbert, RINP Dauerauftrag Miete Kräpelinstr. 61, DE45700100800225067803, PBNKDEFFXXX, , , , , , , , , -900,00, , EUR]]

回答1:

This is a failure of the Apps Script Logger when it comes to displaying object data.

Using this code:

function csvParseSemiOnly() {
  var csv = '01.02.2018;01.02.2018;"SEPA-Dauerauftrag an";Maier, Herbert;RINP Dauerauftrag Miete Kräpelinstr. 61;DE45700100800225067803;PBNKDEFFXXX;;;;;;;;;-900,00;;EUR';
  var csvData = Utilities.parseCsv(csv, ";");
  console.log({message: "Parsed input", input: csv, output: csvData});
  Logger.log(csvData[0][3]);
}

I obtain this result:

In general, I recommend using the Stackdriver Logging functionality if you need to inspect objects, especially if nested, or review logging activity from more than the most recent script execution.

You should review the Apps Script guide to logging:
https://developers.google.com/apps-script/guides/logging

Of note, to review logs in Stackdriver you must be able to access the script's Google Cloud Platform project.