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")
Best Answer
As best as I can establish,
IMPORTDATA
won't respect leading zeros in ANY field containing numbers; the field will always be treated byIMPORTDATA
as numeric, regardless of sheet formatting. This applies regardless of whetherIMPORTDATA
is used alone, or in conjunction with other functions such asQUERY
.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".
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
If columns C & D are formatted as Plain text, then the data will appear formatted - both on the initial import and all subsequent imports.
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.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.