Error: Data between close double quote (") and field separator

user_12104120 0
Viewed 2083

I'm trying to use Google Apps Script to take a CSV from Google Drive and put it into Big Query. When I upload, I get this error:

"Error while reading data, error message: Error detected while parsing row starting at position: 560550. Error: Data between close double quote (") and field separator."

I've tried looking at that byte position of the file and its way outside the bounds of the CSV (it only goes to ~501500 bytes).

Here's a link to the CSV that I'm using which is a scrape of a website: https://drive.google.com/file/d/1k3cGlTSA_zPQCtUkt20vn6XKiLPJ7mFB/view?usp=sharing

Here's my relevant code:

 function csvToBigQuery(exportFolder, csvName, bqDatasetId){
  try{
    //get most recent export from Screaming Frog
    var mostRecentFolder = [];    
    while(exportFolder.hasNext()){
      var folder = exportFolder.next();
      var lastUpdated = folder.getLastUpdated();
      if(mostRecentFolder.length == 0)
        mostRecentFolder = [folder.getLastUpdated(),folder.getId()];
      else if(lastUpdated > mostRecentFolder[0])
        mostRecentFolder = [lastUpdated, folder.getId()];
    }    
    var folderId = mostRecentFolder[1];
    var file = DriveApp.getFolderById(folderId).getFilesByName(csvName + '.csv').next();
    
    if(!file)
      throw "File doesn't exist";
    
    //get csv and add date column.
    //getBlob().getDataAsString().replace(/(["'])(?:(?=(\\?))\2[\s\S])*?\1/g, function(e){return e.replace(/\r?\n|\r/g, ' ')})
    var rows = Utilities.parseCsv(file.getBlob().getDataAsString());
    Logger.log(rows);
    var numColumns = rows[0].length;    
    
    rows.forEach(function(row){
      row[numColumns] = date;
    });
    rows[0][numColumns] = 'Date';
    
    let csvRows = rows.map(values =>values.map(value => JSON.stringify(value).replace(/\\"/g, '""')));
    let csvData = csvRows.map(values => values.join(',')).join('\n');
    //log(csvData)
    var blob = Utilities.newBlob(csvData, 'application/octet-stream');
    
    
    //create job for inserting to BQ.
    var loadJob = {
      configuration: {
        load: {
          destinationTable: {
            projectId: bqProjectId,
            datasetId: bqDatasetId,
            tableId: csvName
          },
          autodetect: true,  // Infer schema from contents.
          writeDisposition: 'WRITE_APPEND',
        }
      }
    };
    
    //append to table in BQ.
    BigQuery.Jobs.insert(loadJob, bqProjectId, blob);
    
    
  }catch(e){
    Logger.log(e); 
  }
}
google-bigquery google-apps-script csv javascript
1 Answers

Modification points:

From your error message, I thought that there might be the parts which are not enclosed by the double quota. So, I searched When I saw your CSV data and your CSV data is replaced \"(|.+?)\" with "" using the following script, it was found that the row 711 has the value.

function sample() {
  var id = "###";  // File ID of your CSV file.

  // This is your script.
  var file = DriveApp.getFileById(id);
  var rows = Utilities.parseCsv(file.getBlob().getDataAsString());
  var numColumns = rows[0].length;
  var date = "sample";
  rows.forEach(function(row){
    row[numColumns] = date;
  });
  rows[0][numColumns] = 'Date';
  let csvRows = rows.map(values =>values.map(value => JSON.stringify(value).replace(/\\"/g, '""')));
  let csvData = csvRows.map(values => values.join(',')).join('\n');
  
  // I added below script for checking your CSV data.
  var res = csvData.replace(/\"(|.+?)\"/g, "");
  DriveApp.createFile("sample.txt", res);
}

The row 711 is as follows.

"https://supergoop.com/products/lip-shield-trio/?utm_source=Gorgias&utm_medium=CustomerCare&utm_campaign=crosssellhello\","text/html; charset=utf-8","200","OK","Non-Indexable","Canonicalised","Lip Shield Trio - Restores, Protects + Water-resistant – Supergoop!","67","595","Moisturizing lip protection made from antioxidant-rich coconut, avocado, and grape seed oil.","92","576","","0","Lip Shield Trio","15","Lip Shield Trio","15","Why We Love It","14","Ingredients","11","","","","https://supergoop.com/products/lip-shield-trio","","","","","451488","754","1.686","5","","12","4","0.590","205","80","8","5","","","","","f6d1476960d22b1c5964581e161bdd49","0.064","","","","","HTTP/1.1","https://supergoop.com/products/lip-shield-trio/?utm_source=Gorgias&utm_medium=CustomerCare&utm_campaign=crosssellhello%5C"

From this value, I found that \" is used at "https://supergoop.com/products/lip-shield-trio/?utm_source=Gorgias&utm_medium=CustomerCare&utm_campaign=crosssellhello\". I thought that the reason of your issue might be due to this.

So in order to avoid this issue, how about the following modification?

Modified script:

From: 
let csvRows = rows.map(values =>values.map(value => JSON.stringify(value).replace(/\\"/g, '""')));
To: 
let csvRows = rows.map(values =>values.map(value => JSON.stringify(value).replace(/\\"/g, '""').replace(/\\"/g, '')));

or

From: 
var rows = Utilities.parseCsv(file.getBlob().getDataAsString());
To: 
var rows = Utilities.parseCsv(file.getBlob().getDataAsString().replace(/\\/g, ''));
  • By this modification, I could confirm that the file size was reduced with 2 bytes between your script and the modified script. And also, when above check script is used for the CSV data using the modified script, I could confirm that all rows have no values.
user_71086530
Related