0

I have a CSV file with certain entries being numbers and start with zeros, such as phone numbers and reference numbers. For instance:

"ID","Name","Telephone Number","Tracking Reference"
1,"John","030928374","098283746322"
2,"Maria","0049827326251","0011928376"

When I use the function IMPORTDATA in Google Spreadsheets, the leading zeros are systematically removed, but I need to keep them. I tried converting the columns to text, but it's still the same. Is there any way to import the data while keeping the initial zeros? Please note that the length of the entries can vary, as phone numbers have different formats across different countries, and reference numbers come from different suppliers.

I put the abovementionned CSV onto a test server, in order to make things easier: =IMPORTDATA("http://35.229.200.45/example.csv")

  • 3
    Just found out that if you add an apostrophe, the import works. "002" to "'002" would work, but it's not really optimal, as I need to have then a CSV format just for google spreadsheet... – Cyrus G Sep 26 '18 at 11:36
  • Welcome. On this site the solutions should be posted as answers and it's encouraged that askers answer their own questions. – Rubén Sep 26 '18 at 14:40
  • @Rubén It's not the answer -- it's just a hint for other people if there is not proper answer, but it doesn't solve the problem. With what I propose I need to have two versions of the CSV, one for Google Spreadsheet, one for normal usage, which is far from optimal.... – Cyrus G Sep 26 '18 at 17:07
  • Is it critical that you import using the IMPORTRANGE function? What if you used the "import file" option from the menu? – Tedinoz Oct 2 '18 at 9:33
  • @Tedinoz yes, because IMPORTRANGE can have an URL where the file can be changed. In my case, it's changed 2-3 times per day. Importing file is not really the best way to streamline the process – Cyrus G Oct 2 '18 at 10:52
0

As best as I can establish, IMPORTDATA won't respect leading zeros in ANY field containing numbers; the field will always be treated by IMPORTDATA as numeric, regardless of sheet formatting. This applies regardless of whether IMPORTDATA is used alone, or in conjunction with other functions such as QUERY.

My proposed solution takes a different approach. Instead of using IMPORTDATA, I suggest importing, parsing and inserting the data into the spreadsheet. The benefits are that this approach enables display of leading zeros, and has the flexibility of enabling the data to be updated easily, and regularly.

1 - Format selected columns as text

First step is to identify the data columns in the CSV file that may contain leading zeros. Then format those spreadsheet columns as "Plain Text". This does not help with IMPORTDATA; it just ignores cell/range formatting. However, cell formatting is respected when the CSV data is "inserted" into the sheet by other means.

This image shows what I mean. Using the sample csv data as a guide, select columns C & D, and then format as "Plain text". enter image description here


2 - Import the data by script

This script imports the csv data and inserts it into the nominated sheet. Insert the script into code.gs in the script editor

/**
* Import, parse and insert the contents of the CSV data file.
*/
function importCSVfile() {

// establish the target sheet for the csv import
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("csvimport");  

// clear existing 
var range = sheet.getDataRange();
range.clear();

// Provide the full URL of the CSV file.
var csvUrl = "http://35.229.200.45/example.csv";

  // get the csv data
var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();

  // parse the data
var csvData = Utilities.parseCsv(csvContent);

// paste the csv data into the sheet
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

If columns C & D are formatted as Plain text, then the data will appear formatted - both on the initial import and all subsequent imports. enter image description here


If, per chance, Columns C & D are not formatted as Plain text, then the data is not formatted. After-the-event formatting does not repair leading zeros in the existing data, but if the data is reimported, then the "new" data will display leading zeros.

3 - Automating the CSV update

The IMPORTDATA function automatically recalculates every hour (assuming the spreadsheet is open). So our remaining challenge is to trigger the function to run whenever the spreadsheet is opened and/or every hour while it is open.

   /**
     * Creates a time-driven triggers to update CSV
    */
    function createCSVUpdateTrigger() {
    // Trigger every 1 hours.
    ScriptApp.newTrigger('importCSVfile')
      .timeBased()
      .everyHours(1)
      .create();
    }

    /**
     * Creates a trigger for when a spreadsheet opens.
     */
    function createCSVUpdateonOpenTrigger() {
    var ss = SpreadsheetApp.getActive();
    ScriptApp.newTrigger('importCSVfile')
      .forSpreadsheet(ss)
      .onOpen()
      .create();
    }

Copy the code into your Code.gs and run both functions to create the triggers. Then from the Script Editor menu, check your Triggers: Edit > Current Projects Triggers. (FWIW, these were adapted direct from the Google documentation

Obviously you can adjust the update frequency to suit your own circumstances. enter image description here

  • Not an easy solution, but that'll do :) Thanks a lot @Tedinoz – Cyrus G Oct 4 '18 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.