CODEWITHSUNDEEP
javascriptarraysgoogle-apps-scriptjavascript-objectscsv-import
S Mullins
S Mullins

Reputation: 1

How do I append data from a CSV file without the header into Google Sheet

I have managed to retrieve the data and append it into my sheet but I have not figured out how to exclude the header row.

function importFromCSV() {
  var fileName = Browser.inputBox("Ingrese archivo en Drive a importar (ej. datosBaby.csv):");
   
  var searchTerm = "title = '"+fileName+"'";
   
  // search for our file
  var files = DriveApp.searchFiles(searchTerm)
  var csvFile = "";
   
  // Loop through the results
  while (files.hasNext()) {
    var file = files.next();
    // assuming the first file we find is the one we want
    if (file.getName() == fileName) {
      // get file as a string
      csvFile = file.getBlob().getDataAsString();
      break;
    }
  }
  // parseCsv returns a [][] array and writes to the sheet
  var csvData = Utilities.parseCsv(csvFile);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  // data to a sheet
  sheet.getRange(sheet.getLastRow()+1, 1, csvData.length, csvData.length).setValues(csvData);
  
  //sheet.getRange(1, 1, csvData.length, csvData[1].length).setValues(csvData); <-- This one places txt a A1
  //sheet.appendRow(csvData[1]); <-- This one  does not work
}

Here is an example of what is happening: The header from the CSV import is repeated

Thanks to anyone who can point me in the right direction. I am a beginner at this.

Upvotes: 0

Views: 2115

Answers (2)

Marc Garbella
Marc Garbella

Reputation: 31

The splice approach is right, but in the solution above, it returns the values for the header, and does not remove the header. What is needed to remove the header is to use the splice function and tell it to start in the second row and go until the end. This would look like this:

var csvDataNoHeader = csvData.splice(1,csvData.length-1)

This tells the function to start at the second element of the array and select all elements after, the minus 1 accounts for the removing of the header.

Upvotes: 2

Raserhin
Raserhin

Reputation: 2676

So I understand that you are importing multiple csv where the header is repeating for each one.

To get rid of it there are a lot of actions, I myself prefer to seperate the header of the actual data. You can do that by invoking the .shift() into the array, and that would remove and return the first value of the array.

Call the shift method after parsing the csv.

var csvData = Utilities.parseCsv(csvFile);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

var header = csvData.shift(); // Now the csvData don't have the header and you can
                              // still use the `header` variable if needed

// data to a sheet
sheet.getRange(sheet.getLastRow()+1, 1, csvData.length, csvData[0].length)
     .setValues(csvData);

I also changed the way you select the columns, unless your data is square N arrays of N elements, you will have problems. So better to take the columns as csvData[0].length.

Upvotes: 1

Related Questions