Import csv file containing Line Break into the cells

user_13858357 0
Viewed 56

I'm using below code to import a csv file called "Home care.csv" into Google Sheet. The issue is that in some of the cells of this csv file there are several informations separated by Line Breaks and as a result the google sheet file is a perfect mess since these informations are put on below lines and not kept into the cells. Do you know a way to keep the cells as they are ?

function DeleteTracker() { 
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home care');
ss.getRange('A2:Z50000').clearContent();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var maxRows = sheet.getMaxRows(); 
var lastRow = sheet.getLastRow();
if (maxRows-lastRow != 0)
{
sheet.deleteRows(lastRow+1, maxRows-lastRow);
}  
}
function importCSVFromGoogleDrive() { 
var file = DriveApp.getFilesByName('Home care.csv').next();
var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home care');
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

Thanks a lot in advance Isabelle

google-apps-script import csv
1 Answers

Try to change the function importCSVFromGoogleDrive() this way:

function importCSVFromGoogleDrive() { 
  var file = DriveApp.getFilesByName('Home care.csv').next();

  // get the contains as a string
  var str = file.getBlob().getDataAsString();

  // replace all break lines inside quotes with ¶
  var m = str.match(/"[^",]*?[\n\r]+[^"]*?"/g); // comma ',' delimited csv
  if (m) m.forEach(t=> str=str.split(t).join(t.replace(/[\n\r]+/g,'¶')));

  // parse the string and put it on the sheet
  var csvData = Utilities.parseCsv(str);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home care');
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

  // replace ¶ back to break lines within cells
  sheet.createTextFinder('¶').replaceAllWith('\n');
}
user_142654690
Related