JDBC Import into sheets, how to keep leading zeros on fields?

Question

I have so much trouble with leading zeros in general. Importing into Sheets using JDBC connection, I haven't figured out a way to keep the zeros. The column types are varchar() for values of varied length, and char() for static length.

In the past with other data I have added a leading ' to values, or chosen to getDisplayValue() to keep them. What would work here?

while (results.next()){
          var tmpArr = [];
          var rowString = '';
          for (var col = 0; col < numCols; col++) {
            rowString += results.getString(col + 1) + '\t';
            tmpArr.push(results.getString(col + 1));
          }
          valArr.push(tmpArr);
        }

        sheet.getRange(3, 1 , valArr.length, numCols).setValues(valArr);

Data Exmaple varchar column:

Have you tried `toString()` instead of `getString`? If this does not help - can you provide a sample of what your data looks like? — ziganotschka, Oct 24 '19 at 15:20
@ziganotschka Calling `toString()` on a `ResultSet` will yield driver-specific behavior (usually just the standard `Object.toString()` which is not useful here). Using `getString(int)` is the right method here. Either there are no leading zeroes in the source, or Google Sheets automatically converts them to a number type without leading zeroes. — Mark Rotteveel, Oct 24 '19 at 15:28
@MarkRotteveel Google is converting them, I can clearly see the zeros in the table. — rcbjmbadb, Oct 24 '19 at 16:47
I don't know Google Sheets myself, but if it behaves anything like Excel, then maybe the cell defaults to numeric, and this conversion is applied automatically. Maybe you should look at the API if there is something to set the cell-type to text or string or something like that. — Mark Rotteveel, Oct 24 '19 at 18:11
@Cooper number columns always drop leading zeros. Also, I have characters mixed in. — rcbjmbadb, Oct 24 '19 at 18:43
@Cooper That will set the column to a set number of digits, and fill in the rest with zeros. That's not what I'm looking for, I want the actual data as it lives in the database. This field hold values of variable length, some starting with zero, some not. — rcbjmbadb, Oct 24 '19 at 19:10
@rcbjmbadb Just to clarify... do you want the values with leading zeros to be recognised as text or numbers? — Tedinoz, Nov 04 '19 at 02:53
@Tedinoz has to be text I think, because some of the values in the column are alphanumeric, and they differ in character length. — rcbjmbadb, Nov 05 '19 at 16:20

Tedinoz · Accepted Answer · 2019-11-11T21:36:37.083

1

You are retrieving data into a Google Sheet from a MySQL database table using Jdbc. One of the database columns is formatted as "varchar" and includes some all-numeric values that have one or more leading zeros. When you update the database values to your Google Sheet, the leading zeros are not displayed.

Why

The reason for this is that the all-numeric values are displayed without the leading zeros is that the cells are formatted as Number, Automatic (or otherwise as a number). This means that they are 'interpreted' by Google Sheets as a number and, by default, all leading zeros are dropped.

On the other hand, if the cells are formatted as Number, Plain Text, then the all-numeric values are 'interpreted' as strings, and any leading zeros are retained.

The effect of formatting can be clearly seen in the following images, which also include istext and isnumber formula to confirm how they are interpreted under each format type.

Formatted as Number - Plain Text - treated as strings

Formatted as Number - Automatic - treated as numbers

Formatting on the fly

An alternative to pre-formatting (which wasn't successful in the OP's case) is to set the format as a part of the setValues() method using setNumberFormat

For example:

sheet.getRange(3, 1 , valArr.length, numCols).setNumberFormat('@STRING@').setValues(valArr);

There is a useful discussion of this methid in Format a Google Sheets cell in plaintext via Apps Script

edited Nov 11 '19 at 21:36

answered Nov 08 '19 at 08:02

Tedinoz

5,911
3
25
35

Unfortunately, a simple change of formatting doesn't work. The leading zeros are lost during import. Well, most leading zeros are lost during import. If a value has letters in the string, then leading zeros will be shown using formatting. If the string is only numbers, the leading zeros are not preserved, and formatting does nothing. I am looking for a way to preserve the leading zeros through the import script, through any possible process. – rcbjmbadb Nov 08 '19 at 13:07
These results were generated using your data in a MySQL table imported into a Google spreadsheet via Jdbc - they weren’t just inserted manually. This IS what happens when the values come out of the array into the spreadsheet; the difference depends on the formatting of the target cells. However, I’ll look at/for an alternative. – Tedinoz Nov 08 '19 at 20:18
Try this `sheet.getRange(3, 1 , valArr.length, numCols).setNumberFormat('@STRING@').setValues(valArr);` Full explanation here [Inserting string to sheet results in number inserted](https://stackoverflow.com/a/36561646/1330560) – Tedinoz Nov 09 '19 at 00:35
FWIW, these are the array values used for `setValues()`: `[[id, data], [1, 0110205361], [2, 0201206352], [3, 140875852], [4, LFCP01367], [5, LGLM00017]]`. The leading zeros are still there. The issue/problem/whatever is that, in the absence of instructions to the contrary, Google Sheets will interpret a value consisting only of numeric characters (even those with leading zeros) as a number. So, one must issue those "instructions to the contrary". You can do that by formatting the target cells in advance, or by using `.setNumberFormat('@STRING@')` to format the cells as they are updated. – Tedinoz Nov 09 '19 at 00:45
Awesome, this works. Why formatting alone works for you and not me I don't understand. Thank you so much, just what I needed. – rcbjmbadb Nov 10 '19 at 16:13
Your actual answer didn't work at all for my case, formatting alone would not work when the values were gone. If you can change/submit another answer with the .setNumberFormat('@STRING@') solution I will mark that as accepted :) – rcbjmbadb Nov 11 '19 at 19:33
Good suggestion; it's can be a bummer when the nuggets of "wisdom" (just a word, not a claim) are hidden away in the comments. – Tedinoz Nov 11 '19 at 21:38

JDBC Import into sheets, how to keep leading zeros on fields?

1 Answers1